Vertica 7 : Tuple mover causing deadlocks
Hello all,
I have recently come across an issue which is as following.
Database environment : Vertica 7.1
1. data gets loaded every 5-10 mintues.
2. Size of data is 10k-100k rows every 5-10 mins.
3. We mostly use insert only option and exploit partition swap feature.
4. There are some data loading processes with updates/deletes.
5. TM resource pool maxconcurrency is 3
We are receiving deadlock error reported by TM process. The strange thing is that the TM is reporting a failure on some other table while the SWAP is happening on some other objects.
My suspecion is that TM process is very aggresive in movingout/mergeout task holding objects with transaction isolation level = SERIALIZABLE. Which is very strict transaction level isolation.
Let me know if anyone of you have experienced similar situation.
- Yogi
SWAP_PARTITIONS_BETWEEN_TABLES(’SCHEMA.WRK_TABLE1’,’PART01’,'PART01’,’SCHEMA.TARGET_TABLE1') ERROR 3011: Deadlock: <Txn 0xa00000007f378f> TM Moveout - deadlock error Deadlock U locking Table:SCHEMA2.TABLE2. O held by <user TEST_USER (swap partitions between tables)>. Your current transaction isolation level is SERIALIZABLE
Comments
Hi
This kind of issue happen with both move_partition_to_tables & swap_partition_between_tables as both of these cause moveout to occur on the entire WOS & not just the table involved in the running function while running on a busy database.
I believe you are running on 7.1 version as you have stated so will advise you to upgrade your database to the latest hot-fix version since this issue no longer exist there.
Tip: Go ahead with the upgrade as this will resolve your concern.
Regards
Rahul Choudhary
Thank you Rahul. We will attempt to upgrade to 7.1.18 and see how it goes.
Hi
My pleasure.Let me know how it goes & if you still receives this issue although I will suggest you to upgrade to 7.1.1-12 version.As it is always good to be on the latest one.
Regards
Rahul Choudhary
Yes , we are planning to get the latest patch and we are hopeful that it will resolve the bug.
regards,
Yogi