Movatterモバイル変換

13.4.1. Enforcing Consistency with Serializable Transactions

13.4.2. Enforcing Consistency with Explicit Blocking Locks

If the Serializable transaction isolation level is used for all writes and for all reads which need a consistent view of the data, no other effort is required to ensure consistency. Software from other environments which is written to use serializable transactions to ensure consistency should“just work” in this regard inPostgreSQL.

When using this technique, it will avoid creating an unnecessary burden for application programmers if the application software goes through a framework which automatically retries transactions which are rolled back with a serialization failure. It may be a good idea to setdefault_transaction_isolation toserializable. It would also be wise to take some action to ensure that no other transaction isolation level is used, either inadvertently or to subvert integrity checks, through checks of the transaction isolation level in triggers.

SeeSection 13.2.3 for performance suggestions.

When non-serializable writes are possible, to ensure the current validity of a row and protect it against concurrent updates one must useSELECT FOR UPDATE,SELECT FOR SHARE, or an appropriateLOCK TABLE statement. (SELECT FOR UPDATE andSELECT FOR SHARE lock just the returned rows against concurrent updates, whileLOCK TABLE locks the whole table.) This should be taken into account when porting applications toPostgreSQL from other environments.

Also of note to those converting from other environments is the fact thatSELECT FOR UPDATE does not ensure that a concurrent transaction will not update or delete a selected row. To do that inPostgreSQL you must actually update the row, even if no values need to be changed.SELECT FOR UPDATEtemporarily blocks other transactions from acquiring the same lock or executing anUPDATE orDELETE which would affect the locked row, but once the transaction holding this lock commits or rolls back, a blocked transaction will proceed with the conflicting operation unless an actualUPDATE of the row was performed while the lock was held.

Global validity checks require extra thought under non-serializableMVCC. For example, a banking application might wish to check that the sum of all credits in one table equals the sum of debits in another table, when both tables are being actively updated. Comparing the results of two successiveSELECT sum(...) commands will not work reliably in Read Committed mode, since the second query will likely include the results of transactions not counted by the first. Doing the two sums in a single repeatable read transaction will give an accurate picture of only the effects of transactions that committed before the repeatable read transaction started — but one might legitimately wonder whether the answer is still relevant by the time it is delivered. If the repeatable read transaction itself applied some changes before trying to make the consistency check, the usefulness of the check becomes even more debatable, since now it includes some but not all post-transaction-start changes. In such cases a careful person might wish to lock all tables needed for the check, in order to get an indisputable picture of current reality. ASHARE mode (or higher) lock guarantees that there are no uncommitted changes in the locked table, other than those of the current transaction.

Note also that if one is relying on explicit locking to prevent concurrent changes, one should either use Read Committed mode, or in Repeatable Read mode be careful to obtain locks before performing queries. A lock obtained by a repeatable read transaction guarantees that no other transactions modifying the table are still running, but if the snapshot seen by the transaction predates obtaining the lock, it might predate some now-committed changes in the table. A repeatable read transaction's snapshot is actually frozen at the start of its first query or data-modification command (SELECT,INSERT,UPDATE,DELETE, orMERGE), so it is possible to obtain locks explicitly before the snapshot is frozen.