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.




  • Options


    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



  • Options

    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."




  • Options

    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.

  • Options

    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 .




Leave a Comment

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