1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
<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>
|
