Thread: Table Lock?

How do I lock a table (and not just the row) with a select in PostgreSQL?


Regards,

BTJ

This isn't a jdbc specific question and probably should have been asked
on pgsql-general, or pgsql-sql.  However according to the documentation
if you want to lock a table you use the lock sql command.  But you also
might mean to lock the rows in a table that a query returns in which you
would use the 'for update' clause select.  Both are documents in the SQL
Commands chapter of the postgresql reference manual.  I have included
the doc for the lock command below.

thanks,
--Barry


LOCK
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 (optionally schema-qualified) of an existing table to lock.
ACCESS SHARE MODE

     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.

         Note: The SELECT command acquires a lock of this mode on
referenced tables. In general, any query that only reads a table and
does not modify it will acquire this lock mode.

ROW SHARE MODE

     Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

         Note: The SELECT FOR UPDATE command acquires a lock of this
mode on the target table(s) (in addition to ACCESS SHARE locks on any
other tables that are referenced but not selected FOR UPDATE).

ROW EXCLUSIVE MODE

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

         Note: The commands UPDATE, DELETE, and INSERT acquire this lock
mode on the target table (in addition to ACCESS SHARE locks on any other
referenced tables). In general, this lock mode will be acquired by any
query that modifies the data in a table.

SHARE UPDATE EXCLUSIVE MODE

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

         Note: Acquired by VACUUM (without FULL).

SHARE MODE

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

         Note: Acquired by CREATE INDEX.

SHARE ROW EXCLUSIVE MODE

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

         Note: This lock mode is not automatically acquired by any
PostgreSQL command.

EXCLUSIVE MODE

     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.

         Note: This lock mode is not automatically acquired by any
PostgreSQL command.

ACCESS EXCLUSIVE MODE

     Conflicts with all lock modes. This mode guarantees that the holder
is the only transaction accessing the table in any way.

         Note: Acquired by ALTER TABLE, DROP TABLE, and VACUUM FULL
statements. This is also the default lock mode for LOCK TABLE statements
that do not specify a mode explicitly.

Outputs

LOCK TABLE

     The lock was successfully acquired.
ERROR name: Table does not exist.

     Message returned if name does not exist.

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 READ COMMITTED
isolation level 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 prevent
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 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.

     Note: To achieve a similar effect 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. A later LOCK will still prevent concurrent writes --- but it
won't ensure that what the transaction reads corresponds to the latest
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.)

Two general rules may be followed 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.
     *

       If multiple lock modes are involved for a single object, 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.

PostgreSQL does detect deadlocks and will rollback at least one waiting
transaction to resolve the deadlock. If it is not practical to code an
application to follow the above rules strictly, an alternative solution
is to be prepared to retry transactions when they are aborted by deadlocks.

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.

RDBMS locking uses the following standard terminology:

EXCLUSIVE

     An exclusive lock prevents other locks of the same type from being
granted.
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.

PostgreSQL does not follow this terminology exactly. 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 does not follow this naming convention
accurately, since it 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.
Usage

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


Bjørn T Johansen wrote:
> How do I lock a table (and not just the row) with a select in
> PostgreSQL?
>
>
> Regards,
>
> BTJ
>