Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-14025

Deadlock during concurrent generic object update operations with MySQL repository

    Details

    • Target Version/s:
    • Verified Version/s:
    • Story Points:
      2
    • Sprint:
      2019.15 - IDM
    • Support Ticket IDs:

      Description

      Multiple concurrent update operations against the same generic object within a MySQL repository may result in deadlocks under heavy load.

      Analysis of the MySQL deadlock indicates that it is OpenIDMs use of LOCK IN SHARE MODE when performing ReadForUpdate queries which enables the deadlock to occur.

      The following is the sequence which results in the deadlock:

      Tx1: SELECT * FROM {table} WHERE id = {id} LOCK IN SHARE MODE

      • IS Table Lock GRANTED
      • S Row Lock GRANTED

      Tx2: SELECT * FROM {table} WHERE id = {id} LOCK IN SHARE MODE

      • IS Table Lock GRANTED
      • S Row Lock GRANTED

      Tx1: UPDATE {table} SET {col} = {val} WHERE id = {id}

      • X Row Lock WAITING

      Tx2: UPDATE {table} SET {col} = {val} WHERE id = {id}

      • X Row Lock WAITING

      Upon attempting the UPDATE within Tx2 MySQL detects the deadlock and fails with the following:

      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

      Note that IS and S locks are both Shared locks, hence why they are granted to both Tx1 and Tx2 without blocking. The X locks required by both Tx1 and Tx2 in order to perform the UPDATE statements can not be granted while the shared locks are held.

      The above sequence is easily reproduced via two separate MySQL client sessions both with the specific SQL statements executed by OpenIDM during a Generic Object update operation as well as with a standalone test table.

      NOTE: The use of LOCK IN SHARE MODE was introduced specifically to address performance issues related to lock contention against the OpenIDM objecttypes table during update operations.  Reverting to FOR UPDATE would re-introduce the performance issues which instigated the switch to LOCK IN SHARE MODE and therefore is not (on it's own) a viable solution.

      One solution which has been tested and confirmed to resolve the issue is to:

      1. Remove the INNER JOIN within the ReadForUdpate statement in favor of a SubQuery with LOCK IN SHARE MODE.  The prevents MySQL from holding an exclusive lock on the row within the objecttypes yet still provides referential integrity.
      2. Revert the outer SELECT statement back to FOR UPDATE such that the row being updated within the genericobjects table is locked exclusively.

      After #1 and #2 above are performed, the ReadForUpdate query for the MySQL repository becomes the following:

      SELECT obj.* FROM genericobjects obj WHERE obj.objecttypes_id = (SELECT objtype.id FROM objecttypes objtype WHERE objtype.objecttype = ? LOCK IN SHARE MODE) AND obj.objectid = ? FOR UPDATE;

       

      See following Reference related to MySQL Locking Reads: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                cgdrake Chris Drake
                Reporter:
                cgdrake Chris Drake
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: