databasepostgresqlconcurrencylocks

In a database with shared/exclusive locks, when an UPDATE statement is executed at the beginning of the transaction, how do the locks work?


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.


Solution

  • 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:

    All this is well documented.