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

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



    • CP: sprint 99, CP: sprint 100, CP: sprint 101, CP: sprint 102
    • 5
    • Core: Platform
    • Cornell, Lehigh, MO State, Simmons


      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


          Issue Links



                hji Hongwei Ji
                julianladisch Julian Ladisch
                Adam Dickmeiss Adam Dickmeiss (Inactive)
                0 Vote for this issue
                2 Start watching this issue



                  TestRail: Runs

                    TestRail: Cases