Index for Section 5

Alphabetical listing for L

Bottom of page

LOCK(5)
NAME
  LOCK - explicitly lock a table

SYNOPSIS
  LOCK [ TABLE ] name [, ...]
  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

  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 FULL commands.

  ROW SHARE MODE

       Note: Automatically acquired by SELECT ... FOR UPDATE.

  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 UPDATE EXCLUSIVE MODE

       Note: Automatically acquired by VACUUM (without FULL).

  Conflicts with SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE,
  EXCLUSIVE and ACCESS EXCLUSIVE modes. This mode protects a table against
  concurrent schema changes and VACUUMs.

  SHARE MODE

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

  Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE,
  EXCLUSIVE and ACCESS EXCLUSIVE modes. This mode protects a table against
  concurrent data updates.

  SHARE ROW EXCLUSIVE MODE

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

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

  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 UPDATE EXCLUSIVE, SHARE,
  SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE modes.  This mode
  allows only concurrent ACCESS SHARE, i.e., only reads from the table can
  proceed in parallel with a transaction holding this lock mode.

  ACCESS EXCLUSIVE MODE

       Note: Automatically acquired by ALTER TABLE, DROP TABLE, VACUUM FULL
       statements.  This is the most restrictive lock mode which protects a
       locked table from any concurrent operations.

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

  Conflicts with all lock modes.

  OUTPUTS

  LOCK TABLE
       The lock was successfully acquired.

  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.	PostgreSQL always uses the least restrictive lock mode
  whenever possible. LOCK TABLE provides for cases when you might need more
  restrictive locking.

  RDBMS locking uses the following terminology:

  EXCLUSIVE
       An exclusive lock prevents other locks of the same type from being
       granted. (Note: ROW EXCLUSIVE mode does not follow this naming
       convention perfectly, since it is shared at the level of the table; it
       is exclusive only with respect to specific rows that are being
       updated.)

  SHARE
       A shared lock allows others to also hold the same type of lock, but
       prevents the corresponding EXCLUSIVE lock from being granted.

  ACCESS
       Locks table schema.

  ROW  Locks individual rows.

  For example, suppose an application runs a transaction at READ COMMITTED
  isolation level and needs to ensure the existence of data in a table for
  the duration of the transaction. To achieve this you could obtain SHARE
  lock mode over the table before querying. This will prevent concurrent data
  changes and ensure further read operations over the table see data in their
  actual current state, because SHARE lock mode conflicts with any ROW
  EXCLUSIVE lock acquired by writers, and your LOCK TABLE name IN SHARE MODE
  statement will wait until any concurrent write operations commit or
  rollback. Thus, once you obtain the lock, there are no uncommitted writes
  outstanding.

       Note: To read data in their actual current state when running a
       transaction at the SERIALIZABLE isolation level, you have to execute
       the LOCK TABLE statement before executing any DML statement. A
       serializable transaction's view of data will be frozen when its first
       DML statement begins.

  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 conditions 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 transactions wait for 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 only one transaction at a
    time). 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 discussing the use of
    SHARE ROW EXCLUSIVE mode rather than SHARE mode.

    Note: PostgreSQL does detect deadlocks and will rollback at least one
    waiting transaction to resolve the deadlock.

  When locking multiple tables, 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.

  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), since the
  lock is dropped as soon as the transaction ends. A LOCK command appearing
  outside any transaction block forms a self-contained transaction, so the
  lock will be dropped as soon as it is obtained.

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_transaction(5)] 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(TM).

Index for Section 5

Alphabetical listing for L

Top of page