/usr/man/cat.l/lock.l.Z(/usr/man/cat.l/lock.l.Z)

NAME

       LOCK - lock a table

SYNOPSIS

       LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ]

       where lockmode is one of:

           ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
           | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

DESCRIPTION

       LOCK  TABLE  obtains  a  table-level lock, waiting if necessary for any
       conflicting locks to be released. Once obtained, the lock is  held  for
       the  remainder  of  the current transaction.  (There is no UNLOCK TABLE
       command; locks are always released at transaction end.)

       When acquiring locks automatically for commands that reference  tables,
       PostgreSQL  always  uses the least restrictive lock mode possible. LOCK
       TABLE provides for cases when you might need more restrictive  locking.
       For example, suppose an application runs a transaction at the isolation
       level read committed and needs to ensure that data in a  table  remains
       stable  for  the duration of the transaction. To achieve this you could
       obtain SHARE lock mode over the table before querying. This  will  pre-
       vent  concurrent  data changes and ensure subsequent reads of the table
       see a stable view of committed data, because SHARE lock mode  conflicts
       with  the  ROW  EXCLUSIVE lock acquired by writers, and your LOCK TABLE
       name IN SHARE MODE statement will wait until any concurrent holders  of
       ROW EXCLUSIVE mode locks commit or roll back. Thus, once you obtain the
       lock, there are no uncommitted writes outstanding; furthermore none can
       begin until you release the lock.

       To achieve a similar effect when running a transaction at the isolation
       level serializable, you have to execute the LOCK TABLE statement before
       executing any data modification statement. A serializable transaction's
       view of data will be frozen when its first data modification  statement
       begins. A later LOCK TABLE will still prevent concurrent writes --- but
       it won't ensure that what the transaction reads corresponds to the lat-
       est committed values.

       If a transaction of this sort is going to change the data in the table,
       then it should use SHARE ROW EXCLUSIVE lock mode instead of SHARE mode.
       This  ensures  that  only  one transaction of this type runs at a time.
       Without this, a deadlock  is  possible:  two  transactions  might  both
       acquire  SHARE  mode,  and then be unable to also acquire ROW EXCLUSIVE
       mode to actually perform their updates. (Note that a transaction's  own
       locks  never  conflict, so a transaction can acquire ROW EXCLUSIVE mode
       when it holds SHARE mode --- but not if anyone else holds SHARE  mode.)
       To  avoid  deadlocks,  make  sure all transactions acquire locks on the
       same objects in the same order, and if multiple lock modes are involved
       for  a  single object, then transactions should always acquire the most
       restrictive mode first.

       More information about the lock modes and  locking  strategies  can  be
       found  in the section called ``Explicit Locking'' in the documentation.

PARAMETERS

       name   The name (optionally schema-qualified) of an existing  table  to
              lock.

              The  command  LOCK  a,  b; is equivalent to LOCK a; LOCK b;. The
              tables are locked one-by-one in the order specified in the  LOCK
              command.

       lockmode
              The  lock  mode  specifies which locks this lock conflicts with.
              Lock modes are described in the section called ``Explicit  Lock-
              ing'' in the documentation.

              If  no  lock  mode is specified, then ACCESS EXCLUSIVE, the most
              restrictive mode, is used.

NOTES

       LOCK ... IN ACCESS SHARE MODE requires SELECT privileges on the  target
       table. All other forms of LOCK require UPDATE and/or DELETE privileges.

       LOCK is useful only inside a  transaction  block  (BEGIN/COMMIT  pair),
       since  the lock is dropped as soon as the transaction ends. A LOCK com-
       mand appearing outside any transaction  block  forms  a  self-contained
       transaction, so the lock will be dropped as soon as it is obtained.

       LOCK  TABLE  only  deals  with table-level locks, and so the mode names
       involving ROW are all misnomers. These mode names should  generally  be
       read as indicating the intention of the user to acquire row-level locks
       within the locked table. Also, ROW EXCLUSIVE mode is a  sharable  table
       lock.  Keep in mind that all the lock modes have identical semantics so
       far as LOCK TABLE is concerned, differing only in the rules about which
       modes conflict with which.

EXAMPLES

       Obtain  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

       There  is  no  LOCK  TABLE  in the SQL standard, which instead uses SET
       TRANSACTION to specify concurrency levels on  transactions.  PostgreSQL
       supports   that  too;  see  SET  TRANSACTION  [set_transaction(l)]  for
       details.

       Except for ACCESS SHARE, ACCESS EXCLUSIVE, and SHARE  UPDATE  EXCLUSIVE
       lock  modes,  the  PostgreSQL  lock modes and the LOCK TABLE syntax are
       compatible with those present in Oracle.

SQL - Language Statements         2003-11-02                           LOCK(l)