[FOLIO-2919] Try out optimistic locking with real module and document steps Created: 16/Dec/20  Updated: 22/Dec/20  Resolved: 21/Dec/20

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Task Priority: P2
Reporter: Hongwei Ji Assignee: Hongwei Ji
Resolution: Done Votes: 0
Labels: platform-backlog
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Relates
relates to RMB-778 Optimistic locking upgrade notes Closed
relates to RMB-777 Optimistic locking environment variab... Closed
Sprint: CP: sprint 104
Story Points: 1
Development Team: Core: Platform

 Description   

Jakub Skoczen brought up the idea that we should test the recent optimistic locking change with a real module, for example, update a table in mod-inventory-storage to see if anything is broken. The test should be done locally, and there will be no code changes to merge back to source control.



 Comments   
Comment by Hongwei Ji [ 20/Dec/20 ]

Tested with mod-inventory-storage instance table and the results are as expected.

  1. 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

  2. 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" }
    
  3. 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

  1. in db schema.json, add "withOptimisticLocking" attribute for "instance" table definition
    {
      "tableName": "instance",
      "withOptimisticLocking": "logOnConflict",
      ...
    }
    
  2. in instance.json, add "_version" field
    {
      "$schema": "http://json-schema.org/draft-04/schema#",
      "description": "An instance record",
      "type": "object",
      "properties": {
       "_version": {
        "type": "integer",
        "description": "Record version for optimistic locking"
       }
      ...
    }
    
  3. 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"
    
Comment by Jakub Skoczen [ 21/Dec/20 ]

Hongwei Ji Thanks for verifying it! This is great.

Generated at Thu Feb 08 23:24:14 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.