5.8. Preventing Lost Updates

A Lost Update, also known as a ww-conflict, is an anomaly that occurs when concurrent transactions update the same rows. PostgreSQL must prevent this anomaly in both REPEATABLE READ and SERIALIZABLE levels. (Note that the READ COMMITTED level does not need to prevent Lost Updates.) This section describes how PostgreSQL prevents Lost Updates and provides examples.

5.8.1. Behavior of Concurrent UPDATE Commands

When an UPDATE command is executed, the ExecUpdate function is internally invoked. The pseudocode of ExecUpdate is shown below:

(1)   FOR each row that will be updated by this UPDATE command
(2)        WHILE true

                /*
                 * The First Block
                 */
(3)             IF the target row is 'being updated' THEN
(4)	             WAIT for the termination of the transaction that updated the target row

(5)                  IF (the status of the terminated transaction is COMMITTED)
   	               AND (the isolation level of this transaction is REPEATABLE READ or SERIALIZABLE) THEN
(6)	                  ABORT this transaction  /* First-Updater-Win */
                     ELSE
(7)                       GOTO step (2)
                     END IF

                /*
                 * The Second Block
                 */
(8)             ELSE IF the target row has been updated by another concurrent transaction THEN
(9)                  IF (the isolation level of this transaction is READ COMMITTED THEN
(10)                      UPDATE the target row
                     ELSE
(11)                      ABORT this transaction  /* First-Updater-Win */
                     END IF

                /*
                 * The Third Block
                 */
                ELSE  /* The target row is not yet modified               */
                      /* or has been updated by a terminated transaction. */
(12)                  UPDATE the target row
                END IF
           END WHILE
      END FOR

• [1] The target row is being updated (Figure 5.11[1]):
  ‘Being updated’ means another concurrent transaction is updating the row. In this case, the current transaction waits for the termination of the other transaction because PostgreSQL’s SI uses the first-updater-win scheme.

  For example, if concurrent transactions Tx_A and Tx_B both target the same row, Tx_B waits for Tx_A to terminate if Tx_A has already updated it and remains in progress.

  After Tx_A commits, Tx_B proceeds. Tx_B updates the row if it is at the READ COMMITTED level; otherwise (REPEATABLE READ or SERIALIZABLE), it aborts immediately to prevent Lost Updates.

• [2] The target row has been updated by a concurrent transaction (Figure 5.11[2]):
  The current transaction attempts to update the target row; however, another concurrent transaction has already updated and committed it.

  In this case, if the current transaction is at the READ COMMITTED level, it updates the target row; otherwise, the current transaction aborts immediately to prevent Lost Updates.

• [3] There is no conflict (Figure 5.11[3]):
  When no conflict exists, the current transaction can update the target row.

5.8.2. Examples

Three examples are presented below. The first and second examples demonstrate behaviors when a target row is being updated. The third example demonstrates the behavior after a target row has been updated.

5.8.2.1. Example 1

Transactions Tx_A and Tx_B update the same row in the same table. Both use the READ COMMITTED isolation level.

testdb=# -- Tx_A
testdb=# START TRANSACTION
testdb-#    ISOLATION LEVEL READ COMMITTED;
START TRANSACTION


testdb=# UPDATE tbl SET name = 'Hyde';
UPDATE 1





testdb=# COMMIT;
COMMIT

testdb=#
testdb=# -- Tx_B
testdb=# START TRANSACTION
testdb-#    ISOLATION LEVEL READ COMMITTED;
START TRANSACTION


testdb=# UPDATE tbl SET name = 'Utterson';


(this transaction is being blocked)


UPDATE 1

Tx_B operates as follows:

1. Tx_B waits for Tx_A to terminate after executing the UPDATE command, because Tx_A is currently updating the target tuple (ExecUpdate Step (4)).
2. Tx_B attempts to update the target row after Tx_A commits (ExecUpdate Step (7)).
3. Tx_B updates the target row again during the second round of ExecUpdate (ExecUpdate Steps (2), (8), (9), and (10)).

5.8.2.2. Example 2

Tx_A and Tx_B update the same row. Tx_A uses READ COMMITTED, and Tx_B uses REPEATABLE READ.

testdb=# -- Tx_A
testdb=# START TRANSACTION
testdb-#    ISOLATION LEVEL READ COMMITTED;
START TRANSACTION


testdb=# UPDATE tbl SET name = 'Hyde';
UPDATE 1





testdb=# COMMIT;
COMMIT

testdb=#
testdb=# -- Tx_B
testdb=# START TRANSACTION
testdb-#    ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION


testdb=# UPDATE tbl SET name = 'Utterson';


(this transaction is being blocked)


ERROR:couldn't serialize access due to concurrent update

Tx_B behaves as follows:

1. Tx_B waits for Tx_A to terminate after executing the UPDATE command (ExecUpdate Step (4)).
2. Tx_B aborts to resolve the conflict after Tx_A commits. This occurs because the target row has been updated and Tx_B uses the REPEATABLE READ isolation level (ExecUpdate Steps (5) and (6)).

5.8.2.3. Example 3

Tx_B (REPEATABLE READ) attempts to update a target row already updated by the committed Tx_A. Tx_B aborts in this case (ExecUpdate Steps (2), (8), (9), and (11)).

testdb=# -- Tx_A
testdb=# START TRANSACTION
testdb-#    ISOLATION LEVEL READ COMMITTED;
START TRANSACTION


testdb=# UPDATE tbl SET name = 'Hyde';
UPDATE 1

testdb=# COMMIT;
COMMIT

testdb=#
testdb=#
testdb=# -- Tx_B
testdb=# START TRANSACTION
testdb-#    ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
testdb=# SELECT * FROM tbl;
  name
--------
 Jekyll
(1 row)
testdb=# UPDATE tbl SET name = 'Utterson';
ERROR:couldn't serialize access due to concurrent update