Applications can use Explicit Locks to prevent Lost Updates

If your application needs to do more than an atomic update (like perform validations), or if your database does not support atomic write operations, you can explicitly lock objects that will be updated.

Then the application can safely perform a Read-Modify-Write cycle. If any other transaction tries to read the object concurrently, it is forced to wait until the first transaction has been completed.

The FOR UPDATE clause in SQL is made available to support explicit locking. When a SELECT is executed along with a FOR UPDATE, the database locks all rows returned by the query, until the enclosing transaction is committed.

BEGIN TRANSACTION;

SELECT * FROM pages
  WHERE title = 'T21' AND tenant_id = 1142
  FOR UPDATE;

-- Check whether the page can be published by the logged-in user.
-- If valid, update the publishing flag on the page
--      returned by the previous SELECT.
UPDATE pages SET published = true WHERE page_id = 1234;

COMMIT;```

Most programming languages either have built-in support for locks or provide locking modules that can help make code thread-safe. Python, for instance, provides the `Lock` class, which is a mutual-exclusion lock (mutex). `Lock` can be used to safely update objects in memory or push database updates serially.


export const _frontmatter = {"type":"note","title":"Applications can use Explicit Locks to prevent Lost Updates","last_updated_on":"2021-02-17T00:00:00.000Z","slug":"explicit-locks-prevent-lost-updates","tags":["Explicit Locks","Lost Updates"],"draft":false,"links":[{"title":"Lost Updates","slug":"lost-updates"},{"title":"Serializable Isolation is the only way to prevent all write-related race conditions","slug":"serializable-isolation-only-guarantee-against-race-conditions"}]}

Related: