Does the ctid chain in Postgres fork under concurrent update of a row?

Viewed 75

I am reading this guide and this guide to get a better idea of how postgres internals work, and mostly things make sense. However, I have a question about how concurrent updates would impact the ctid chain.

As I understand it, ctids point to the "next" (page,pointer) combo to describe revisions to a given row. However, what happens when two concurrent transactions are executing, attempting to update the same row? The transactions have an ordering imposed by their txids, but that is no guarantee of what order they attempt to change the row in. What are the possible outcomes of these updates?

For example, if we have a table t with a single column s as in the second guide and one update, we might have:

=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();

 txid_current 
--------------
         3666

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | BAR
(1 row)

And when we use the heap_page function to look at the page internals, it looks like:

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3664 (c) | 3666  | (0,2)
 (0,2) | normal | 3666     | 0 (a) | (0,2)

If two updates are occurring at the same time, how does the final table state vary with

  1. The serialization levels of the two updates
  2. The order the transactions start
  3. The order the transactions write to the page for this row?

UPDATE: Laurenz is correct. Corroborating pseudocode for how updates are executed, from later in one of the linked guides, is:

(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 

Note line 4, which describes the row-locking effect of setting an xmax in another transaction.

1 Answers

The answer is simpler than you think: row locks prevent concurrent modifications of the same row, so the problem never arises, and the chain of updates does not fork.

Related