I am selecting a dataset of records from table 'tablename' and then iterating through the dataset, updating one record at time (UPDATE tablename SET... WHERE id=x).
Each update is done within a transaction block, and each one sets relation RowExclusiveLocks on the
tablename
tablename_pkey
and indexes in tablename
However I was a little surprised to see a page SIReadLock to be set on
tablename_pkey
Under what circumstances would this happen ?
The documentation says:
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 and therefore 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 src/backend/storage/lmgr/README-SSI you learn more:
Predicate Locking
-----------------
Both S2PL and SSI require some form of predicate locking to handle
situations where reads conflict with later inserts or with later
updates which move data into the selected range. PostgreSQL didn't
already have predicate locking, so it needed to be added to support
full serializable transactions under either strategy. Practical
implementations of predicate locking generally involve acquiring
locks against data as it is accessed, using multiple granularities
(tuple, page, table, etc.) with escalation as needed to keep the lock
count to a number which can be tracked within RAM structures. This
approach was used in PostgreSQL. Coarse granularities can cause some
false positive indications of conflict. The number of false positives
can be influenced by plan choice.
So this is how PostgreSQL checks if a concurrent transaction adds another row that would influence the result of the query and thus has a read-write dependency with the current transaction. Theoretically, it would be enough to lock the "spaces" between the index entry and the adjacent entries, but for practical reasons, the whole page is locked.
As a consequence, you can get false positive serialization failures in PostgreSQL, but that is no less that the documentation promises:
While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution.