Optimistic Concurrency Control¶
Table of contents
Introduction¶
Even though CrateDB does not support transactions, Optimistic Concurrency Control can be achieved by using the internal system columns _seq_no and _primary_term.
Every new primary shard row has an initial sequence number of 0
. This value
is increased by 1
on every insert, delete or update operation the primary
shard executes. The primary term will be incremented when a shard is promoted
to primary so the user can know if they are executing an update against the
most up to date cluster configuration.
It’s possible to fetch the _seq_no
and _primary_term
by selecting
them:
cr> SELECT id, type, _seq_no, _primary_term FROM sensors ORDER BY 1;
+-----+-------+---------+---------------+
| id | type | _seq_no | _primary_term |
+-----+-------+---------+---------------+
| ID1 | DHT11 | 0 | 1 |
| ID2 | DHT21 | 0 | 1 |
+-----+-------+---------+---------------+
SELECT 2 rows in set (... sec)
These _seq_no
and _primary_term
values can now be used on updates
and deletes.
Note
Optimistic concurrency control only works using the =
operator, checking for the exact _seq_no
and _primary_term
your update or delete is based on.
Optimistic update¶
Querying for the correct _seq_no
and _primary_term
ensures that no
concurrent update and cluster configuration change has taken place:
cr> UPDATE sensors SET last_verification = '2020-01-10 09:40'
... WHERE
... id = 'ID1'
... AND "_seq_no" = 0
... AND "_primary_term" = 1;
UPDATE OK, 1 row affected (... sec)
Updating a row with a wrong or outdated sequence number or primary term will not execute the update and results in 0 affected rows:
cr> UPDATE sensors SET last_verification = '2020-01-10 09:40'
... WHERE
... id = 'ID1'
... AND "_seq_no" = 42
... AND "_primary_term" = 5;
UPDATE OK, 0 rows affected (... sec)
Optimistic delete¶
The same can be done when deleting a row:
cr> DELETE FROM sensors WHERE id = 'ID2'
... AND "_seq_no" = 0
... AND "_primary_term" = 1;
DELETE OK, 1 row affected (... sec)
Known limitations¶
The
_seq_no
and_primary_term
columns can only be used when specifying the whole primary key in a query. For example, the query below is not possible with the database schema used for testing, becausetype
is not declared as a primary key:cr> DELETE FROM sensors WHERE type = 'DHT11' ... AND "_seq_no" = 3 ... AND "_primary_term" = 1; UnsupportedFeatureException["_seq_no" and "_primary_term" columns can only be used together in the WHERE clause with equals comparisons and if there are also equals comparisons on primary key columns]
In order to use the optimistic concurrency control mechanism, both the
_seq_no
and_primary_term
columns need to be specified. It is not possible to only specify one of them. For example, the query below will result in an error:cr> DELETE FROM sensors WHERE id = 'ID1' AND "_seq_no" = 3; VersioningValidationException["_seq_no" and "_primary_term" columns can only be used together in the WHERE clause with equals comparisons and if there are also equals comparisons on primary key columns]
There is an exception to this behaviour, when the
WHERE
clause contains complex filtering and/or lots of Primary Key values. You can find more details here.
Note
Both DELETE
and UPDATE
commands will return a row count of 0
, if
the given required version does not match the actual version of the relevant
row.