|
Tested with mod-inventory-storage instance table and the results are as expected.
- when optimistic locking is logOnConflict
POST instance without _version, it is a success and db adds _version = 1. Note, the HTTP response has no _version because POST implementation does not read the updated db record
PUT instance record without _version or with a wrong _version, it is a success but the module log contains a WARN log entry like below
06:52:52 [] [] [] [] WARN ? Backend notice: severity='WARNING', code='23F09', message='Cannot update record ffe24a16-065b-4db9-b3e8-bb7f16f9ee9c because it has been changed: Stored _version is 1, _version of request is <NULL>', detail='null', hint='null', position='null', internalPosition='null', internalQuery='null', where='PL/pgSQL function instance_set_ol_version() line 8 at RAISE', file='pl_exec.c', line='3337', routine='exec_stmt_raise', schema='null', table='null', column='null', dataType='null', constraint='null'
PUT instance record with correct _version is a success and the log is clean
- when optimistic locking is failOnConflict
POST works fine
PUT without _version for an existing non versioned record, it is a success and db adds _version = 1
PUT with correct _version, it is a success
PUT with wrong _version, 409 response code is returned with body like below.
When request does not pass _version in
{ "message": "Cannot update record ec5eb802-e8b3-4d0d-95a2-72a422cc4143 because it has been changed: Stored _version is 1, _version of request is <NULL>", "severity": "ERROR", "code": "23F09", "where": "PL/pgSQL function instance_set_ol_version() line 8 at RAISE", "file": "pl_exec.c", "line": "3337", "routine": "exec_stmt_raise" }
When both db and request have _version but they do not match
{ "message": "Cannot update record 5b1eb450-ff9f-412d-a9e7-887f6eaeb5b4 because it has been changed: Stored _version is 2, _version of request is 1", "severity": "ERROR", "code": "23F09", "where": "PL/pgSQL function instance_set_ol_version() line 8 at RAISE", "file": "pl_exec.c", "line": "3337", "routine": "exec_stmt_raise" }
When db has no _version but the request body has a _version. This should not happen in real life.
{ "message": "Cannot update record 6506b79b-7702-48b2-9774-a1c538fdd34e because it has been changed: Stored _version is <NULL>, _version of request is 10", "severity": "ERROR", "code": "23F09", "where": "PL/pgSQL function instance_set_ol_version() line 8 at RAISE", "file": "pl_exec.c", "line": "3337", "routine": "exec_stmt_raise" }
- when optimistic locking is off
Both POST and PUT work fine
To enable optimistic locking for a specific table. For example mod-inventory-storage instance table
- in db schema.json, add "withOptimisticLocking" attribute for "instance" table definition
{
"tableName": "instance",
"withOptimisticLocking": "logOnConflict",
...
}
- in instance.json, add "_version" field
{
"$schema": "http:,
"description": "An instance record",
"type": "object",
"properties": {
"_version": {
"type": "integer",
"description": "Record version for optimistic locking"
}
...
}
- update raml file to define 409 response code. For example in instance-storage.raml add below for /{instanceId} API
put:
responses:
409:
description: "Conflict"
body:
text/plain:
example: "Optimistic locking version has changed"
|