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.