In a database with shared/exclusive locks, when an UPDATE statement is executed at the beginning of the transaction, how do the locks work? Assuming Repeatable Read or higher, does it get a shared lock during the reading and searching stage and then an exclusive lock, or does it get an exclusive lock from the beginning? Assuming Read Committed, does an UPDATE statement get the exclusive lock only at the write stage, or does it get it as soon as it starts to read? I'm using PostgreSQL.
When PostgreSQL scans the table for rows that meet the WHERE
condition of the UPDATE
, it doesn't lock rows at all. Only when a candidate row is found, it locks it in EXCLUSIVE
mode. If the lock cannot be acquired right away, and the UPDATE
is blocked, PostgreSQL waits until it can get the lock and then reads the row again. The behavior depends on the isolation level:
with READ COMMITTED
, if the latest row version still satisfies the WHERE
condition, it is locked
with REPEATABLE READ
or SERIALIZABLE
, if the row has changed, you get a serialization error and have to retry the transaction
All this is well documented.