Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Querying while Dropping Table / Table Locking

Hi - 

 

I have the need to 'refresh' data in a table on a cron job. The cron job drops the table and rebuilds it / reloads the data. We have found this is the simplest way to do it.

 

My question is - if a user is running a long rnning query on the table, and another user comes and runs drop table, what is the behavior of vertica? I think this involves asking what type of locking is going on in each scenario.

 

Thanks.

Comments

  • Hi

    DROP = X  (Exclusive ) lock ,SELECT = S (Shared Lock ) , during  the long select query , you will not be able to drop the table . You can see more details on https://my.vertica.com/docs/7.2.x/HTML/Content/Authoring/ConceptsGuide/Components/Locks/LockModes.htm

     

    I hope you will find it useful

     

    Thanks 

  • SELECT statements only take S locks if running in SERIALIZABLE transaction mode, which isn't typical.  The default is READ COMMITTED.  From the documentation link that Eli posted:

     

    "Select operations in READ COMMITTED transaction mode do not require S table locks. See Transactions in Vertica Concepts for more information."

     

      --Sharon

     

  • Okay - so i'm a bit confused here. The question is, what behavior happens when a user runs a select query, and mid-query, in another session a user runs a drop table.

     

    It seems that under https://my.vertica.com/docs/7.2.x/HTML/Content/Authoring/ConceptsGuide/Other/Transactions.htm it says that a dirty read is not possible for 'Read Committed' (Default). However, I don't see anything here about dropping the entire table (does a drop table amount to something else behind the scenes?) - the page only mentions protection from dirty reads of a 'COPY' or 'REFRESH' command.

  • Hi ,

    For READ COMMITTED  the query will  get an error  ( as no lock is take place ):

     

    ERROR 4566:  Relation "<Your table Name> " does not exist

     

    You can protect your query with SERIALIZABLE isolation level .

     

     

    Thanks 

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.