Optimistic locking proposal

Owned by Jakub Skoczen
Last updated: Jan 18, 2023 by Julian Ladisch

Note: This document is current as of 2023-01-18.

PROTOCOL (also relevant for non-RMB modules)

FOLIO OL protocol is based on exchanging the value of _version field between the client and the server during record(s) retrieval (GET) and subsequent record update (PUT or POST in the batch update case), The ides is based on how HTTP ETAGs work: server generates a _version  value during initial record creation and inserts it into the designated _version  field in the record body. The _version  value is then returned to the client (during GET) as a regular field in the record. Client is required is to provide the exact same value in the _version  field for a subsequent update (PUT). Server then checks if the provided value matches the value stored on the server and if true accepts the update. If the provided value does not match stored value, server responds with 409 Conflict  response code.

Schema

The _version  should be added as a top-level field to the entity's JSON schema. See example in mod-inventory-storage's instance record: https://github.com/folio-org/mod-inventory-storage/blob/master/ramls/instance.json

What _version value is provided when creating a new record?

None is provided by the client. The server will insert an initial _version property when saving.

What _version value is set for existing records when optimistic locking is enabled?

None, existing records can have no _version property forever if they are not changed.

What is the maximum value of the _version property?

Java's Integer.MAX_VALUE = 2147483647. After this value RMB automatically starts from 0.

Who increments the _version property?

The server. RMB uses a database trigger that increments the _version property after checking that there is no conflict.

The client simply sends back the same _version it got from a previous GET request. The client must not alter the _version.

How should I handle an optimistic locking conflict?

When the server notices that the _version sent by the client doesn't match the _version in the database the server rejects the update request and responds with a 409 HTTP status code.

If the client still wants to update the record the client needs to send a new GET request. The server will respond with the record with the data changes from the other process or user and with the new _version value. Then the client can make own changes to the data, and try again.

Algorithm:

  1. GET the existing record from the server.
  2. Change the data in the record as needed.
  3. Optional: Check that the changes in step 2. actually alter the record. If step 2. doesn't change the record skip all following steps to avoid unnecessary _version increments that may lead to unnecessary optimistic locking conflicts.
  4. Send the record to the server for update (PUT, or some bulk update using POST).
  5. If the server responds with a 409 status code indicating an optimistic locking conflict repeat from step 1. (or simply fail and let the user/admin decide what to do).

Why isn't there a grace period for instance/holding/item?

Can't you provide two API endpoints, one with optimistic locking, on without optimistic locking?

During the pointing process institutions ranked optimistic locking very high. Optimistic locking has been discussed for over a year. A test with "logOnConflict" has been run since 2021-02-01 and showed no problems that require a grace period. Allowing to use an API that circumvents optimistic locking for some grace period defeats the purpose. The new _version property is a regular property in the JSON that most code simply passes on, therefore most code don't need to be changed. Core platform team reports that the majority of code changes are needed in unit tests, not in production code.

Therefore it was decided to enable optimistic locking for instance/holding/item without grace period.

How can I enable/disable optimistic locking for instance/holding/item?

Simply exchange mod-inventory-storage and run the module upgrade for mod-inventory-storage (POST /_/tenant).

  • mod-inventory-storage v23.0.0 (Lotus) and all later versions have optimistic locking enabled
  • mod-inventory-storage v22.0.* (Kiwi) has optimistic locking disabled (conflicts are logged but don't fail the update)
  • mod-inventory-storage v21.0.* (Juniper) has optimistic locking disabled (conflicts are logged but don't fail the update)
  • mod-inventory-storage v20.2.* (Iris) has optimistic locking disabled (conflicts are logged but don't fail the update)
  • mod-inventory-storage v19.*.* (Honeysuckle) and all previous versions have optimistic locking disabled (conflicts are not logged, no _version property gets created)
  • docker.dev.folio.org/mod-inventory-storage:22.0.0-optimistic-locking has optimistic locking enabled
  • docker.dev.folio.org/mod-inventory-storage:22.0.2-optimistic-locking has optimistic locking enabled

When the mod-inventory-storage Rancher workload runs the new image open its drop down menu, click "Execute Shell" and trigger the module upgrade using 22.0.0 for both module_from and module_to:

curl -w"\n" -s -S -D - -H "Content-type: application/json" -H "x-okapi-url-to: https://myteam-okapi.ci.folio.org/" -H "x-okapi-tenant: diku" \
     -d '{"module_from": "22.0.0", "module_to": "22.0.0" }' http://localhost:8081/_/tenant

This module upgrade will install the optimistic locking triggers (OL enabled or OL disabled) the module ships with.

Is deletion protected by optimistic locking?

No. Deletes are executed unconditionally without any optimistic locking checks. Deletes are out of scope of this implementation. GBV has reported that they don't need optimistic locking for deletes because they don't know of any real world delete conflicts.

RMB schema.json

RMB's schema.json uses "withOptimisticLocking" to configure optimistic locking. For details see RMB Readme.

Example for mod-inventory-storage v23.0.2 with instance optimistic locking enabled: https://github.com/folio-org/mod-inventory-storage/blob/v23.0.2/src/main/resources/templates/db_scripts/schema.json#L342-L346

Example for mod-inventory-storage v22.0.0 with instance optimistic locking disabled (log a conflict but don't fail the instance update): https://github.com/folio-org/mod-inventory-storage/blob/v22.0.0/src/main/resources/templates/db_scripts/schema.json#L335-L339

RMB/Postgres implementation

RMB-727 - Getting issue details... STATUS

Once a table has been configured with "withVersioning" property in schema.json, RMB installs:

an INSERT trigger that populates the initial value for the "version" property
an UPDATE trigger that compares NEW value with the OLD value and if they match updates the NEW value to a new generated value and allows the update

https://github.com/folio-org/raml-module-builder/blob/master/domain-models-runtime/src/main/resources/templates/db_scripts/optimistic_locking.ftl

This implementation is unlikely to cause a significant performance degregation.

If there is a need to speed up updates from an external source of truth where getting the old record with the current _version property should be avoided this can be implemented at a later time; this was considered in RMB-727 but postponed.

Does the RMB trigger implementation protect against concurrent access to _version?

Yes, PostgreSQL's transaction isolation works. Simply try it out with two database connections (for example two psql at the same time):


1st database connection
SET search_path TO diku_mod_inventory_storage;
insert into item (id, jsonb) values ('9b577ea5-1b3d-4b73-acdf-afbaabae96de', '{}');
update item set jsonb = ('{"_version": 1}' || pg_sleep(5))::jsonb WHERE id = '9b577ea5-1b3d-4b73-acdf-afbaabae96de';
UPDATE 1
Time: 5015.148 ms (00:05.015)
2nd database connection
SET search_path TO diku_mod_inventory_storage;
update item set jsonb = ('{"_version": 1}' || pg_sleep(5))::jsonb WHERE id = '9b577ea5-1b3d-4b73-acdf-afbaabae96de';
ERROR: 23F09: Cannot update record 9b577ea5-1b3d-4b73-acdf-afbaabae96de because it has been changed (optimistic locking): Stored _version is 2, _version of request is 1
CONTEXT: PL/pgSQL function item_set_ol_version() line 8 at RAISE
SCHEMA NAME: diku_mod_inventory_storage
TABLE NAME: item
LOCATION: exec_stmt_raise, pl_exec.c:3323
Time: 10008.817 ms (00:10.009)

Notice that the 2nd UPDATE takes 10 seconds: 5 seconds while being blocked by the 1st UPDATE, 5 seconds for the own pg_sleep.

VERSION format

Use a simple (to calculate and update) increment counter. Other options like hashing or a UUID have issues without clear benefits: hashing is more computationally complex; creating a random UUID doesn't work when clustering because each node creates a different UUID for the same record (learn more at Pgpool-II Restrictions).

POST Example 

POST /instance-storage/instances
{
  "source": "Local: MARC",
  "title": "ADVANCING LIBRARY EDUCATION: TECHNOLOGICAL INNOVATION AND INSTRUCTIONAL DESIGN",
  "instanceTypeId": "2b94c631-fca9-4892-a730-03ee529ffe2c",
}
201 Created

GET Example

GET /instance-storage/instances/601a8dc4-dee7-48eb-b03f-d02fdf0debd0
{
  "id": "601a8dc4-dee7-48eb-b03f-d02fdf0debd0",
  "_version": 1,
  "source": "Local: MARC",
  "title": "ADVANCING LIBRARY EDUCATION",
  "instanceTypeId": "2b94c631-fca9-4892-a730-03ee529ffe2c",
}

200 OK

PUT Example

PUT

/instance-storage/instances/601a8dc4-dee7-48eb-b03f-d02fdf0debd0
{
  "id": "601a8dc4-dee7-48eb-b03f-d02fdf0debd0",
  "_version": 1,
  "source": "Local: MARC",
  "title": "Advancing Library Education: Technological Innovation and Instructional Design",
  "instanceTypeId": "2b94c631-fca9-4892-a730-03ee529ffe2c",
}

201 Updated

or

409 Cannot update record 601a8dc4-dee7-48eb-b03f-d02fdf0debd0 because it has been changed (optimistic locking): Stored _version is 2, _version of request is 1