lock.sgml

<REFENTRY ID="SQL-LOCK">
<REFMETA>
<REFENTRYTITLE>
LOCK
</REFENTRYTITLE>
<REFMISCINFO>SQL - Language Statements</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>
LOCK
</REFNAME>
<REFPURPOSE>
Explicit lock of a table inside a transaction
</REFPURPOSE>

<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1998-09-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
LOCK [ TABLE ] <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
</SYNOPSIS>

<REFSECT2 ID="R2-SQL-LOCK-1">
<REFSECT2INFO>
<DATE>1998-09-01</DATE>
</REFSECT2INFO>
<TITLE>
Inputs
</TITLE>
<PARA>
</PARA>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
<REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
          The name of an existing table to lock.
</VARIABLELIST>

</REFSECT2>

<REFSECT2 ID="R2-SQL-LOCK-2">
<REFSECT2INFO>
<DATE>1998-09-24</DATE>
</REFSECT2INFO>
<TITLE>
Outputs
</TITLE>
<PARA>

<VARIABLELIST>
<VARLISTENTRY>
<TERM>
<replaceable>status</replaceable>
</TERM>
<LISTITEM>
<PARA>

<VARIABLELIST>
<VARLISTENTRY>
<TERM>
DELETE 0
</TERM>
<LISTITEM>
<PARA>
Message returned on a successful lock.
<command>LOCK</command> is implemented as a 
<command>DELETE FROM <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE></command>
which is guaranteed to not delete any rows.

<VARLISTENTRY>
<TERM>
ERROR <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>: Table does not exist.
</TERM>
<LISTITEM>
<PARA>
Message returned if <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
 does not exist.

</VARIABLELIST>
</VARIABLELIST>

</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-SQL-LOCK-1">
<REFSECT1INFO>
<DATE>1998-09-24</DATE>
</REFSECT1INFO>
<TITLE>
Description
</TITLE>
<PARA>
   <command>LOCK</command> locks in exclusive mode a table inside
   a transaction. The classic use for this is
   the case where you want to select some data, then
   update it inside a transaction.
   If you don't explicit lock a table using LOCK statement, it will be
   implicit locked only at the first
 <command>UPDATE</command>, <command>INSERT</command>,
 or <command>DELETE</command> operation.
   If you don't exclusive lock the table before the select, some
   other user may also read the selected data, and try and do
   their own update, causing a deadlock while you both wait
   for the other to release the select-induced shared lock so
   you can get an exclusive lock to do the update.

<para>
   Another example of deadlock is where one user locks one
   table, and another user locks a second table. While both
   keep their existing locks, the first user tries to lock
   the second user's table, and the second user tries to lock
   the first user's table. Both users deadlock waiting for
   the tables to become available. The only solution to this
   is for both users to lock tables in the same order, so
   user's lock acquisitions and requests to not form a deadlock.

<note>
<para>
<productname>Postgres</productname> does detect deadlocks and will
rollback transactions to resolve the deadlock. Usually, at least one
of the deadlocked transactions will complete successfully.
</note>

<REFSECT2 ID="R2-SQL-LOCK-3">
<REFSECT2INFO>
<DATE>1998-09-24</DATE>
</REFSECT2INFO>
<TITLE>
Notes
</TITLE>
<PARA>
<command>LOCK</command> is a <productname>Postgres</productname>
 language extension.
<para>
<command>LOCK</command> works only inside transactions.

<note>
<title>Bug</title>
<para>
If the locked table is dropped then it will be automatically
            unlocked even if a transaction is still in progress.
</note>

</REFSECT2>

<REFSECT1 ID="R1-SQL-LOCK-2">
<TITLE>
Usage
</TITLE>
<PARA>
</PARA>
<ProgramListing>
--Explicit locking to prevent deadlock:
--
BEGIN WORK;
    LOCK films;
    SELECT * FROM films;
    UPDATE films SET len = INTERVAL '100 minute'
        WHERE len = INTERVAL '117 minute';
COMMIT WORK;
</ProgramListing>
        
</REFSECT1>

<REFSECT1 ID="R1-SQL-LOCK-3">
<TITLE>
Compatibility
</TITLE>
<PARA>

<REFSECT2 ID="R2-SQL-LOCK-4">
<REFSECT2INFO>
<DATE>1998-09-24</DATE>
</REFSECT2INFO>
<TITLE>
SQL92
</TITLE>
<PARA>
   There is no <command>LOCK TABLE</command> in <acronym>SQL92</acronym>,
 which instead uses <command>SET TRANSACTION</command> to specify
   concurrency level on transactions.

</REFENTRY>