Table Locks

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 .

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

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.

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 ?

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 .

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 ?

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

You can explicity create the projection after table is created