Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-388

PostgresClient.getById with transaction, with "SELECT … FOR UPDATE"

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      CP: sprint 99, CP: sprint 100, CP: sprint 101, CP: sprint 102
    • Story Points:
      5
    • Development Team:
      Core: Platform
    • Affected Institution:
      Cornell, Lehigh, MO State, Simmons

      Description

      We have 3 non-transactional getById methods for a single id in PostgresClient:

      getByIdAsString(String table, String id, Handler<AsyncResult<String>> replyHandler)
      getById(String table, String id, Handler<AsyncResult<JsonObject>> replyHandler)
      getById(String table, String id, Class<T> clazz, Handler<AsyncResult<T>> replyHandler)
      

      Add a transactional variant for each of them where the first parameter is "AsyncResult<SQLConnection> conn".

      Also add a transactional variant with "FOR UPDATE" for each of them. The name should be getByIdAsStringForUpdate or getByIdForUpdate. It locks the table as described on https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE .
      If there are several update requests for the same record "FOR UPDATE" will make the database queue them up so that the transactions happen in sequence. Without "FOR UPDATE" one transaction will succeed and all others will fail (rollback).

      This has been requested on https://wiki.folio.org/display/FOLIJET/Working+with+transactions+in+modules+based+on+RMB
      It is one possible solution to prevent update conflicts (UXPROD-1752).

      One example where "FOR UPDATE" is needed is check-out using an RFID reader that can read a batch of items at once. For each item there is a check that the number of checked-out items doesn't exceed the limit for the patron. Each loan increases the number of checked-out items. The second item should wait unil the first item's transaction has finished before reading the number of checked-out items.

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                hji Hongwei Ji
                Reporter:
                julianladisch Julian Ladisch
                Tester Assignee:
                Adam Dickmeiss Adam Dickmeiss
                Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases