CodePark/ questions / Locking, concurrency & deadlocks
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Locking, concurrency & deadlocks

You use optimistic locking. The row with this id exists, but the statement reports UPDATE 0. What does that mean?

UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;
Someone else changed the document after you read version $3; reload it and retry, or report a conflict to the user

It means someone else changed the document after you read version $3. Your WHERE checks the version. Another writer already increased version, so your update matched no rows. Reload the document and retry, or report a conflict to the user.

"The row is locked by another transaction" is wrong. A row lock held by someone else makes the statement wait. It does not return 0.

"The new body equals the old one, so PostgreSQL skipped the write" is also wrong. PostgreSQL updates the row even if the values are unchanged.