4
<title>Concurrency Control</title>
7
<primary>concurrency</primary>
11
This chapter describes the behavior of the
12
<productname>PostgreSQL</productname> database system when two or
13
more sessions try to access the same data at the same time. The
14
goals in that situation are to allow efficient access for all
15
sessions while maintaining strict data integrity. Every developer
16
of database applications should be familiar with the topics covered
20
<sect1 id="mvcc-intro">
21
<title>Introduction</title>
24
<primary>MVCC</primary>
28
<productname>PostgreSQL</productname> provides a rich set of tools
29
for developers to manage concurrent access to data. Internally,
30
data consistency is maintained by using a multiversion
31
model (Multiversion Concurrency Control, <acronym>MVCC</acronym>).
32
This means that while querying a database each transaction sees
33
a snapshot of data (a <firstterm>database version</firstterm>)
35
time ago, regardless of the current state of the underlying data.
36
This protects the transaction from viewing inconsistent data that
37
could be caused by (other) concurrent transaction updates on the same
38
data rows, providing <firstterm>transaction isolation</firstterm>
39
for each database session. <acronym>MVCC</acronym>, by eschewing
40
explicit locking methodologies of traditional database systems,
41
minimizes lock contention in order to allow for reasonable
42
performance in multiuser environments.
46
The main advantage to using the <acronym>MVCC</acronym> model of
47
concurrency control rather than locking is that in
48
<acronym>MVCC</acronym> locks acquired for querying (reading) data
49
do not conflict with locks acquired for writing data, and so
50
reading never blocks writing and writing never blocks reading.
54
Table- and row-level locking facilities are also available in
55
<productname>PostgreSQL</productname> for applications that cannot
56
adapt easily to <acronym>MVCC</acronym> behavior. However, proper
57
use of <acronym>MVCC</acronym> will generally provide better
58
performance than locks. In addition, application-defined advisory
59
locks provide a mechanism for acquiring locks that are not tied
60
to a single transaction.
64
<sect1 id="transaction-iso">
65
<title>Transaction Isolation</title>
68
<primary>transaction isolation</primary>
72
The <acronym>SQL</acronym> standard defines four levels of
73
transaction isolation in terms of three phenomena that must be
74
prevented between concurrent transactions. These undesirable
81
<indexterm><primary>dirty read</primary></indexterm>
85
A transaction reads data written by a concurrent uncommitted transaction.
93
<indexterm><primary>nonrepeatable read</primary></indexterm>
97
A transaction re-reads data it has previously read and finds that data
98
has been modified by another transaction (that committed since the
107
<indexterm><primary>phantom read</primary></indexterm>
111
A transaction re-executes a query returning a set of rows that satisfy a
112
search condition and finds that the set of rows satisfying the condition
113
has changed due to another recently-committed transaction.
122
<primary>transaction isolation level</primary>
124
The four transaction isolation levels and the corresponding
125
behaviors are described in <xref linkend="mvcc-isolevel-table">.
128
<table tocentry="1" id="mvcc-isolevel-table">
129
<title><acronym>SQL</acronym> Transaction Isolation Levels</title>
212
In <productname>PostgreSQL</productname>, you can request any of the
213
four standard transaction isolation levels. But internally, there are
214
only two distinct isolation levels, which correspond to the levels Read
215
Committed and Serializable. When you select the level Read
216
Uncommitted you really get Read Committed, and when you select
217
Repeatable Read you really get Serializable, so the actual
218
isolation level might be stricter than what you select. This is
219
permitted by the SQL standard: the four isolation levels only
220
define which phenomena must not happen, they do not define which
221
phenomena must happen. The reason that <productname>PostgreSQL</>
222
only provides two isolation levels is that this is the only
223
sensible way to map the standard isolation levels to the multiversion
224
concurrency control architecture. The behavior of the available
225
isolation levels is detailed in the following subsections.
229
To set the transaction isolation level of a transaction, use the
230
command <xref linkend="sql-set-transaction" endterm="sql-set-transaction-title">.
233
<sect2 id="xact-read-committed">
234
<title>Read Committed Isolation Level</title>
237
<primary>transaction isolation level</primary>
238
<secondary>read committed</secondary>
242
<firstterm>Read Committed</firstterm> is the default isolation
243
level in <productname>PostgreSQL</productname>. When a transaction
244
uses this isolation level, a <command>SELECT</command> query
245
(without a <literal>FOR UPDATE/SHARE</> clause) sees only data
246
committed before the query began; it never sees either uncommitted
247
data or changes committed during query execution by concurrent
248
transactions. In effect, a <command>SELECT</command> query sees
249
a snapshot of the database as of the instant the query begins to
250
run. However, <command>SELECT</command> does see the effects
251
of previous updates executed within its own transaction, even
252
though they are not yet committed. Also note that two successive
253
<command>SELECT</command> commands can see different data, even
254
though they are within a single transaction, if other transactions
255
commit changes during execution of the first <command>SELECT</command>.
259
<command>UPDATE</command>, <command>DELETE</command>, <command>SELECT
260
FOR UPDATE</command>, and <command>SELECT FOR SHARE</command> commands
261
behave the same as <command>SELECT</command>
262
in terms of searching for target rows: they will only find target rows
263
that were committed as of the command start time. However, such a target
264
row might have already been updated (or deleted or locked) by
265
another concurrent transaction by the time it is found. In this case, the
266
would-be updater will wait for the first updating transaction to commit or
267
roll back (if it is still in progress). If the first updater rolls back,
268
then its effects are negated and the second updater can proceed with
269
updating the originally found row. If the first updater commits, the
270
second updater will ignore the row if the first updater deleted it,
271
otherwise it will attempt to apply its operation to the updated version of
272
the row. The search condition of the command (the <literal>WHERE</> clause) is
273
re-evaluated to see if the updated version of the row still matches the
274
search condition. If so, the second updater proceeds with its operation
275
using the updated version of the row. In the case of
276
<command>SELECT FOR UPDATE</command> and <command>SELECT FOR
277
SHARE</command>, this means it is the updated version of the row that is
278
locked and returned to the client.
282
Because of the above rule, it is possible for an updating command to see an
283
inconsistent snapshot: it can see the effects of concurrent updating
284
commands on the same rows it is trying to update, but it
285
does not see effects of those commands on other rows in the database.
286
This behavior makes Read Committed mode unsuitable for commands that
287
involve complex search conditions; however, it is just right for simpler
288
cases. For example, consider updating bank balances with transactions
293
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
294
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
298
If two such transactions concurrently try to change the balance of account
299
12345, we clearly want the second transaction to start from the updated
300
version of the account's row. Because each command is affecting only a
301
predetermined row, letting it see the updated version of the row does
302
not create any troublesome inconsistency.
306
More complex usage can produce undesirable results in Read Committed
307
mode. For example, consider a <command>DELETE</command> command
308
operating on data that is being both added and removed from its
309
restriction criteria by another command, e.g. assume
310
<literal>website</literal> is a two-row table with
311
<literal>website.hits</literal> equaling <literal>9</literal> and
312
<literal>10</literal>:
316
UPDATE website SET hits = hits + 1;
317
-- run from another session: DELETE FROM website WHERE hits = 10;
321
The <command>DELETE</command> will have no effect even though
322
there is a <literal>website.hits = 10</literal> row before and
323
after the <command>UPDATE</command>. This occurs because the
324
pre-update row value <literal>9</> is skipped, and when the
325
<command>UPDATE</command> completes and <command>DELETE</command>
326
obtains a lock, the new row value is no longer <literal>10</> but
327
<literal>11</>, which no longer matches the criteria.
331
Because Read Committed mode starts each command with a new snapshot
332
that includes all transactions committed up to that instant,
333
subsequent commands in the same transaction will see the effects
334
of the committed concurrent transaction in any case. The point
335
at issue above is whether or not a <emphasis>single</> command
336
sees an absolutely consistent view of the database.
340
The partial transaction isolation provided by Read Committed mode
341
is adequate for many applications, and this mode is fast and simple
342
to use; however, it is not sufficient for all cases. Applications
343
that do complex queries and updates might require a more rigorously
344
consistent view of the database than Read Committed mode provides.
348
<sect2 id="xact-serializable">
349
<title>Serializable Isolation Level</title>
352
<primary>transaction isolation level</primary>
353
<secondary>serializable</secondary>
357
The level <firstterm>Serializable</firstterm> provides the strictest transaction
358
isolation. This level emulates serial transaction execution,
359
as if transactions had been executed one after another, serially,
360
rather than concurrently. However, applications using this level must
361
be prepared to retry transactions due to serialization failures.
365
When a transaction is on the serializable level,
366
a <command>SELECT</command> query sees only data committed before the
367
transaction began; it never sees either uncommitted data or changes
369
during transaction execution by concurrent transactions. (However, the
370
<command>SELECT</command> does see the effects of previous updates
371
executed within its own transaction, even though they are not yet
372
committed.) This is different from Read Committed in that the
373
<command>SELECT</command>
374
sees a snapshot as of the start of the transaction, not as of the start
375
of the current query within the transaction. Thus, successive
376
<command>SELECT</command> commands within a single transaction always see the same
381
<command>UPDATE</command>, <command>DELETE</command>, <command>SELECT
382
FOR UPDATE</command>, and <command>SELECT FOR SHARE</command> commands
383
behave the same as <command>SELECT</command>
384
in terms of searching for target rows: they will only find target rows
385
that were committed as of the transaction start time. However, such a
387
row might have already been updated (or deleted or locked) by
388
another concurrent transaction by the time it is found. In this case, the
389
serializable transaction will wait for the first updating transaction to commit or
390
roll back (if it is still in progress). If the first updater rolls back,
391
then its effects are negated and the serializable transaction can proceed
392
with updating the originally found row. But if the first updater commits
393
(and actually updated or deleted the row, not just locked it)
394
then the serializable transaction will be rolled back with the message
397
ERROR: could not serialize access due to concurrent update
400
because a serializable transaction cannot modify or lock rows changed by
401
other transactions after the serializable transaction began.
405
When the application receives this error message, it should abort
406
the current transaction and then retry the whole transaction from
407
the beginning. The second time through, the transaction sees the
408
previously-committed change as part of its initial view of the database,
409
so there is no logical conflict in using the new version of the row
410
as the starting point for the new transaction's update.
414
Note that only updating transactions might need to be retried; read-only
415
transactions will never have serialization conflicts.
419
The Serializable mode provides a rigorous guarantee that each
420
transaction sees a wholly consistent view of the database. However,
421
the application has to be prepared to retry transactions when concurrent
422
updates make it impossible to sustain the illusion of serial execution.
423
Since the cost of redoing complex transactions might be significant,
424
this mode is recommended only when updating transactions contain logic
425
sufficiently complex that they might give wrong answers in Read
426
Committed mode. Most commonly, Serializable mode is necessary when
427
a transaction executes several successive commands that must see
428
identical views of the database.
431
<sect3 id="mvcc-serializability">
432
<title>Serializable Isolation versus True Serializability</title>
435
<primary>serializability</primary>
439
<primary>predicate locking</primary>
443
The intuitive meaning (and mathematical definition) of
444
<quote>serializable</> execution is that any two successfully committed
445
concurrent transactions will appear to have executed strictly serially,
446
one after the other — although which one appeared to occur first might
447
not be predictable in advance. It is important to realize that forbidding
448
the undesirable behaviors listed in <xref linkend="mvcc-isolevel-table">
449
is not sufficient to guarantee true serializability, and in fact
450
<productname>PostgreSQL</productname>'s Serializable mode <emphasis>does
451
not guarantee serializable execution in this sense</>. As an example,
452
consider a table <structname>mytab</>, initially containing
461
Suppose that serializable transaction A computes
463
SELECT SUM(value) FROM mytab WHERE class = 1;
465
and then inserts the result (30) as the <structfield>value</> in a
466
new row with <structfield>class</> = 2. Concurrently, serializable
467
transaction B computes
469
SELECT SUM(value) FROM mytab WHERE class = 2;
471
and obtains the result 300, which it inserts in a new row with
472
<structfield>class</> = 1. Then both transactions commit. None of
473
the listed undesirable behaviors have occurred, yet we have a result
474
that could not have occurred in either order serially. If A had
475
executed before B, B would have computed the sum 330, not 300, and
476
similarly the other order would have resulted in a different sum
481
To guarantee true mathematical serializability, it is necessary for
482
a database system to enforce <firstterm>predicate locking</>, which
483
means that a transaction cannot insert or modify a row that would
484
have matched the <literal>WHERE</> condition of a query in another concurrent
485
transaction. For example, once transaction A has executed the query
486
<literal>SELECT ... WHERE class = 1</>, a predicate-locking system
487
would forbid transaction B from inserting any new row with class 1
488
until A has committed.
491
Essentially, a predicate-locking system prevents phantom reads
492
by restricting what is written, whereas MVCC prevents them by
493
restricting what is read.
496
Such a locking system is complex to
497
implement and extremely expensive in execution, since every session must
498
be aware of the details of every query executed by every concurrent
499
transaction. And this large expense is mostly wasted, since in
500
practice most applications do not do the sorts of things that could
501
result in problems. (Certainly the example above is rather contrived
502
and unlikely to represent real software.) For these reasons,
503
<productname>PostgreSQL</productname> does not implement predicate
508
In those cases where the possibility of nonserializable execution
509
is a real hazard, problems can be prevented by appropriate use of
510
explicit locking. Further discussion appears in the following
517
<sect1 id="explicit-locking">
518
<title>Explicit Locking</title>
521
<primary>lock</primary>
525
<productname>PostgreSQL</productname> provides various lock modes
526
to control concurrent access to data in tables. These modes can
527
be used for application-controlled locking in situations where
528
<acronym>MVCC</acronym> does not give the desired behavior. Also,
529
most <productname>PostgreSQL</productname> commands automatically
530
acquire locks of appropriate modes to ensure that referenced
531
tables are not dropped or modified in incompatible ways while the
532
command executes. (For example, <command>ALTER TABLE</> cannot safely be
533
executed concurrently with other operations on the same table, so it
534
obtains an exclusive lock on the table to enforce that.)
538
To examine a list of the currently outstanding locks in a database
540
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
541
system view. For more information on monitoring the status of the lock
542
manager subsystem, refer to <xref linkend="monitoring">.
545
<sect2 id="locking-tables">
546
<title>Table-Level Locks</title>
548
<indexterm zone="locking-tables">
549
<primary>LOCK</primary>
553
The list below shows the available lock modes and the contexts in
554
which they are used automatically by
555
<productname>PostgreSQL</productname>. You can also acquire any
556
of these locks explicitly with the command <xref
557
linkend="sql-lock" endterm="sql-lock-title">.
558
Remember that all of these lock modes are table-level locks,
559
even if the name contains the word
560
<quote>row</quote>; the names of the lock modes are historical.
561
To some extent the names reflect the typical usage of each lock
562
mode — but the semantics are all the same. The only real difference
563
between one lock mode and another is the set of lock modes with
564
which each conflicts (see <xref linkend="table-lock-compatibility">).
565
. Two transactions cannot hold locks of conflicting
566
modes on the same table at the same time. (However, a transaction
567
never conflicts with itself. For example, it might acquire
568
<literal>ACCESS EXCLUSIVE</literal> lock and later acquire
569
<literal>ACCESS SHARE</literal> lock on the same table.) Non-conflicting
570
lock modes can be held concurrently by many transactions. Notice in
571
particular that some lock modes are self-conflicting (for example,
572
an <literal>ACCESS EXCLUSIVE</literal> lock cannot be held by more than one
573
transaction at a time) while others are not self-conflicting (for example,
574
an <literal>ACCESS SHARE</literal> lock can be held by multiple transactions).
578
<title>Table-level lock modes</title>
581
<literal>ACCESS SHARE</literal>
585
Conflicts with the <literal>ACCESS EXCLUSIVE</literal> lock
590
The <command>SELECT</command> command acquires a lock of this mode on
591
referenced tables. In general, any query that only reads a table
592
and does not modify it will acquire this lock mode.
599
<literal>ROW SHARE</literal>
603
Conflicts with the <literal>EXCLUSIVE</literal> and
604
<literal>ACCESS EXCLUSIVE</literal> lock modes.
608
The <command>SELECT FOR UPDATE</command> and
609
<command>SELECT FOR SHARE</command> commands acquire a
610
lock of this mode on the target table(s) (in addition to
611
<literal>ACCESS SHARE</literal> locks on any other tables
612
that are referenced but not selected
613
<option>FOR UPDATE/FOR SHARE</option>).
620
<literal>ROW EXCLUSIVE</literal>
624
Conflicts with the <literal>SHARE</literal>, <literal>SHARE ROW
625
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
626
<literal>ACCESS EXCLUSIVE</literal> lock modes.
630
The commands <command>UPDATE</command>,
631
<command>DELETE</command>, and <command>INSERT</command>
632
acquire this lock mode on the target table (in addition to
633
<literal>ACCESS SHARE</literal> locks on any other referenced
634
tables). In general, this lock mode will be acquired by any
635
command that modifies the data in a table.
642
<literal>SHARE UPDATE EXCLUSIVE</literal>
646
Conflicts with the <literal>SHARE UPDATE EXCLUSIVE</literal>,
647
<literal>SHARE</literal>, <literal>SHARE ROW
648
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
649
<literal>ACCESS EXCLUSIVE</literal> lock modes.
650
This mode protects a table against
651
concurrent schema changes and <command>VACUUM</> runs.
655
Acquired by <command>VACUUM</command> (without <option>FULL</option>),
656
<command>ANALYZE</>, and <command>CREATE INDEX CONCURRENTLY</>.
663
<literal>SHARE</literal>
667
Conflicts with the <literal>ROW EXCLUSIVE</literal>,
668
<literal>SHARE UPDATE EXCLUSIVE</literal>, <literal>SHARE ROW
669
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
670
<literal>ACCESS EXCLUSIVE</literal> lock modes.
671
This mode protects a table against concurrent data changes.
675
Acquired by <command>CREATE INDEX</command>
676
(without <option>CONCURRENTLY</option>).
683
<literal>SHARE ROW EXCLUSIVE</literal>
687
Conflicts with the <literal>ROW EXCLUSIVE</literal>,
688
<literal>SHARE UPDATE EXCLUSIVE</literal>,
689
<literal>SHARE</literal>, <literal>SHARE ROW
690
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
691
<literal>ACCESS EXCLUSIVE</literal> lock modes.
695
This lock mode is not automatically acquired by any
696
<productname>PostgreSQL</productname> command.
703
<literal>EXCLUSIVE</literal>
707
Conflicts with the <literal>ROW SHARE</literal>, <literal>ROW
708
EXCLUSIVE</literal>, <literal>SHARE UPDATE
709
EXCLUSIVE</literal>, <literal>SHARE</literal>, <literal>SHARE
710
ROW EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
711
<literal>ACCESS EXCLUSIVE</literal> lock modes.
712
This mode allows only concurrent <literal>ACCESS SHARE</literal> locks,
713
i.e., only reads from the table can proceed in parallel with a
714
transaction holding this lock mode.
718
This lock mode is not automatically acquired on user tables by any
719
<productname>PostgreSQL</productname> command. However it is
720
acquired on certain system catalogs in some operations.
727
<literal>ACCESS EXCLUSIVE</literal>
731
Conflicts with locks of all modes (<literal>ACCESS
732
SHARE</literal>, <literal>ROW SHARE</literal>, <literal>ROW
733
EXCLUSIVE</literal>, <literal>SHARE UPDATE
734
EXCLUSIVE</literal>, <literal>SHARE</literal>, <literal>SHARE
735
ROW EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
736
<literal>ACCESS EXCLUSIVE</literal>).
737
This mode guarantees that the
738
holder is the only transaction accessing the table in any way.
742
Acquired by the <command>ALTER TABLE</command>, <command>DROP
743
TABLE</command>, <command>TRUNCATE</command>, <command>REINDEX</command>,
744
<command>CLUSTER</command>, and <command>VACUUM FULL</command>
745
commands. This is also the default lock mode for <command>LOCK
746
TABLE</command> statements that do not specify a mode explicitly.
754
Only an <literal>ACCESS EXCLUSIVE</literal> lock blocks a
755
<command>SELECT</command> (without <option>FOR UPDATE/SHARE</option>)
761
Once acquired, a lock is normally held till end of transaction. But if a
762
lock is acquired after establishing a savepoint, the lock is released
763
immediately if the savepoint is rolled back to. This is consistent with
764
the principle that <command>ROLLBACK</> cancels all effects of the
765
commands since the savepoint. The same holds for locks acquired within a
766
<application>PL/pgSQL</> exception block: an error escape from the block
767
releases locks acquired within it.
772
<table tocentry="1" id="table-lock-compatibility">
773
<title> Conflicting lock modes</title>
775
<colspec colnum="2" colname="lockst">
776
<colspec colnum="9" colname="lockend">
777
<spanspec namest="lockst" nameend="lockend" spanname="lockreq">
780
<entry morerows="1">Requested Lock Mode</entry>
781
<entry spanname="lockreq">Current Lock Mode</entry>
784
<entry>ACCESS SHARE</entry>
785
<entry>ROW SHARE</entry>
786
<entry>ROW EXCLUSIVE</entry>
787
<entry>SHARE UPDATE EXCLUSIVE</entry>
789
<entry>SHARE ROW EXCLUSIVE</entry>
790
<entry>EXCLUSIVE</entry>
791
<entry>ACCESS EXCLUSIVE</entry>
796
<entry>ACCESS SHARE</entry>
797
<entry align="center"></entry>
798
<entry align="center"></entry>
799
<entry align="center"></entry>
800
<entry align="center"></entry>
801
<entry align="center"></entry>
802
<entry align="center"></entry>
803
<entry align="center"></entry>
804
<entry align="center">X</entry>
807
<entry>ROW SHARE</entry>
808
<entry align="center"></entry>
809
<entry align="center"></entry>
810
<entry align="center"></entry>
811
<entry align="center"></entry>
812
<entry align="center"></entry>
813
<entry align="center"></entry>
814
<entry align="center">X</entry>
815
<entry align="center">X</entry>
818
<entry>ROW EXCLUSIVE</entry>
819
<entry align="center"></entry>
820
<entry align="center"></entry>
821
<entry align="center"></entry>
822
<entry align="center"></entry>
823
<entry align="center">X</entry>
824
<entry align="center">X</entry>
825
<entry align="center">X</entry>
826
<entry align="center">X</entry>
829
<entry>SHARE UPDATE EXCLUSIVE</entry>
830
<entry align="center"></entry>
831
<entry align="center"></entry>
832
<entry align="center"></entry>
833
<entry align="center">X</entry>
834
<entry align="center">X</entry>
835
<entry align="center">X</entry>
836
<entry align="center">X</entry>
837
<entry align="center">X</entry>
841
<entry align="center"></entry>
842
<entry align="center"></entry>
843
<entry align="center">X</entry>
844
<entry align="center">X</entry>
845
<entry align="center"></entry>
846
<entry align="center">X</entry>
847
<entry align="center">X</entry>
848
<entry align="center">X</entry>
851
<entry>SHARE ROW EXCLUSIVE</entry>
852
<entry align="center"></entry>
853
<entry align="center"></entry>
854
<entry align="center">X</entry>
855
<entry align="center">X</entry>
856
<entry align="center">X</entry>
857
<entry align="center">X</entry>
858
<entry align="center">X</entry>
859
<entry align="center">X</entry>
862
<entry>EXCLUSIVE</entry>
863
<entry align="center"></entry>
864
<entry align="center">X</entry>
865
<entry align="center">X</entry>
866
<entry align="center">X</entry>
867
<entry align="center">X</entry>
868
<entry align="center">X</entry>
869
<entry align="center">X</entry>
870
<entry align="center">X</entry>
873
<entry>ACCESS EXCLUSIVE</entry>
874
<entry align="center">X</entry>
875
<entry align="center">X</entry>
876
<entry align="center">X</entry>
877
<entry align="center">X</entry>
878
<entry align="center">X</entry>
879
<entry align="center">X</entry>
880
<entry align="center">X</entry>
881
<entry align="center">X</entry>
888
<sect2 id="locking-rows">
889
<title>Row-Level Locks</title>
892
In addition to table-level locks, there are row-level locks, which
893
can be exclusive or shared locks. An exclusive row-level lock on a
894
specific row is automatically acquired when the row is updated or
895
deleted. The lock is held until the transaction commits or rolls
896
back, in just the same way as for table-level locks. Row-level locks do
897
not affect data querying; they block <emphasis>writers to the same
902
To acquire an exclusive row-level lock on a row without actually
903
modifying the row, select the row with <command>SELECT FOR
904
UPDATE</command>. Note that once the row-level lock is acquired,
905
the transaction can update the row multiple times without
910
To acquire a shared row-level lock on a row, select the row with
911
<command>SELECT FOR SHARE</command>. A shared lock does not prevent
912
other transactions from acquiring the same shared lock. However,
913
no transaction is allowed to update, delete, or exclusively lock a
914
row on which any other transaction holds a shared lock. Any attempt
915
to do so will block until the shared lock(s) have been released.
919
<productname>PostgreSQL</productname> doesn't remember any
920
information about modified rows in memory, so it has no limit to
921
the number of rows locked at one time. However, locking a row
922
might cause a disk write; thus, for example, <command>SELECT FOR
923
UPDATE</command> will modify selected rows to mark them locked, and so
924
will result in disk writes.
928
In addition to table and row locks, page-level share/exclusive locks are
929
used to control read/write access to table pages in the shared buffer
930
pool. These locks are released immediately after a row is fetched or
931
updated. Application developers normally need not be concerned with
932
page-level locks, but we mention them for completeness.
937
<sect2 id="locking-deadlocks">
938
<title>Deadlocks</title>
940
<indexterm zone="locking-deadlocks">
941
<primary>deadlock</primary>
945
The use of explicit locking can increase the likelihood of
946
<firstterm>deadlocks</>, wherein two (or more) transactions each
947
hold locks that the other wants. For example, if transaction 1
948
acquires an exclusive lock on table A and then tries to acquire
949
an exclusive lock on table B, while transaction 2 has already
950
exclusive-locked table B and now wants an exclusive lock on table
951
A, then neither one can proceed.
952
<productname>PostgreSQL</productname> automatically detects
953
deadlock situations and resolves them by aborting one of the
954
transactions involved, allowing the other(s) to complete.
955
(Exactly which transaction will be aborted is difficult to
956
predict and should not be relied on.)
960
Note that deadlocks can also occur as the result of row-level
961
locks (and thus, they can occur even if explicit locking is not
962
used). Consider the case in which there are two concurrent
963
transactions modifying a table. The first transaction executes:
966
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
969
This acquires a row-level lock on the row with the specified
970
account number. Then, the second transaction executes:
973
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
974
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
977
The first <command>UPDATE</command> statement successfully
978
acquires a row-level lock on the specified row, so it succeeds in
979
updating that row. However, the second <command>UPDATE</command>
980
statement finds that the row it is attempting to update has
981
already been locked, so it waits for the transaction that
982
acquired the lock to complete. Transaction two is now waiting on
983
transaction one to complete before it continues execution. Now,
984
transaction one executes:
987
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
990
Transaction one attempts to acquire a row-level lock on the
991
specified row, but it cannot: transaction two already holds such
992
a lock. So it waits for transaction two to complete. Thus,
993
transaction one is blocked on transaction two, and transaction
994
two is blocked on transaction one: a deadlock
995
condition. <productname>PostgreSQL</productname> will detect this
996
situation and abort one of the transactions.
1000
The best defense against deadlocks is generally to avoid them by
1001
being certain that all applications using a database acquire
1002
locks on multiple objects in a consistent order. In the example
1003
above, if both transactions
1004
had updated the rows in the same order, no deadlock would have
1005
occurred. One should also ensure that the first lock acquired on
1006
an object in a transaction is the highest mode that will be
1007
needed for that object. If it is not feasible to verify this in
1008
advance, then deadlocks can be handled on-the-fly by retrying
1009
transactions that are aborted due to deadlock.
1013
So long as no deadlock situation is detected, a transaction seeking
1014
either a table-level or row-level lock will wait indefinitely for
1015
conflicting locks to be released. This means it is a bad idea for
1016
applications to hold transactions open for long periods of time
1017
(e.g., while waiting for user input).
1021
<sect2 id="advisory-locks">
1022
<title>Advisory Locks</title>
1024
<indexterm zone="advisory-locks">
1025
<primary>lock</primary>
1026
<secondary>advisory</secondary>
1030
<productname>PostgreSQL</productname> provides a means for
1031
creating locks that have application-defined meanings. These are
1032
called <firstterm>advisory locks</>, because the system does not
1033
enforce their use — it is up to the application to use them
1034
correctly. Advisory locks can be useful for locking strategies
1035
that are an awkward fit for the MVCC model. Once acquired, an
1036
advisory lock is held until explicitly released or the session ends.
1037
Unlike standard locks, advisory locks do not
1038
honor transaction semantics: a lock acquired during a
1039
transaction that is later rolled back will still be held following the
1040
rollback, and likewise an unlock is effective even if the calling
1041
transaction fails later. The same lock can be acquired multiple times by
1042
its owning process: for each lock request there must be a corresponding
1043
unlock request before the lock is actually released. (If a session
1044
already holds a given lock, additional requests will always succeed, even
1045
if other sessions are awaiting the lock.) Like all locks in
1046
<productname>PostgreSQL</productname>, a complete list of advisory
1047
locks currently held by any session can be found in the
1048
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
1053
Advisory locks are allocated out of a shared memory pool whose size
1054
is defined by the configuration variables
1055
<xref linkend="guc-max-locks-per-transaction"> and
1056
<xref linkend="guc-max-connections">.
1057
Care must be taken not to exhaust this
1058
memory or the server will not be able to grant any locks at all.
1059
This imposes an upper limit on the number of advisory locks
1060
grantable by the server, typically in the tens to hundreds of thousands
1061
depending on how the server is configured.
1065
A common use of advisory locks is to emulate pessimistic locking
1066
strategies typical of so called <quote>flat file</> data management
1068
While a flag stored in a table could be used for the same purpose,
1069
advisory locks are faster, avoid MVCC bloat, and are automatically
1070
cleaned up by the server at the end of the session.
1071
In certain cases using this method, especially in queries
1072
involving explicit ordering and <literal>LIMIT</> clauses, care must be
1073
taken to control the locks acquired because of the order in which SQL
1074
expressions are evaluated. For example:
1076
SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
1077
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
1078
SELECT pg_advisory_lock(q.id) FROM
1080
SELECT id FROM foo WHERE id > 12345 LIMIT 100;
1083
In the above queries, the second form is dangerous because the
1084
<literal>LIMIT</> is not guaranteed to be applied before the locking
1085
function is executed. This might cause some locks to be acquired
1086
that the application was not expecting, and hence would fail to release
1087
(until it ends the session).
1088
From the point of view of the application, such locks
1089
would be dangling, although still viewable in
1090
<structname>pg_locks</structname>.
1094
The functions provided to manipulate advisory locks are described in
1095
<xref linkend="functions-advisory-locks">.
1101
<sect1 id="applevel-consistency">
1102
<title>Data Consistency Checks at the Application Level</title>
1105
Because readers in <productname>PostgreSQL</productname>
1106
do not lock data, regardless of
1107
transaction isolation level, data read by one transaction can be
1108
overwritten by another concurrent transaction. In other words,
1109
if a row is returned by <command>SELECT</command> it doesn't mean that
1110
the row is still current at the instant it is returned (i.e., sometime
1111
after the current query began). The row might have been modified or
1112
deleted by an already-committed transaction that committed after this one
1114
Even if the row is still valid <quote>now</quote>, it could be changed or
1116
before the current transaction does a commit or rollback.
1120
Another way to think about it is that each
1121
transaction sees a snapshot of the database contents, and concurrently
1122
executing transactions might very well see different snapshots. So the
1123
whole concept of <quote>now</quote> is somewhat ill-defined anyway.
1124
This is not normally
1125
a big problem if the client applications are isolated from each other,
1126
but if the clients can communicate via channels outside the database
1127
then serious confusion might ensue.
1131
To ensure the current validity of a row and protect it against
1132
concurrent updates one must use <command>SELECT FOR UPDATE</command>,
1133
<command>SELECT FOR SHARE</command>, or an appropriate <command>LOCK
1134
TABLE</command> statement. (<command>SELECT FOR UPDATE</command>
1135
or <command>SELECT FOR SHARE</command> locks just the
1136
returned rows against concurrent updates, while <command>LOCK
1137
TABLE</command> locks the whole table.) This should be taken into
1138
account when porting applications to
1139
<productname>PostgreSQL</productname> from other environments.
1143
Global validity checks require extra thought under <acronym>MVCC</acronym>.
1144
For example, a banking application might wish to check that the sum of
1145
all credits in one table equals the sum of debits in another table,
1146
when both tables are being actively updated. Comparing the results of two
1147
successive <literal>SELECT sum(...)</literal> commands will not work reliably under
1148
Read Committed mode, since the second query will likely include the results
1149
of transactions not counted by the first. Doing the two sums in a
1150
single serializable transaction will give an accurate picture of the
1151
effects of transactions that committed before the serializable transaction
1152
started — but one might legitimately wonder whether the answer is still
1153
relevant by the time it is delivered. If the serializable transaction
1154
itself applied some changes before trying to make the consistency check,
1155
the usefulness of the check becomes even more debatable, since now it
1156
includes some but not all post-transaction-start changes. In such cases
1157
a careful person might wish to lock all tables needed for the check,
1158
in order to get an indisputable picture of current reality. A
1159
<literal>SHARE</> mode (or higher) lock guarantees that there are no
1160
uncommitted changes in the locked table, other than those of the current
1165
Note also that if one is
1166
relying on explicit locking to prevent concurrent changes, one should use
1167
Read Committed mode, or in Serializable mode be careful to obtain the
1168
lock(s) before performing queries. A lock obtained by a
1169
serializable transaction guarantees that no other transactions modifying
1170
the table are still running, but if the snapshot seen by the
1171
transaction predates obtaining the lock, it might predate some now-committed
1172
changes in the table. A serializable transaction's snapshot is actually
1173
frozen at the start of its first query or data-modification command
1174
(<literal>SELECT</>, <literal>INSERT</>,
1175
<literal>UPDATE</>, or <literal>DELETE</>), so
1176
it's possible to obtain locks explicitly before the snapshot is
1181
<sect1 id="locking-indexes">
1182
<title>Locking and Indexes</title>
1184
<indexterm zone="locking-indexes">
1185
<primary>index</primary>
1186
<secondary>locks</secondary>
1190
Though <productname>PostgreSQL</productname>
1191
provides nonblocking read/write access to table
1192
data, nonblocking read/write access is not currently offered for every
1193
index access method implemented
1194
in <productname>PostgreSQL</productname>.
1195
The various index types are handled as follows:
1200
B-tree and <acronym>GiST</acronym> indexes
1204
Short-term share/exclusive page-level locks are used for
1205
read/write access. Locks are released immediately after each
1206
index row is fetched or inserted. These index types provide
1207
the highest concurrency without deadlock conditions.
1218
Share/exclusive hash-bucket-level locks are used for read/write
1219
access. Locks are released after the whole bucket is processed.
1220
Bucket-level locks provide better concurrency than index-level
1221
ones, but deadlock is possible since the locks are held longer
1222
than one index operation.
1229
<acronym>GIN</acronym> indexes
1233
Short-term share/exclusive page-level locks are used for
1234
read/write access. Locks are released immediately after each
1235
index row is fetched or inserted. But note that a GIN-indexed
1236
value insertion usually produces several index key insertions
1237
per row, so GIN might do substantial work for a single value's
1246
Currently, B-tree indexes offer the best performance for concurrent
1247
applications; since they also have more features than hash
1248
indexes, they are the recommended index type for concurrent
1249
applications that need to index scalar data. When dealing with
1250
non-scalar data, B-trees are not useful, and GiST or GIN indexes should
added
removed
doc/src/sgml/mvcc.sgml
