Optimistic concurrency control¶
Invenio makes use of SQLAlchemy’s version counter feature to provide optimistic concurrency control on the records table when the database transaction isolation level is below repeatable read isolation level (e.g. read committed isolation level which is the default in PostgreSQL).
Imagine the following sequence of events for two transactions A and B:
Transaction A reads existing record 1.
Transaction B reads existing record 1.
Transaction A modifies record 1.
Transaction B modifies record 1.
Transaction A commits.
Transaction B commits.
Repeatable read¶
Under either serializable and repeatable read isolation level, the transaction B in step 4 will wait until transaction A commits in step 5, and then produce an error as well as rollback then entire transaction B - i.e. transaction B never commits.
Read committed¶
Under read committed isolation level (which is the default in PostgreSQL), then again transaction B in step 4 will wait until transaction A commits in step 5, however transaction B will then try to update the record with the new value from transaction A.
The JSON document for a record is stored in a single column, thus under read committed isolation level, changes made by transaction A to the JSON document would be overwritten by transaction B.
To prevent this scenario under read committed isolation level, Invenio stores a version counter in the database table. The fields of the records table looks like this:
id
(uuid)json
(jsonb)version_id
(integer)created
(timestamp)updated
(timestamp)
When transaction A modifies the record in step 3, it does it with an UPDATE
statement which looks similar to this:
UPDATE records_metadata
SET json=..., version_id=2
WHERE id=1 AND version_id=1
When transaction B tries to modify the record in step 4 it uses the same
UPDATE
statement. As described above, transaction B then waits until
transaction A commits in step 5. However, now the WHERE
condition (id=1
and version_id=1
) will no longer match the record’s row in the database
(because version_id
is now 2). Thus transaction B will update 0 rows
and make SQLAlchemy throw an error about stale data, and afterwards rollback
the transaction.
Thus, the version counter prevents scenarios that could cause concurrent transactions to overwrite each other under read committed isolation level.
Note
The version counter does not prevent concurrent transactions to overwrite
each other’s data if you update many records in a single UPDATE
statement. Normally this is not possible if you use the
Record
API.
If, however, you use the low-level SQLAlchemy model
RecordMetadata
directly, it is possible
to execute UPDATE
statements that update multiple rows at once and you
should be very careful and be aware of details (or e.g. change your
isolation level to repeatable read).
REST API¶
The version counter is also used in the REST API to provide concurrency control. The version counter is provided in an ETag header when a record is retrieved via the REST API. When a client then issues an update of a record and includes the version counter in the If-Match header, it’s checked against the current record’s version and refused if it doesn’t match, thus preventing REST API clients to overwrite each other’s changes.