Querying while Dropping Table / Table Locking

nartz

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.

eli_revach

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 

Sharon_Cutter

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

 

nartz

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.

eli_revach

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