serializable. 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.
SELECT FOR UPDATE,
SELECT FOR SHARE, or an appropriate
LOCK TABLEstatement. (
SELECT FOR UPDATEand
SELECT FOR SHARElock just the returned rows against concurrent updates, while
LOCK TABLElocks the whole table.) This should be taken into account when porting applications to PostgreSQL from other environments.
SELECT FOR UPDATEdoes not ensure that a concurrent transaction will not update or delete a selected row. To do that in PostgreSQL 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 an
DELETEwhich 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 actual
UPDATEof the row was performed while the lock was held.
SELECT 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. A
SHAREmode (or higher) lock guarantees that there are no uncommitted changes in the locked table, other than those of the current transaction.
DELETE), so it is possible to obtain locks explicitly before the snapshot is frozen.