Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Слідкувати 1
В обрані 0
Вікі 捐赠
gooderp18绿色标准版
Ви не можете вибрати більше 25 тем Теми мають розпочинатися з літери або цифри, можуть містити дефіси (-) і не повинні перевищувати 35 символів.
Гілка: master
Гілки Теги
${ item.name }
Створити гілку ${ searchTerm }
з 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/sql-lock.html

162 lines
13KB
Неформатований Постійне посилання Blame Історія 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>LOCK</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="sql-load.html" title="LOAD" /><link rel="next" href="sql-move.html" title="MOVE" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">LOCK</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-load.html" title="LOAD">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-move.html" title="MOVE">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-LOCK"><div class="titlepage"></div><a id="id-1.9.3.155.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">LOCK</span></h2><p>LOCK — lock a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">

  3. LOCK [ TABLE ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ] [, ...] [ IN <em class="replaceable"><code>lockmode</code></em> MODE ] [ NOWAIT ]

  4. 

  5. <span class="phrase">where <em class="replaceable"><code>lockmode</code></em> is one of:</span>

  6. 

  7.     ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE

  8.     | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

  9. </pre></div><div class="refsect1" id="id-1.9.3.155.5"><h2>Description</h2><p>

  10.    <code class="command">LOCK TABLE</code> obtains a table-level lock, waiting

  11.    if necessary for any conflicting locks to be released.  If

  12.    <code class="literal">NOWAIT</code> is specified, <code class="command">LOCK

  13.    TABLE</code> does not wait to acquire the desired lock: if it

  14.    cannot be acquired immediately, the command is aborted and an

  15.    error is emitted.  Once obtained, the lock is held for the

  16.    remainder of the current transaction.  (There is no <code class="command">UNLOCK

  17.    TABLE</code> command; locks are always released at transaction

  18.    end.)

  19.   </p><p>

  20.    When a view is locked, all relations appearing in the view definition

  21.    query are also locked recursively with the same lock mode.

  22.   </p><p>

  23.    When acquiring locks automatically for commands that reference

  24.    tables, <span class="productname">PostgreSQL</span> always uses the least

  25.    restrictive lock mode possible. <code class="command">LOCK TABLE</code>

  26.    provides for cases when you might need more restrictive locking.

  27.    For example, suppose an application runs a transaction at the

  28.    <code class="literal">READ COMMITTED</code> isolation level and needs to ensure that

  29.    data in a table remains stable for the duration of the transaction.

  30.    To achieve this you could obtain <code class="literal">SHARE</code> lock mode over the

  31.    table before querying. This will prevent concurrent data changes

  32.    and ensure subsequent reads of the table see a stable view of

  33.    committed data, because <code class="literal">SHARE</code> lock mode conflicts with

  34.    the <code class="literal">ROW EXCLUSIVE</code> lock acquired by writers, and your

  35.    <code class="command">LOCK TABLE <em class="replaceable"><code>name</code></em> IN SHARE MODE</code>

  36.    statement will wait until any concurrent holders of <code class="literal">ROW

  37.    EXCLUSIVE</code> mode locks commit or roll back. Thus, once you

  38.    obtain the lock, there are no uncommitted writes outstanding;

  39.    furthermore none can begin until you release the lock.

  40.   </p><p>

  41.    To achieve a similar effect when running a transaction at the

  42.    <code class="literal">REPEATABLE READ</code> or <code class="literal">SERIALIZABLE</code>

  43.    isolation level, you have to execute the <code class="command">LOCK TABLE</code> statement

  44.    before executing any <code class="command">SELECT</code> or data modification statement.

  45.    A <code class="literal">REPEATABLE READ</code> or <code class="literal">SERIALIZABLE</code> transaction's

  46.    view of data will be frozen when its first

  47.    <code class="command">SELECT</code> or data modification statement begins.  A <code class="command">LOCK

  48.    TABLE</code> later in the transaction will still prevent concurrent writes

  49.    — but it won't ensure that what the transaction reads corresponds to

  50.    the latest committed values.

  51.   </p><p>

  52.    If a transaction of this sort is going to change the data in the

  53.    table, then it should use <code class="literal">SHARE ROW EXCLUSIVE</code> lock mode

  54.    instead of <code class="literal">SHARE</code> mode.  This ensures that only one

  55.    transaction of this type runs at a time.  Without this, a deadlock

  56.    is possible: two transactions might both acquire <code class="literal">SHARE</code>

  57.    mode, and then be unable to also acquire <code class="literal">ROW EXCLUSIVE</code>

  58.    mode to actually perform their updates.  (Note that a transaction's

  59.    own locks never conflict, so a transaction can acquire <code class="literal">ROW

  60.    EXCLUSIVE</code> mode when it holds <code class="literal">SHARE</code> mode — but not

  61.    if anyone else holds <code class="literal">SHARE</code> mode.)  To avoid deadlocks,

  62.    make sure all transactions acquire locks on the same objects in the

  63.    same order, and if multiple lock modes are involved for a single

  64.    object, then transactions should always acquire the most

  65.    restrictive mode first.

  66.   </p><p>

  67.    More information about the lock modes and locking strategies can be

  68.    found in <a class="xref" href="explicit-locking.html" title="13.3. Explicit Locking">Section 13.3</a>.

  69.   </p></div><div class="refsect1" id="id-1.9.3.155.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>

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

  71.       lock. If <code class="literal">ONLY</code> is specified before the table name, only that

  72.       table is locked. If <code class="literal">ONLY</code> is not specified, the table and all

  73.       its descendant tables (if any) are locked.  Optionally, <code class="literal">*</code>

  74.       can be specified after the table name to explicitly indicate that

  75.       descendant tables are included.

  76.      </p><p>

  77.       The command <code class="literal">LOCK TABLE a, b;</code> is equivalent to

  78.       <code class="literal">LOCK TABLE a; LOCK TABLE b;</code>. The tables are locked

  79.       one-by-one in the order specified in the <code class="command">LOCK

  80.       TABLE</code> command.

  81.      </p></dd><dt><span class="term"><em class="replaceable"><code>lockmode</code></em></span></dt><dd><p>

  82.       The lock mode specifies which locks this lock conflicts with.

  83.       Lock modes are described in <a class="xref" href="explicit-locking.html" title="13.3. Explicit Locking">Section 13.3</a>.

  84.      </p><p>

  85.       If no lock mode is specified, then <code class="literal">ACCESS

  86.       EXCLUSIVE</code>, the most restrictive mode, is used.

  87.      </p></dd><dt><span class="term"><code class="literal">NOWAIT</code></span></dt><dd><p>

  88.       Specifies that <code class="command">LOCK TABLE</code> should not wait for

  89.       any conflicting locks to be released: if the specified lock(s)

  90.       cannot be acquired immediately without waiting, the transaction

  91.       is aborted.

  92.      </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.155.7"><h2>Notes</h2><p>

  93.     <code class="literal">LOCK TABLE ... IN ACCESS SHARE MODE</code> requires <code class="literal">SELECT</code>

  94.     privileges on the target table.  <code class="literal">LOCK TABLE ... IN ROW EXCLUSIVE

  95.     MODE</code> requires <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>,

  96.     or <code class="literal">TRUNCATE</code> privileges on the target table. All other forms of

  97.     <code class="command">LOCK</code> require table-level <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>,

  98.     or <code class="literal">TRUNCATE</code> privileges.

  99.    </p><p>

  100.     The user performing the lock on the view must have the corresponding privilege

  101.     on the view.  In addition the view's owner must have the relevant privileges on

  102.     the underlying base relations, but the user performing the lock does

  103.     not need any permissions on the underlying base relations.

  104.    </p><p>

  105.     <code class="command">LOCK TABLE</code> is useless outside a transaction block: the lock

  106.     would remain held only to the completion of the statement.  Therefore

  107.     <span class="productname">PostgreSQL</span> reports an error if <code class="command">LOCK</code>

  108.     is used outside a transaction block.

  109.     Use

  110.     <a class="xref" href="sql-begin.html" title="BEGIN"><span class="refentrytitle">BEGIN</span></a> and

  111.     <a class="xref" href="sql-commit.html" title="COMMIT"><span class="refentrytitle">COMMIT</span></a>

  112.     (or <a class="xref" href="sql-rollback.html" title="ROLLBACK"><span class="refentrytitle">ROLLBACK</span></a>)

  113.     to define a transaction block.

  114.    </p><p>

  115.    <code class="command">LOCK TABLE</code> only deals with table-level locks, and so

  116.    the mode names involving <code class="literal">ROW</code> are all misnomers.  These

  117.    mode names should generally be read as indicating the intention of

  118.    the user to acquire row-level locks within the locked table.  Also,

  119.    <code class="literal">ROW EXCLUSIVE</code> mode is a shareable table lock.  Keep in

  120.    mind that all the lock modes have identical semantics so far as

  121.    <code class="command">LOCK TABLE</code> is concerned, differing only in the rules

  122.    about which modes conflict with which. For information on how to

  123.    acquire an actual row-level lock, see <a class="xref" href="explicit-locking.html#LOCKING-ROWS" title="13.3.2. Row-Level Locks">Section 13.3.2</a>

  124.    and the <a class="xref" href="sql-select.html#SQL-FOR-UPDATE-SHARE" title="The Locking Clause">The Locking Clause</a> in the <code class="command">SELECT</code>

  125.    reference documentation.

  126.   </p></div><div class="refsect1" id="id-1.9.3.155.8"><h2>Examples</h2><p>

  127.    Obtain a <code class="literal">SHARE</code> lock on a primary key table when going to perform

  128.    inserts into a foreign key table:

  129. 

  130. </p><pre class="programlisting">

  131. BEGIN WORK;

  132. LOCK TABLE films IN SHARE MODE;

  133. SELECT id FROM films

  134.     WHERE name = 'Star Wars: Episode I - The Phantom Menace';

  135. -- Do ROLLBACK if record was not returned

  136. INSERT INTO films_user_comments VALUES

  137.     (_id_, 'GREAT! I was waiting for it for so long!');

  138. COMMIT WORK;

  139. </pre><p>

  140.   </p><p>

  141.    Take a <code class="literal">SHARE ROW EXCLUSIVE</code> lock on a primary key table when going to perform

  142.    a delete operation:

  143. 

  144. </p><pre class="programlisting">

  145. BEGIN WORK;

  146. LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;

  147. DELETE FROM films_user_comments WHERE id IN

  148.     (SELECT id FROM films WHERE rating &lt; 5);

  149. DELETE FROM films WHERE rating &lt; 5;

  150. COMMIT WORK;

  151. </pre></div><div class="refsect1" id="id-1.9.3.155.9"><h2>Compatibility</h2><p>

  152.    There is no <code class="command">LOCK TABLE</code> in the SQL standard,

  153.    which instead uses <code class="command">SET TRANSACTION</code> to specify

  154.    concurrency levels on transactions.  <span class="productname">PostgreSQL</span> supports that too;

  155.    see <a class="xref" href="sql-set-transaction.html" title="SET TRANSACTION"><span class="refentrytitle">SET TRANSACTION</span></a> for details.

  156.   </p><p>

  157.    Except for <code class="literal">ACCESS SHARE</code>, <code class="literal">ACCESS EXCLUSIVE</code>,

  158.    and <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock modes, the

  159.    <span class="productname">PostgreSQL</span> lock modes and the

  160.    <code class="command">LOCK TABLE</code> syntax are compatible with those

  161.    present in <span class="productname">Oracle</span>.

  162.   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-load.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-move.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">LOAD </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> MOVE</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1