LOCK - Explicitly lock a table inside a transaction


SYNOPSIS

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

   INPUTS
       name   The name of an existing table to lock.

       ACCESS SHARE MODE

              Note: This lock mode is acquired automatically over
              tables being queried.

       This is the least restrictive lock mode. It conflicts only
       with  ACCESS EXCLUSIVE mode. It is used to protect a table
       from being modified by concurrent ALTER TABLE, DROP  TABLE
       and VACUUM commands.

       ROW SHARE MODE

              Note:   Automatically   acquired   by  SELECT...FOR
              UPDATE.  While it is a shared lock, may be upgraded
              later to a ROW EXCLUSIVE lock.

       Conflicts  with EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

       ROW EXCLUSIVE MODE

              Note: Automatically acquired by UPDATE, DELETE, and
              INSERT statements.

       Conflicts  with  SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and
       ACCESS EXCLUSIVE modes.

       SHARE MODE

              Note:  Automatically  acquired  by  CREATE   INDEX.
              Share-locks the entire table.

       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

              Note: This is like EXCLUSIVE MODE, but allows SHARE
              ROW locks by others.

       Conflicts with ROW EXCLUSIVE, SHARE, SHARE ROW  EXCLUSIVE,


       EXCLUSIVE MODE

              Note:  This mode is yet more restrictive than SHARE
              ROW  EXCLUSIVE.   It  blocks  all  concurrent   ROW
              SHARE/SELECT...FOR UPDATE queries.

       Conflicts  with ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW
       EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE modes.

       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 name: Table does not exist.
              Message returned if name does not exist.


DESCRIPTION

       LOCK TABLE controls concurrent access to a table  for  the
       duration of a transaction.  Postgres always uses the least
       restrictive lock mode whenever possible. LOCK  TABLE  pro­
       vides for cases when you might need more restrictive lock­
       ing.

       RDBMS locking uses the following terminology:

       EXCLUSIVE
              Exclusive lock that prevents other locks from being
              granted.

       SHARE  Allows  others  to  share  lock. Prevents EXCLUSIVE
              locks.

       ACCESS Locks table schema.

       ROW    Locks individual rows.

              EXCLUSIVE is assumed.  Locks exist for the duration
              of the transaction.

       For example, an application runs  a  transaction  at  READ
       COMMITTED  isolation  level  and needs to ensure the exis­
       tence 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 name IN SHARE
       MODE statement will wait until any concurrent write opera­
       tions 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 executing 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 a concurrent SHARE lock.

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

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

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

         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 TRANSACTION [set_transac­
       tion(l)] for details.


Man(1) output converted with man2html