Optimistic Concurrency Control

Table of Contents

• Introduction
• Optimistic Update
• Optimistic Delete
• Known Limitations

Introduction

Even though CrateDB does not support transactions, Optimistic Concurrency Control can be achieved by using the internal system column _version.

Every new row has an initial version of 1. This value is increased by 1 on every update.

It’s possible to fetch the _version by selecting it:

cr> select name, id, "_version" from locations
... where kind = 'Star System' order by name asc;
+----------------+----+----------+
| name           | id | _version |
+----------------+----+----------+
| Aldebaran      | 4  | 3        |
| Algol          | 5  | 3        |
| Alpha Centauri | 6  | 3        |
| Altair         | 7  | 1        |
+----------------+----+----------+
SELECT 4 rows in set (... sec)

These _version values can now be used on updates and deletes.

Note

Optimistic concurrency control only works using the = operator, checking for the exact _version your update/delete is based on.

Optimistic Update

Querying for the correct _version ensures that no concurrent update has taken place:

cr> update locations set description = 'Updated description'
... where id=5 and "_version" = 3;
UPDATE OK, 1 row affected (... sec)

Updating a row with a wrong or outdated version number will not execute the update and results in 0 affected rows:

cr> update locations set description = 'Updated description'
... where id=5 and "_version" = 2;
UPDATE OK, 0 rows affected (... sec)

Optimistic Delete

Of course the same can be done when deleting a row:

cr> delete from locations where id = '6' and "_version" = 3;
DELETE OK, 1 row affected (... sec)

Known Limitations

• The _version column can only be used when specifying the whole primary key in a query. For example, the query below is not possible with our used testing data because name is not declared as a primary key and results in an error:

  cr> delete from locations where name = 'Aldebaran' and "_version" = 3;
  SQLActionException... "_version" column can only be used in the WHERE ...

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.