File: sql-lock.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (336 lines) | stat: -rw-r--r-- 5,098 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>LOCK</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="LOAD"
HREF="sql-load.html"><LINK
REL="NEXT"
TITLE="MOVE"
HREF="sql-move.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-load.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-move.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>LOCK</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>LOCK &#8212; Explicit lock of a table inside a transaction</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>LOCK [ TABLE ] <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
></P
><P
></P
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></DT
><DD
><P
>          The name of an existing table to lock.</P
></DD
></DL
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
>DELETE 0</DT
><DD
><P
>Message returned on a successful lock.
<B
CLASS="COMMAND"
>LOCK</B
> is implemented as a 
<B
CLASS="COMMAND"
>DELETE FROM <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></B
>
which is guaranteed to not delete any rows.&#13;</P
></DD
><DT
>ERROR <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>: Table does not exist.</DT
><DD
><P
>Message returned if <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
 does not exist.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
>   <B
CLASS="COMMAND"
>LOCK</B
> 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
 <B
CLASS="COMMAND"
>UPDATE</B
>, <B
CLASS="COMMAND"
>INSERT</B
>,
 or <B
CLASS="COMMAND"
>DELETE</B
> 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.&#13;</P
><P
>   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.

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> does detect deadlocks and will
rollback transactions to resolve the deadlock. Usually, at least one
of the deadlocked transactions will complete successfully.</P
></BLOCKQUOTE
>&#13;</P
><DIV
CLASS="REFSECT2"
><H3
>Notes</H3
><P
><B
CLASS="COMMAND"
>LOCK</B
> is a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
 language extension.</P
><P
><B
CLASS="COMMAND"
>LOCK</B
> works only inside transactions.

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Bug: </B
>If the locked table is dropped then it will be automatically
            unlocked even if a transaction is still in progress.</P
></BLOCKQUOTE
>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
></P
><PRE
CLASS="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;</PRE
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><P
></P
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
>   There is no <B
CLASS="COMMAND"
>LOCK TABLE</B
> in <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
>,
 which instead uses <B
CLASS="COMMAND"
>SET TRANSACTION</B
> to specify
   concurrency level on transactions.&#13;</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-load.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-move.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>LOAD</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>MOVE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>