We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Vertica 7 : Tuple mover causing deadlocks — Vertica Forum

Vertica 7 : Tuple mover causing deadlocks

Yogi_BabaYogi_Baba
in General Discussion

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

  • Rahul_ChoudharyRahul_Choudhary

    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

     

     

  • Yogi_BabaYogi_Baba

     

    Thank you Rahul.   We will attempt to upgrade to 7.1.18 and see how it goes.

  • Rahul_ChoudharyRahul_Choudhary

    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

  • Yogi_BabaYogi_Baba

     

    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
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...