The idea: one business transaction, many system transactions
Martin opens customer 129 in a web form, thinks for a few minutes, types a new name and presses Save. To him that is one piece of work: a business transaction. To the server it is at least two HTTP requests, minutes apart. Each request runs its own short database transaction, a system transaction: BEGIN, a few statements, COMMIT. Between the requests nothing is open in the database. While Martin thinks, David can open the same customer and save first.
The database's isolation covers each system transaction, not the business transaction around them. So the two classic problems come back between requests: a lost update and an inconsistent read. Controlling concurrency for data that is used across several system transactions is called offline concurrency. The page runs the same two users through five strategies, one per tab: no lock, Optimistic Offline Lock, Pessimistic Offline Lock, Coarse-Grained Lock, and the same idea in HTTP with ETag and If-Match.
Business and system transactions
A request is one call from the browser and its response. A session is the series of requests from one user, held together on the server by an HTTP session (here M1 and D1). One session runs its business transactions one after another.
The simple answer would be one long system transaction from "open form" to "save". That keeps everything correct, but it keeps locks and a database connection open while a person thinks, so few users can work at once. It is a fair choice when there is little concurrency. Otherwise the business transaction is split into short system transactions, and the app adds the glue. Atomicity and durability are easy: do all the writes in the last request, in one system transaction. Isolation is the hard part, and that is what the patterns below are for.
Lost update and inconsistent read
The form posts the whole row back. Martin reads name Acme Ltd and credit 5000 at 10:00 and changes the name. David reads the same row at 10:03, raises the credit to 8000 and saves at 10:05. When Martin saves at 10:09, his form still carries credit 5000, so David's change is overwritten. Both saves report success and nobody is told. (Demo: lost update, No lock tab.)
The inconsistent read is quieter. Martin starts a $1,000 charge, and the sales tax comes from the state of the billing address: Oregon, 0 %. Before he posts it, David moves the address to Seattle, Washington, 6.5 %. Martin never changed the address, so a check on his writes alone finds nothing, and the charge is saved with the wrong tax.
Optimistic Offline Lock
Optimistic locking detects a conflict when the work is committed. Every row has a version number. The version read with the form is kept in the session (or in a hidden field), and the save puts it in the WHERE clause:
UPDATE customer
SET name = 'Acme Group', credit = 5000, version = 8,
modifiedBy = 'martin', modified = '10:09'
WHERE id = 129 AND version = 7; -- 1 row: ok, 0 rows: conflict
One statement both checks and writes, inside the last system transaction, so nobody can slip in between. The app looks at the row count. 1 row: commit. 0 rows: the row was changed or deleted since it was read. The app rolls the system transaction back and reads modifiedBy and modified to tell the user who changed it and when. (Demo: conflict on save: "changed by david at 10:05".) Use a counter, not a timestamp, as the version: clocks on different servers disagree. If you cannot add a column, the WHERE can list the old value of every column instead, at the price of a long statement.
The cost is when the loser finds out: at the very end. Martin reloads, sees David's credit, types his change again and saves version 9. If conflicts are rare that is cheap, and nothing is held while users think. An early "is it still current?" check can warn sooner, but it cannot promise the save will pass.
To catch an inconsistent read, also check the version of what the business transaction only read. With check reads on, posting the charge first runs UPDATE address SET version = 4 WHERE id = 301 AND version = 3. David's change made the address version 4, so this matches 0 rows and the charge is refused; Martin starts it again and gets $65 of tax. Bumping the version works at any isolation level; just re-reading it is enough only under repeatable read or stronger. (Demo: inconsistent read.) Untick check reads and the same steps save a charge with $0 tax.
Pessimistic Offline Lock
Pessimistic locking prevents the conflict: a business transaction must get a lock before it uses the data. A database lock (SELECT … FOR UPDATE) cannot do this, because it ends with the system transaction. Instead the app keeps its own lock table, an ordinary table:
CREATE TABLE lock (lockableid BIGINT PRIMARY KEY, ownerid VARCHAR(32)); INSERT INTO lock VALUES (129, 'M1'); -- the lock: one row, owned by session M1 DELETE FROM lock WHERE ownerid = 'M1'; -- release every lock of the session
A lock manager is the only code that touches this table. It takes the lock before loading the data, so the copy read is the latest and stays the latest. The lock is on the id (129), so it can be taken without loading anything. The owner is the session id. The primary key makes a second INSERT for 129 fail with a duplicate key. The lock manager does not wait: it refuses at once, so David is told "locked by martin since 10:00" before he types anything. Waiting for a lock that may be held for twenty minutes makes no sense, and because nobody waits, there are no deadlocks. (Demo: lock refused.) Locks are released when the business transaction ends: in the same system transaction as the save, or on cancel.
The price is liveness: while Martin holds 129, nobody else can work on it, and the code is harder to write than a version column. If the lock table is in memory instead, there must be exactly one of it, which a cluster of app servers breaks; a database table works for a cluster.
Exclusive write, exclusive read, read/write
| lock type | needed to | others while it is held | trade-off |
|---|---|---|---|
| exclusive write | edit | may view (possibly stale data), may not edit | most concurrency; readers can see data that is about to change |
| exclusive read | even load the record | may do nothing | always the latest data; least concurrency |
| read/write | READ to view, WRITE to edit | many READs together; a WRITE excludes everyone | best of both; harder to build and to explain |
With read/write locks a reader can upgrade to a writer only when it is the sole reader. In Demo: read/write locks both users read at once, David's WRITE is refused while Martin reads, granted once Martin closes the page, and then Martin's READ is refused while David writes. Two readers who both want to upgrade would deadlock with a waiting lock manager; with a refusing one, both are simply told no.
Abandoned sessions and timeouts
A web user can close the tab at any moment. No request is sent, so the server cannot know, and the lock row stays. Everyone else is locked out until the server notices. The usual tool is the HTTP session timeout (<session-timeout>30</session-timeout> in web.xml): when the container invalidates an idle session, a listener on it deletes all its locks. Another way is to give each lock row a time and treat old ones as dead. In Demo: abandoned session Martin walks away at 10:01, David is refused at 10:05, session M1 times out at 10:30 and its lock is deleted, and David gets the lock at 10:31. Optimistic locking has no such problem: an abandoned session holds nothing in the database.
Coarse-Grained Lock
Customer 129 and its addresses are edited as one unit: an aggregate with the customer as its root. Locking each object separately means finding and often loading every member just to lock it, and many rows in the lock table. A Coarse-Grained Lock is one lock for the whole group, in one of two forms:
- Shared version (optimistic). The rows do not have their own version; they all point to one row of a
versiontable. Any save first runsUPDATE version SET value = 8 WHERE id = 900 AND value = 7. Note that it is the same version, not an equal one. - Root lock (pessimistic). To lock address 301 the app navigates to its root and locks customer 129. (Pick group lock: root lock and let David open address 301 while Martin edits the customer.)
The gain is simplicity and fewer locks. The cost shows in Demo: one version for the group: David changes only the address, Martin only the customer row, and Martin's save still fails, because the whole group moved from version 7 to 8. That is right when the business sees the group as one thing (a lease and its assets); do not invent groups just to save lock rows.
Let the framework do it
One forgotten lock or version check breaks the whole scheme, and tests rarely notice. So the locking belongs in the framework, not in every command: a mapper that always adds AND version = ? and checks the row count, or a decorator that takes the read lock in every find(). The book calls this Implicit Lock. JPA and Hibernate do the optimistic half for you: a @Version field adds the check and throws OptimisticLockException on 0 rows (see Hibernate Session). The rule is simple: if a record is locked anywhere, it must be locked everywhere.
The same idea in HTTP: ETag and If-Match
A REST API keeps no session, so the version travels to the client. The response carries it as an ETag (ETag: "7"). The client sends it back with the write as If-Match: "7", which means "change it only if it is still version 7". The server turns that into the same AND version = 7. If the row has moved on, the answer is 412 Precondition Failed: the HTTP form of 0 rows. An API that refuses writes without If-Match answers 428 Precondition Required. (Demo: If-Match, 412; untick require If-Match and a PUT without it brings back the lost update.) The same ETag also serves caches as a validator; see HTTP Caching.
Which one when
| Optimistic Offline Lock | Pessimistic Offline Lock | |
|---|---|---|
| assumes | conflicts are rare | conflicts are likely, or too costly |
| the loser finds out | at Save, after all the work | when opening the record, before any work |
| held while the user thinks | nothing (a number in the session) | a row in the lock table |
| abandoned session | costs nothing | blocks others until the timeout |
| code to write | a version column and a row-count check | a lock manager, lock types, release and timeout rules |
| concurrency | high | lower; depends on the lock type |
A good default is optimistic everywhere, and pessimistic only for the few records where losing an hour of work is not acceptable. Before either, ask whether the business transaction can fit into one request, or into one long system transaction: then the database does the work and none of this is needed.
Offline locks vs database locks
Optimistic vs Pessimistic Locking shows the same two ideas inside database transactions: concurrent requests, InnoDB row locks with SELECT … FOR UPDATE, lock queues where a transaction waits, deadlocks, lock wait timeouts and automatic retries. This page is about what happens between requests, where a database lock cannot be held: the lock is application data owned by a session, a request is refused instead of made to wait, a conflict means a person redoes their work, and an abandoned session needs a timeout. See also Distributed Lock (a lease with a time limit is the same answer to a client that disappears) and Event Sourcing and CQRS (the expected version on append is an optimistic lock).
What the page leaves out
Each request takes a whole minute here, and a session is checked for expiry once a minute. Real lock managers must serialise access to the lock table (a unique key, or a serializable system transaction just for locking) and often cache which locks a session already owns. Merging business data instead of throwing the loser's work away, checking a condition instead of a whole version, re-running a query at commit to check a dynamic read, and lazy loading the path to an aggregate's root across system transactions are not shown. Neither are deleting a shared version once its last owner is gone, a pessimistic lock that also bumps the shared version, or the way an app server runs requests on threads or processes.