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

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

    XMLWordPrintable

Details

    • CP: sprint 99, CP: sprint 100, CP: sprint 101, CP: sprint 102
    • 5
    • Core: Platform
    • 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

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

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases