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


How to generate a stalled truncate query? — Vertica Forum

How to generate a stalled truncate query?

Hi,
we observed that sometimes truncate queries were not returning without throwing an error. A DROP TABLE command did also not work. Howerver, that returned with an error that other objects depend on it.
We have difficulties devloping a solution that handles stalled truncate queries, because we we not able to reproduce them and test our solution accordingly.
So the question is if someone knows, how we can produce a truncate query that does not return?

Best Answer

  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    A TRUNCATE is a DDL operation, and it requires both an exclusive lock on the truncated table and on the global catalogue. And if it cannot acquire that lock, it waits for 5 minutes, by default. The database parameter LockTimeout, whose default is 300 (seconds) controls that at table level.

    If a table is locked by a transaction, and another transaction tries to truncate it, the second tries to acquire an exclusive lock on that table before proceeding. After five minutes, it times out.

    Try this for a table you can play with. Mine was public.from_kafka:

    • connect once:
    sbx=> lock table public.from_kafka in exclusive mode;
    LOCK TABLE
    
    • connect in another session:
    sbx=> TRUNCATE TABLE public.from_kafka;
    

    For five minutes, nothing happens.
    Then:

    sbx=> TRUNCATE TABLE public.from_kafka;
    ROLLBACK 5157:  Unavailable: [Txn 0xa00000002842aa] O lock table - timeout error Timed out O locking Table:public.from_kafka. X held by [user dbadmin (LOCK TABLE)]. Your current transaction isolation level is SERIALIZABLE
    sbx=> 
    

    If you repeat the exercise in session 2:

    sbx=> TRUNCATE TABLE public.from_kafka;
    

    ... and then go back to session 1 and:

    sbx=> lock table public.from_kafka in exclusive mode;
    LOCK TABLE
    sbx=> COMMIT; -- <===
    

    ... you will instantly see this in session 2:

    sbx=> TRUNCATE TABLE public.from_kafka;
    TRUNCATE TABLE
    sbx=> 
    

    I would just open another session and truncate the table in question in exclusive mode to simulate a stalled truncate attempt.

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    Sorry, LOCK public.the_table IN EXCLUSIVE MODE; , NOT truncate.

  • joergschaberjoergschaber Vertica Customer

    Thanks for this comment! We will definetly tried this approach and let you know. However, I am pretty sure that the query was stalled for more than 300s, so it actually did not seem to return at all. However, I will have a closer look next time this occurs.

  • marcothesanemarcothesane - Select Field - Administrator

    What's your current LockTimeout value?

  • joergschaberjoergschaber Vertica Customer

    How can I query it? I tried SHOW SESSION PARAMETER ALL, but could not find it.

  • marcothesanemarcothesane - Select Field - Administrator
    marco ~/1/aaa $ vsql -c "SELECT get_config_parameter('LockTimeout')"
     get_config_parameter 
    ----------------------
     300
    
  • joergschaberjoergschaber Vertica Customer

    Thanks! It's the default: 300.

  • joergschaberjoergschaber Vertica Customer

    We tried your suggestion and it worked nicely! We could now develop and test a solution that handles stalled truncate queries. Thanks!

Leave a Comment

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