Table Locks

Navneet_1

Hi,
Is there a way to explicitly lock a table in Vertica before writing to it? The intent is to limit the write to one session at a time. I know about select for update, but what if the record does not even exist.. 

Also if you can explain how Serializable isloation level affects other sessions if at all it does (which I believe it does not) and what is the real use of it.
Thanks

eli_revach

Hi
For explicitly lock you can run LOCK TABLE <table_name>   on your session no one else on other session will be ab write to this table  until to explicitly run commit or rollback .

Serializable  will provide your constant view for all the duration of the transaction , for example , if during the transaction you access several time to the same table record , it promise you that you always see the same data .

Other session will not be able to update the table that you access .

Navneet_1

Thanks Eli.
Had to post this as I could not find Lock Table thing in the documentation (Sql ref and programming manual). 

Navneet_1

Hi Again Eli, well I tried this, but surprisingly, if I lock the table explicitly then I am not even able to write to it from the same session. Is it how it is supposed to work ? Because the sole intent of locking the table explicitly is to ensure that there is no one but current session that writes to it. 

Navneet_1

On the same topic .. I tried this, but surprisingly, if I lock the table explicitly then I am not even able to write to it from the same session. Is it how it is supposed to work ? Because the sole intent of locking the table explicitly is to ensure that there is no one but current session that writes to it. 

EDIT:: I tested it in another Database and it seems to be working fine. Any idea what will lead to this difference in the behaviour ?

eli_revach

Hi
This is not normal behave , the normal behave  is that once you able to take an  exclusive lock on a table on  the session you are the owner of it . The only option is see it is that you have some kind of auto commit on the session which release your  lock and someone else  on other session take it , so you become lock .

Navneet_1

Here are some more details:

## DB 1 Session 1::

Lock Table myschema.mytable;
Lock Table

Insert into myschema.mytable values (1, 'ABC') ;

<Session goes into wait.... >

## DB 1 Session 2::
Insert into myschema.mytable values (1, 'ABC') ;

<Session goes into wait.... >

## Back in DB 1 Session 1::
(still waiting..... )

ctrl + C

ERROR 3365:  Failed to create default projections for table myschema.mytable Canceled: [Txn 0xc000000036aee0] S lock table - canceled Canceled lock wait for Table: myschema.mytable
HINT:  Ensure that tables involved in the query and their super projections exist (DDL interference)

++++++++++++    +++++++++++      +++++++++    ++++++++

## DB 2 : Session 1
Lock Table myschema.mytable;
Lock Table

Insert into myschema.mytable values (1, 'ABC') ;

OUTPUT
--------
      1

<Data inserted .. no problem>

## DB 2 : Session 2
Insert into myschema.mytable values (1, 'ABC') ;

<goes into wait>

-----------------------------


So having said that, this amazes me why DB1 shows a different behaviour, is there a system property or something that I can check ?

eli_revach

The first time you insert data into vertica after a table was created , the super projection is created ( lead to DDL statment) that that take an exclusive lock . 

Please run you test after you have at list 1 records on your table 

Navneet_1

Thanks Eli, that did work. So is that the only way to work with it ? 
Will creating a projection explicitly help ?

Let's say I am creating the table for first time and have to lock it before any row gets into it, to ensure sequential inserts; how can this be achieved then. 
One way would be to have a dummy row in there and other which I am not sure if is correct, will be to create a projection. Is that right ?

eli_revach

You can explicity create the projection after table is created