78
Chapter 13. Concurrency Control
updater rolls back, then its effects are negated and the second updater can proceed with updating the
originally found row. If the first updater commits, the second updater will ignore the row if the first
updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.
The searchcondition of the command (the
WHERE
clause) is re-evaluated to see if the updated version
of the row still matches the search condition. If so, the second updater proceeds with its operation
usingtheupdated versionof the row. In the case of
SELECT FOR UPDATE
and
SELECT FOR SHARE
,
this means it is the updated version of the row that is locked and returned to the client.
Because of the above rule, it is possible for an updating command to see an inconsistent snapshot:
it can see the effects of concurrent updating commands on the same rows it is trying to update, but
it does not see effects of those commands on other rows in the database. This behavior makes Read
Committedmode unsuitable for commands that involve complexsearchconditions;however, it is just
right for simpler cases. For example, consider updating bank balances with transactions like:
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;
If twosuch transactions concurrently try to change the balance of account12345, we clearly want the
second transaction to start with the updated version of the account’s row. Because each command is
affecting only a predetermined row, letting it see the updated version of the row does not create any
troublesome inconsistency.
More complex usage can produce undesirable results in Read Committed mode. For example, con-
sider a
DELETE
command operating ondata that is being both added and removed from its restriction
criteria by another command, e.g., assume
website
is a two-rowtable with
website.hits
equaling
9
and
10
:
BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:
DELETE FROM website WHERE hits = 10;
COMMIT;
The
DELETE
willhave no effecteven thoughthere is a
website.hits = 10
rowbefore and after the
UPDATE
.This occurs because thepre-update row value
9
is skipped, and when the
UPDATE
completes
and
DELETE
obtains a lock, the new row value is no longer
10
but
11
,which no longer matches the
criteria.
Because Read Committed mode starts each command with a new snapshot that includes all transac-
tions committed up to that instant, subsequent commands in the same transaction will see the effects
of the committed concurrent transaction in any case. The point at issue above is whether or not a
single command sees anabsolutely consistent view of the database.
The partial transaction isolation provided by Read Committed mode is adequate for many applica-
tions, and this mode is fast and simple to use; however, it is not sufficient for all cases. Applications
that do complex queries and updates might require a more rigorously consistent view of the database
than Read Committed mode provides.
13.2.2. Repeatable Read Isolation Level
The Repeatable Read isolation level only sees data committed before the transaction began; it never
sees either uncommitted data or changes committed duringtransaction execution by concurrent trans-
actions. (However, the query does see the effects of previous updates executed within its own trans-
action, even though they are not yet committed.) This is a stronger guarantee than is required by the
373
55
Chapter 13. Concurrency Control
SQL standard for this isolation level, and prevents all of the phenomena described in Table 13-1.
As mentioned above, this is specifically allowed by the standard, which only describes the minimum
protections each isolationlevel must provide.
This level is different from Read Committed in that a query in a repeatable read transaction sees a
snapshot asof thestart of the transaction, not asof thestart of the current query within thetransaction.
Thus, successive
SELECT
commands within a single transaction see the same data, i.e., they do not
see changes made byother transactions that committed after their owntransaction started.
Applications using this level must be prepared to retry transactions due to serialization failures.
UPDATE
,
DELETE
,
SELECT FOR UPDATE
,and
SELECT FOR SHARE
commands behave the same as
SELECT
in terms of searching for target rows: they will only find target rows that were committed as
of the transaction start time. However, such a target row might have already been updated (or deleted
or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read
transaction will wait for the first updating transaction to commit or roll back (if it is still in progress).
If the first updater rolls back, then its effects are negated and the repeatable read transaction can
proceedwith updatingthe originallyfoundrow. But if the first updater commits (and actually updated
or deletedthe row, not just locked it) then the repeatable read transaction will be rolled back with the
message
ERROR:
could not serialize access due to concurrent update
because a repeatable read transaction cannot modify or lockrows changed by other transactions after
the repeatable read transaction began.
When an application receives this error message, it should abort the current transaction and retry
the whole transaction from the beginning. The second time through, the transaction will see the
previously-committed change as part of its initial view of the database, so there is no logical con-
flict in using the new version of the row as the starting point for the new transaction’s update.
Note that only updating transactions might need to be retried; read-only transactions will never have
serialization conflicts.
The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely
stable view of the database. However, this view will not necessarily always be consistent with some
serial (one at a time) execution of concurrent transactions of the same level. For example, even a
read only transaction at this level may see a control record updated to show that a batch has been
completed but not see one of the detail records which is logically part of the batch because it read
an earlier revision of the control record. Attempts to enforce business rules by transactions running
at this isolation level are not likely to work correctly without careful use of explicit locks to block
conflicting transactions.
Note: Prior to PostgreSQL version 9.1, a request for the Serializable transaction isolation level
provided exactly the same behavior described here. To retain the legacy Serializable behavior,
Repeatable Read should now be requested.
13.2.3. Serializable Isolation Level
The Serializable isolation level provides the strictest transaction isolation. This level emulates serial
transaction execution for all committed transactions; as if transactions had been executed one after
another, serially, rather than concurrently. However, like the Repeatable Read level, applications using
this level mustbe prepared toretrytransactions dueto serializationfailures. Infact, this isolation level
374
69
Chapter 13. Concurrency Control
works exactly the same as Repeatable Read except that it monitors for conditions which could make
execution of a concurrent set of serializable transactions behave in a manner inconsistent with all
possible serial (one at a time) executions of those transactions. This monitoring does not introduce
any blocking beyond that present in repeatable read, but there is some overhead to the monitoring,
and detection of the conditions which could cause a serialization anomaly will trigger a serialization
failure.
As an example, consider a table
mytab
,initially containing:
class | value
-------+-------
1 |
10
1 |
20
2 |
100
2 |
200
Suppose that serializable transaction A computes:
SELECT SUM(value) FROM mytab WHERE class = 1;
andtheninserts the result (30) as the
value
in anewrowwith
class = 2
.Concurrently, serializable
transaction B computes:
SELECT SUM(value) FROM mytab WHERE class = 2;
and obtains the result 300, which it inserts in a new row with
class = 1
.Then both transactions
try to commit. If either transaction were running at the Repeatable Read isolation level, both would
be allowed to commit; but since there is no serial order of execution consistent with the result, using
Serializable transactions will allow one transaction to commit and will roll the other back with this
message:
ERROR:
could not serialize access due to read/write dependencies among transactions
This is because if A had executed before B, B would have computed the sum 330, not 300, and
similarly the other order would have resulted in a different sum computed by A.
When relyingon Serializable transactions to prevent anomalies, it is importantthat any data readfrom
apermanent user table not be considered valid until the transaction which read it has successfully
committed. This is true even for read-only transactions, except that data read within a deferrable
read-only transaction is known to be valid as soon as it is read, because such a transaction waits until
it can acquire a snapshot guaranteed to be free from such problems before starting to read any data.
In all other cases applications must not depend on results read during a transaction that later aborted;
instead, they should retry the transaction until it succeeds.
To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks
which allow it to determine when a write would have had an impact on the result of a previous
read from a concurrent transaction, had it run first. In PostgreSQL these locks do not cause any
blocking andtherefore can not play any part in causing a deadlock. They are used to identify and flag
dependencies among concurrent serializable transactions which in certain combinations can lead to
serialization anomalies. In contrast, a Read Committed or Repeatable Read transaction which wants
to ensure data consistency may need to take out a lock on an entire table, which could block other
usersattemptingtousethattable, or it mayuse
SELECT FOR UPDATE
or
SELECT FOR SHARE
which
not only can block other transactions but cause disk access.
Predicate locks in PostgreSQL, like in most other database systems, are based on data actually
accessed by a transaction. These will show up in the
pg_locks
system view with a
mode
of
SIReadLock
. The particular locks acquired during execution of a query will depend on the plan
375
67
Chapter 13. Concurrency Control
used by the query, and multiple finer-grained locks (e.g., tuple locks) may be combined into fewer
coarser-grained locks (e.g., page locks) during the course of the transaction to prevent exhaustion
of the memory used to track the locks. A
READ ONLY
transaction may be able to release its
SIRead locks before completion, if it detects that no conflicts can still occur which could lead to a
serialization anomaly. In fact,
READ ONLY
transactions will often be able to establish that fact at
startup and avoid taking any predicate locks. If you explicitly request a
SERIALIZABLE READ
ONLY DEFERRABLE
transaction, it will block until it can establish this fact. (This is the only case
where Serializable transactions block but Repeatable Read transactions don’t.) On the other hand,
SIRead locks often need to be keptpast transaction commit, until overlapping read write transactions
complete.
Consistent use of Serializable transactions can simplify development. The guarantee that any set of
concurrent serializable transactions will have the same effect as if they were run one at a time means
that if you can demonstrate that a single transaction, as written, will do the right thing when run
by itself, you can have confidence that it will do the right thing in any mix of serializable transac-
tions, even without any information about what those other transactions might do. It is important that
an environment which uses this technique have a generalized way of handling serialization failures
(which always return with a SQLSTATE value of ’40001’), because it will be very hard to predict
exactly which transactions mightcontribute tothe read/writedependencies and needto be rolled back
to prevent serialization anomalies. The monitoring of read/write dependencies has a cost, as does the
restart of transactions which are terminated with a serialization failure, but balanced against the cost
and blocking involved in use of explicit locks and
SELECT FOR UPDATE
or
SELECT FOR SHARE
,
Serializable transactions are the best performance choice for some environments.
For optimal performance when relying on Serializable transactions for concurrency control, these
issues should be considered:
•
Declare transactions as
READ ONLY
when possible.
•
Control the number of active connections, using a connection pool if needed. This is always an
important performance consideration, but it can be particularly important in a busy system using
Serializable transactions.
•
Don’t put more into a single transaction than needed for integrity purposes.
•
Don’t leave connections dangling “idle in transaction” longer than necessary.
•
Eliminate explicitlocks,
SELECT FOR UPDATE
,and
SELECT FOR SHARE
wherenolonger needed
due to the protections automatically provided by Serializable transactions.
•
When the system is forced to combine multiple page-level predicate locks into a single relation-
level predicate lock because the predicate lock table is short of memory, an increase in the rate of
serializationfailures mayoccur. Youcanavoidthis byincreasingmax_pred_locks_per_transaction.
•
Asequential scan will always necessitate a relation-level predicate lock. This can result in an in-
creased rate of serialization failures. It may be helpful to encourage the use of index scans by
reducing random_page_cost and/or increasing cpu_tuple_cost. Be sure to weigh any decrease in
transaction rollbacks and restarts against any overall change in query execution time.
13.3. Explicit Locking
PostgreSQL provides various lock modes to control concurrent access to data in tables. These modes
can be used for application-controlled locking in situations where MVCC does not give the desired
376
108
Chapter 13. Concurrency Control
behavior. Also, most PostgreSQL commands automatically acquire locks of appropriate modes to
ensure that referenced tables are not dropped or modified in incompatible ways while the command
executes. (For example,
TRUNCATE
cannot safely be executed concurrently with other operations on
the same table, so it obtains an exclusive lock on the table to enforce that.)
To examine a list of the currently outstanding locks in a database server, use the
pg_locks
system
view. For more information on monitoringthe status of the lock manager subsystem, refer to Chapter
27.
13.3.1. Table-level Locks
The list below shows the available lock modes and the contexts in which they are used automatically
by PostgreSQL. You can also acquire any of these locks explicitly with the command LOCK. Re-
member that all of these lock modes are table-level locks, even if the name contains the word “row”;
the names of the lockmodes are historical. To some extent the names reflect the typical usage of each
lock mode — but the semantics are all the same. The only real difference between one lock mode
and another is the set of lock modes with which each conflicts (see Table 13-2). Two transactions
cannot hold locks of conflicting modes on the same table at the same time. (However, a transaction
never conflicts with itself. For example, it might acquire
ACCESS EXCLUSIVE
lock and later acquire
ACCESS SHARE
lock on the same table.) Non-conflicting lock modes can be held concurrently by
many transactions. Notice in particular that some lock modes are self-conflicting (for example, an
ACCESS EXCLUSIVE
lock cannotbe held by more thanone transaction at a time) whileothers are not
self-conflicting (for example, an
ACCESS SHARE
lock can be held by multiple transactions).
Table-level Lock Modes
ACCESS SHARE
Conflicts with the
ACCESS EXCLUSIVE
lock mode only.
The
SELECT
command acquires a lock of this mode on referenced tables. In general, any query
that only reads a table and does not modify it will acquire this lock mode.
ROW SHARE
Conflicts with the
EXCLUSIVE
and
ACCESS EXCLUSIVE
lock modes.
The
SELECT FOR UPDATE
and
SELECT FOR SHARE
commands acquire a lock of this mode on
the target table(s) (in addition to
ACCESS SHARE
locks on any other tables that are referenced
but not selected
FOR UPDATE/FOR SHARE
).
ROW EXCLUSIVE
Conflicts with the
SHARE
,
SHARE ROW EXCLUSIVE
,
EXCLUSIVE
, and
ACCESS EXCLUSIVE
lock modes.
The commands
UPDATE
,
DELETE
,and
INSERT
acquire this lock mode on the target table (in
additionto
ACCESS SHARE
locks on any other referenced tables). In general, this lock modewill
be acquired byany command that modifies data in a table.
SHARE UPDATE EXCLUSIVE
Conflicts with the
SHARE UPDATE EXCLUSIVE
,
SHARE
,
SHARE ROW EXCLUSIVE
,
EXCLUSIVE
, and
ACCESS EXCLUSIVE
lock modes. This mode protects a table against
concurrent schema changes and
VACUUM
runs.
Acquired by
VACUUM
(without
FULL
),
ANALYZE
,
CREATE INDEX CONCURRENTLY
,and
ALTER
TABLE VALIDATE
and other
ALTER TABLE
variants (for full details see ALTER TABLE).
377
Documents you may be interested
Documents you may be interested
