LOCK(l)                                                   LOCK(l)

NAME
       LOCK - Explicit lock of a table inside a transaction

SYNOPSIS
       LOCK [ TABLE ] table
       LOCK [ TABLE ] table IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE
       LOCK [ TABLE ] table IN SHARE ROW EXCLUSIVE MODE

   INPUTS
       table  The name of an existing table to lock.

       ACCESS SHARE MODE

              Note: This lock mode is acquired automatically over
              tables being queried.  Postgres releases  automati-
              cally  acquired ACCESS SHARE locks after the state-
              ment is done.

       This is the least restrictive lock  mode  which  conflicts
       only with ACCESS EXCLUSIVE mode. It is intended to protect
       a table being queried from concurrent  ALTER  TABLE,  DROP
       TABLE and VACUUM statements over the same table.

       ROW SHARE MODE

              Note:  Automatically  acquired  by  any  SELECT FOR
              UPDATE statement.

       Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock  modes.

       ROW EXCLUSIVE MODE

              Note: Automatically acquired by any UPDATE, DELETE,
              INSERT statement.

       Conflicts with SHARE, SHARE ROW EXCLUSIVE,  EXCLUSIVE  and
       ACCESS EXCLUSIVE modes. Generally means that a transaction
       updated or inserted some tuples in a table.

       SHARE MODE

              Note: Automatically acquired by  any  CREATE  INDEX
              statement.

       Conflicts  with ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLU-
       SIVE and ACCESS EXCLUSIVE  modes.  This  mode  protects  a
       table against concurrent updates.

       SHARE ROW EXCLUSIVE MODE
              Conflicts  with  ROW  EXCLUSIVE,  SHARE,  SHARE ROW
              EXCLUSIVE, EXCLUSIVE and  ACCESS  EXCLUSIVE  modes.
              This  mode  is  more  restrictive  than  SHARE mode
              because of only one transaction at  time  can  hold
              this lock.

       EXCLUSIVE MODE
              Conflicts  with  ROW  SHARE,  ROW EXCLUSIVE, SHARE,
              SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE
              modes.  This mode is yet more restrictive than that
              of SHARE ROW EXCLUSIVE; it  blocks  all  concurrent
              SELECT FOR UPDATE queries.

       ACCESS EXCLUSIVE MODE

              Note:  Automatically  acquired by ALTER TABLE, DROP
              TABLE, VACUUM statements.

       This is the most restrictive  lock  mode  which  conflicts
       with all other lock modes and protects a locked table from
       any concurrent operations.

              Note: This lock mode is also acquired by an unqual-
              ified  LOCK  TABLE  (i.e.  the  command  without an
              explicit lock mode option).

   OUTPUTS
       LOCK TABLE
              The lock was successfully applied.

       ERROR table: Table does not exist.
              Message returned if table does not exist.

DESCRIPTION
       Postgres always uses the least restrictive lock mode when-
       ever  possible.  LOCK  TABLE  provided  for cases when you
       might need more restrictive locking.

       For example, an application runs  a  transaction  at  READ
       COMMITTED  isolation  level  and needs to ensure the exis-
       tance of data in a table for the duration of the  transac-
       tion.  To  achieve this you could use SHARE lock mode over
       the table before querying. This  will  protect  data  from
       concurrent changes and provide any further read operations
       over the table with data in their  actual  current  state,
       because  SHARE  lock mode conflicts with any ROW EXCLUSIVE
       one acquired by writers, and  your  LOCK  TABLE  table  IN
       SHARE  MODE statement will wait until any concurrent write
       operations commit or rollback.

              Note: To read data in their real current state when
              running a transaction at the SERIALIZABLE isolation
              level you have to execute a  LOCK  TABLE  statement
              before execution any DML statement, when the trans-
              action defines what concurrent changes will be vis-
              ible to itself.

       In addition to the requirements above, if a transaction is
       going to change data in a table then SHARE  ROW  EXCLUSIVE
       lock  mode  should  be acquired to prevent deadlock condi-
       tions when two concurrent transactions attempt to lock the
       table  in  SHARE  mode and then try to change data in this
       table, both (implicitly) acquiring ROW EXCLUSIVE lock mode
       that conflicts with concurrent SHARE lock.

       To  continue  with the deadlock (when two transaction wait
       one another) issue raised above,  you  should  follow  two
       general rules to prevent deadlock conditions:

       o Transactions  have  to acquire locks on the same objects
         in the same order.

         For example, if one application updates row R1 and  than
         updates row R2 (in the same transaction) then the second
         application shouldn't update row R2  if  it's  going  to
         update row R1 later (in a single transaction).  Instead,
         it should update rows R1 and R2 in the same order as the
         first application.

       o Transactions  should  acquire two conflicting lock modes
         only if one of them is  self-conflicting  (i.e.  may  be
         held  by one transaction at time only). If multiple lock
         modes are  involved,  then  transactions  should  always
         acquire the most restrictive mode first.

         An  example for this rule was given previously when dis-
         cussing the use of SHARE ROW EXCLUSIVE mode rather  than
         SHARE mode.

         Note:  Postgres  does detect deadlocks and will rollback
         at least one waiting transaction to  resolve  the  dead-
         lock.

   NOTES
       LOCK is a Postgres language extension.

       Except  for  ACCESS  SHARE/EXCLUSIVE lock modes, all other
       Postgres lock modes and the LOCK TABLE syntax are compati-
       ble with those present in Oracle.

       LOCK works only inside transactions.

USAGE
       Illustrate  a SHARE lock on a primary key table when going
       to perform inserts into a foreign key table:

       BEGIN WORK;
       LOCK TABLE films IN SHARE MODE;
       SELECT id FROM films
           WHERE name = 'Star Wars: Episode I - The Phantom Menace';
       -- Do ROLLBACK if record was not returned
       INSERT INTO films_user_comments VALUES
           (_id_, 'GREAT! I was waiting for it for so long!');
       COMMIT WORK;

       Take a SHARE ROW EXCLUSIVE lock on  a  primary  key  table
       when going to perform a delete operation:

       BEGIN WORK;
       LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
       DELETE FROM films_user_comments WHERE id IN
           (SELECT id FROM films WHERE rating < 5);
       DELETE FROM films WHERE rating < 5;
       COMMIT WORK;

COMPATIBILITY
   SQL92
       There  is  no  LOCK TABLE in SQL92, which instead uses SET
       TRANSACTION to specify concurrency levels on transactions.
       We support that too; see set(l) for details.

SQL - Language Statements 15 August 1999                        1