codekingpro/portable-devtools
115k
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 Vsnapshot" /><link rel="prev" href="sql-load.html" title="LOAD" /><link rel="next" href="sql-merge.html" title="MERGE" /></head><body id="docContent" class="container-fluid col-10"><div 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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-merge.html" title="MERGE">Next</a></td></tr></table><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">3LOCK [ 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 EXCLUSIVE8 | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE9</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, waiting11 if necessary for any conflicting locks to be released. If12 <code class="literal">NOWAIT</code> is specified, <code class="command">LOCK13 TABLE</code> does not wait to acquire the desired lock: if it14 cannot be acquired immediately, the command is aborted and an15 error is emitted. Once obtained, the lock is held for the16 remainder of the current transaction. (There is no <code class="command">UNLOCK17 TABLE</code> command; locks are always released at transaction18 end.)19 </p><p>20 When a view is locked, all relations appearing in the view definition21 query are also locked recursively with the same lock mode.22 </p><p>23 When acquiring locks automatically for commands that reference24 tables, <span class="productname">PostgreSQL</span> always uses the least25 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 the28 <code class="literal">READ COMMITTED</code> isolation level and needs to ensure that29 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 the31 table before querying. This will prevent concurrent data changes32 and ensure subsequent reads of the table see a stable view of33 committed data, because <code class="literal">SHARE</code> lock mode conflicts with34 the <code class="literal">ROW EXCLUSIVE</code> lock acquired by writers, and your35 <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">ROW37 EXCLUSIVE</code> mode locks commit or roll back. Thus, once you38 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 the42 <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> statement44 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's46 view of data will be frozen when its first47 <code class="command">SELECT</code> or data modification statement begins. A <code class="command">LOCK48 TABLE</code> later in the transaction will still prevent concurrent writes49 — but it won't ensure that what the transaction reads corresponds to50 the latest committed values.51 </p><p>52 If a transaction of this sort is going to change the data in the53 table, then it should use <code class="literal">SHARE ROW EXCLUSIVE</code> lock mode54 instead of <code class="literal">SHARE</code> mode. This ensures that only one55 transaction of this type runs at a time. Without this, a deadlock56 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's59 own locks never conflict, so a transaction can acquire <code class="literal">ROW60 EXCLUSIVE</code> mode when it holds <code class="literal">SHARE</code> mode — but not61 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 the63 same order, and if multiple lock modes are involved for a single64 object, then transactions should always acquire the most65 restrictive mode first.66 </p><p>67 More information about the lock modes and locking strategies can be68 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 to71 lock. If <code class="literal">ONLY</code> is specified before the table name, only that72 table is locked. If <code class="literal">ONLY</code> is not specified, the table and all73 its descendant tables (if any) are locked. Optionally, <code class="literal">*</code>74 can be specified after the table name to explicitly indicate that75 descendant tables are included.76 </p><p>77 The command <code class="literal">LOCK TABLE a, b;</code> is equivalent to78 <code class="literal">LOCK TABLE a; LOCK TABLE b;</code>. The tables are locked79 one-by-one in the order specified in the <code class="command">LOCK80 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">ACCESS86 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 for89 any conflicting locks to be released: if the specified lock(s)90 cannot be acquired immediately without waiting, the transaction91 is aborted.92 </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.155.7"><h2>Notes</h2><p>93 To lock a table, the user must have the right privilege for the specified94 <em class="replaceable"><code>lockmode</code></em>, or be the table's95 owner or a superuser. If the user has96 <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, or97 <code class="literal">TRUNCATE</code> privileges on the table, any <em class="replaceable"><code>lockmode</code></em> is permitted. If the user has98 <code class="literal">INSERT</code> privileges on the table, <code class="literal">ROW EXCLUSIVE99 MODE</code> (or a less-conflicting mode as described in <a class="xref" href="explicit-locking.html" title="13.3. Explicit Locking">Section 13.3</a>) is permitted. If a user has100 <code class="literal">SELECT</code> privileges on the table, <code class="literal">ACCESS SHARE101 MODE</code> is permitted.102 </p><p>103 The user performing the lock on the view must have the corresponding104 privilege on the view. In addition, by default, the view's owner must105 have the relevant privileges on the underlying base relations, whereas the106 user performing the lock does not need any permissions on the underlying107 base relations. However, if the view has108 <code class="literal">security_invoker</code> set to <code class="literal">true</code>109 (see <a class="link" href="sql-createview.html" title="CREATE VIEW"><code class="command">CREATE VIEW</code></a>),110 the user performing the lock, rather than the view owner, must have the111 relevant privileges on the underlying base relations.112 </p><p>113 <code class="command">LOCK TABLE</code> is useless outside a transaction block: the lock114 would remain held only to the completion of the statement. Therefore115 <span class="productname">PostgreSQL</span> reports an error if <code class="command">LOCK</code>116 is used outside a transaction block.117 Use118 <a class="link" href="sql-begin.html" title="BEGIN"><code class="command">BEGIN</code></a> and119 <a class="link" href="sql-commit.html" title="COMMIT"><code class="command">COMMIT</code></a>120 (or <a class="link" href="sql-rollback.html" title="ROLLBACK"><code class="command">ROLLBACK</code></a>)121 to define a transaction block.122 </p><p>123 <code class="command">LOCK TABLE</code> only deals with table-level locks, and so124 the mode names involving <code class="literal">ROW</code> are all misnomers. These125 mode names should generally be read as indicating the intention of126 the user to acquire row-level locks within the locked table. Also,127 <code class="literal">ROW EXCLUSIVE</code> mode is a shareable table lock. Keep in128 mind that all the lock modes have identical semantics so far as129 <code class="command">LOCK TABLE</code> is concerned, differing only in the rules130 about which modes conflict with which. For information on how to131 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>132 and <a class="xref" href="sql-select.html#SQL-FOR-UPDATE-SHARE" title="The Locking Clause">The Locking Clause</a>133 in the <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a> documentation.134 </p></div><div class="refsect1" id="id-1.9.3.155.8"><h2>Examples</h2><p>135 Obtain a <code class="literal">SHARE</code> lock on a primary key table when going to perform136 inserts into a foreign key table:137 138</p><pre class="programlisting">139BEGIN WORK;140LOCK TABLE films IN SHARE MODE;141SELECT id FROM films142 WHERE name = 'Star Wars: Episode I - The Phantom Menace';143-- Do ROLLBACK if record was not returned144INSERT INTO films_user_comments VALUES145 (_id_, 'GREAT! I was waiting for it for so long!');146COMMIT WORK;147</pre><p>148 </p><p>149 Take a <code class="literal">SHARE ROW EXCLUSIVE</code> lock on a primary key table when going to perform150 a delete operation:151 152</p><pre class="programlisting">153BEGIN WORK;154LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;155DELETE FROM films_user_comments WHERE id IN156 (SELECT id FROM films WHERE rating < 5);157DELETE FROM films WHERE rating < 5;158COMMIT WORK;159</pre></div><div class="refsect1" id="id-1.9.3.155.9"><h2>Compatibility</h2><p>160 There is no <code class="command">LOCK TABLE</code> in the SQL standard,161 which instead uses <code class="command">SET TRANSACTION</code> to specify162 concurrency levels on transactions. <span class="productname">PostgreSQL</span> supports that too;163 see <a class="xref" href="sql-set-transaction.html" title="SET TRANSACTION"><span class="refentrytitle">SET TRANSACTION</span></a> for details.164 </p><p>165 Except for <code class="literal">ACCESS SHARE</code>, <code class="literal">ACCESS EXCLUSIVE</code>,166 and <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock modes, the167 <span class="productname">PostgreSQL</span> lock modes and the168 <code class="command">LOCK TABLE</code> syntax are compatible with those169 present in <span class="productname">Oracle</span>.170 </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" title="LOAD">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-merge.html" title="MERGE">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" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> MERGE</td></tr></table></div></body></html>