Vertica 7 : Tuple mover causing deadlocks
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.
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