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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file