Deadlock while concurrently updating multiple rows on the same table

Question

We have a database where concurrent kafka streams are updating multiple rows at the same time, We are seeing deadlock occur in very few cases and I am not entirely sure what's causing it

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-05-24 16:05:22 0x2b84dd626700
*** (1) TRANSACTION:
TRANSACTION 52960655, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 115182, OS thread handle 47848750593792, query id 73757580 10.175.134.185 admin Searching rows for update
UPDATE my_model_v2 SET model = '{"id": {"qid": "14.98.50.42.JMS.h2.UNRESOLVED", "cid": "pi-232-snr", "aid": 2, "etype": 14, "td": 1}, "sliceId": 2000, "metadata": {"67744": {"lastUpdatedTime": 1684944319186, "trainingSamplesProcessed": 1818}, "67743": {"lastUpdatedTime": 1684944319186, "trainingSamplesProcessed": 1814}}, "slices": {"67744": {"avg": 2.136, "variance": 0.117, "count": 31}, "67743": {"avg": 0.295, "variance": 0.207, "count": 31}}}', updated_at = 1684944319187 WHERE q_id = '14.98.50.42.JMS.h2.UNRESOLVED' AND c_id = 'pi-232-snr' AND a_id = 2 AND e_type = 14 AND t_d = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1233 page no 30 n bits 0 index PRIMARY of table `models`.`my_model_v2` /* Partition `p1` */ trx id 52960655 lock_mode X waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len=29; bufptr=0x2b84b10ad041; hex= 31342e39382e35302e34322e4a4d532e68322e554e5245534f4c564544; asc 14.98.50.42.JMS.h2.UNRESOLVED;;
 1: len=10; bufptr=0x2b84b10ad05e; hex= 70692d3233322d736e72; asc pi-232-snr;;
 2: len=8; bufptr=0x2b84b10ad068; hex= 8000000000000002; asc         ;;
 3: len=4; bufptr=0x2b84b10ad070; hex= 8000000e; asc     ;;
 4: len=2; bufptr=0x2b84b10ad074; hex= 8001; asc   ;;
 5: len=8; bufptr=0x2b84b10ad076; hex= 800001884e4b0440; asc     NK @;;
 6: len=6; bufptr=0x2b84b10ad07e; hex= 00000327f15b; asc    ' [;;
 7: len=7; bufptr=0x2b84b10ad084; hex= ec000003bc0110; asc        ;;
 8: len=30; bufptr=0x2b84b10ad08b; hex= 000400e6012000020022000600280007002f00080000370000ba0005d007; asc          "   (   /    7       ; (total 487 bytes);

*** (2) TRANSACTION:
TRANSACTION 52960651, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 201 lock struct(s), heap size 1136, 102 row lock(s), undo log entries 1
MySQL thread id 115181, OS thread handle 47848635528960, query id 73757578 10.175.134.185 admin updating
UPDATE my_model_v2 SET model = '{"id": {"qid": "14.97.52.44.CUSTOM.h1.UNRESOLVED", "cid": "pi-232-snr", "aid": 2, "etype": 14, "td": 1}, "sliceId": 2000, "metadata": {"67799": {"lastUpdatedTime": 1684944319187, "trainingSamplesProcessed": 1819}, "67798": {"lastUpdatedTime": 1684944319187, "trainingSamplesProcessed": 1526}}, "slices": {"67799": {"avg": 3.996, "variance": 1.04, "count": 31}, "67798": {"avg": 0.0, "variance": 0.0, "count": 31}}}', updated_at = 1684944319187 WHERE q_id = '14.97.52.44.CUSTOM.h1.UNRESOLVED' AND c_id = 'pi-232-snr' AND a_id = 2 AND e_type = 14 AND t_d = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1233 page no 30 n bits 0 index PRIMARY of table `models`.`my_model_v2` /* Partition `p1` */ trx id 52960651 lock_mode X
Record lock, heap no 8 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
 0: len=30; bufptr=0x2b84b10ace01; hex= 31342e39372e35322e34342e435553544f4d2e68312e554e5245534f4c56; asc 14.97.52.44.CUSTOM.h1.UNRESOLV; (total 32 bytes);
 1: len=10; bufptr=0x2b84b10ace21; hex= 70692d3233322d736e72; asc pi-232-snr;;
 2: len=8; bufptr=0x2b84b10ace2b; hex= 8000000000000002; asc         ;;
 3: len=4; bufptr=0x2b84b10ace33; hex= 8000000e; asc     ;;
 4: len=2; bufptr=0x2b84b10ace37; hex= 8001; asc   ;;
 5: len=8; bufptr=0x2b84b10ace39; hex= 800001884e4b0411; asc     NK  ;;
 6: len=6; bufptr=0x2b84b10ace41; hex= 000003281d8b; asc    (  ;;
 7: len=7; bufptr=0x2b84b10ace47; hex= 6c00000ca5056b; asc l     k;;
 8: len=30; bufptr=0x2b84b10ace4e; hex= 000400e9012000020022000600280007002f00080000370000bd0005d007; asc          "   (   /    7       ; (total 490 bytes);

[bitmap of 256 bytes in hex: 00 03 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1233 page no 30 n bits 0 index PRIMARY of table `models`.`my_model_v2` /* Partition `p1` */ trx id 52960651 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len=29; bufptr=0x2b84b10ad041; hex= 31342e39382e35302e34322e4a4d532e68322e554e5245534f4c564544; asc 14.98.50.42.JMS.h2.UNRESOLVED;;
 1: len=10; bufptr=0x2b84b10ad05e; hex= 70692d3233322d736e72; asc pi-232-snr;;
 2: len=8; bufptr=0x2b84b10ad068; hex= 8000000000000002; asc         ;;
 3: len=4; bufptr=0x2b84b10ad070; hex= 8000000e; asc     ;;
 4: len=2; bufptr=0x2b84b10ad074; hex= 8001; asc   ;;
 5: len=8; bufptr=0x2b84b10ad076; hex= 800001884e4b0440; asc     NK @;;
 6: len=6; bufptr=0x2b84b10ad07e; hex= 00000327f15b; asc    ' [;;
 7: len=7; bufptr=0x2b84b10ad084; hex= ec000003bc0110; asc        ;;
 8: len=30; bufptr=0x2b84b10ad08b; hex= 000400e6012000020022000600280007002f00080000370000ba0005d007; asc          "   (   /    7       ; (total 487 bytes);

*** WE ROLL BACK TRANSACTION (1)

Output of Show Create table :

my_model_v2 | CREATE TABLE `my_model_v2` (
  `q_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `c_id` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `a_id` bigint(20) NOT NULL,
  `e_type` int(11) NOT NULL,
  `t_d` smallint(6) NOT NULL,
  `model` json NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  PRIMARY KEY (`q_id`,`c_id`,`a_id`,`e_type`,`t_d`,`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY HASH (updated_at DIV (1000 * 60 * 60 * 24))
PARTITIONS 100 */ |

We also have a stored procedure that gets triggered every 24 hours to drop partition which holds data which haven't been updated for 15 days.

The java code workflow goes something like this :

try (Connection connection = writeDatasource.getConnection()) {

  try(PreparedStatement preparedStatementUpdate =
          connection.prepareStatement(UPDATE_MODEL_QUERY)) {
      batchPreparedStatementForModelUpdate(dataModelList, preparedStatementUpdate);
      // Execute batch statement to Update existing models.
      updatedModelResults = preparedStatementUpdate.executeBatch();

      // Filter out the models which are not updated
      insertList =
      IntStream.range(0, updatedModelResults.length)
              .filter(i -> updatedModelResults[i] == 0)
              .mapToObj(dataModelList::get)
              .collect(Collectors.toList());
      
  }
  // Execute batch statement to store new models.

  if(!insertList.isEmpty()) {
      try (PreparedStatement preparedStatementInsert = connection.prepareStatement(INSERT_MODEL_QUERY)) {
          batchPreparedStatementForModelInsert(indestModelObjectList, preparedStatementInsert);
          preparedStatementInsert.executeBatch();
      }
  }

}

Should I do the update & insert in the same function but open two separate connections for them ? I can't lower the isolation level to read_commited , cause it might affect other work flows.

I have noticed the deadlock only occurs while updating rows & not inserting.

Answer 1

Assumptions (for this Answer):

• Lots of one-row UPDATEs are done throughout the day. They use a 5-column unique key and change only a JSON and updated_at.
• Data with updated_at before 15 days ago should be purged.
• You never INSERT new rows. (If you do, it seems like it would be combined with UPDATE into INSERT ... ON DUPLICATE KEY UPDATE ...

With those, I recommend

• Abandon PARTITIONing.
• Do a gradual and continual DELETE that is not too invasive.

Proposal:

CREATE TABLE `my_model_v2` (
  `q_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `c_id` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `a_id` bigint(20) NOT NULL,
  `e_type` int(11) NOT NULL,
  `t_d` smallint(6) NOT NULL,
  `model` json NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  PRIMARY KEY (`q_id`,`c_id`,`a_id`,`e_type`,`t_d`)  -- changed
  INDEX(updated_at)                                  -- added
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
                                                     -- no PARTITIONing

UPDATE my_model_v2 SET
        json = ...
        updated_ at = ...
    WHERE ((the 5 PK columns match));

As a separate task, running all the time.  Not 'cron'. --

SET AUTOCOMMIT = ON;
while(true) {   -- pseudo code; rewrite in suitable language
    DELETE
       FROM my_model_v2
       WHERE updated_at < 1000 * TO_SECONDS(NOW() - INTERVAL 15 DAY)
       ORDER BY updated_at 
       LIMIT 50;
    sleep 1 second   -- pseudo code
};

Notes:

• The Update seems to be what you really wanted, but was clouded by what Partitioning needed.
• The Update does need to check for deadlock and rerun. But there should be no problem if this happens.
• The purging task does a little bit of work at a time. It will probably keep up-to-date. But even if it does not, it will catch up later.
• The delete does not need a transaction nor a check for failure. It will simply retry the 50 again.
• LIMIT 50 is probably low enough to avoid noticeable interference with the Updates. It is probably high enough to be 'efficient'.
• The "sleep" may or may not be needed. It is to further avoid the Deletes pounding on the system.
• The Delete process may need a "keep-alive" mechanism. It must be restarted if the server it is running on is restarted.
• It does not matter whether the "client" (for updates, deletes) is the same machine as the server (hosting the database).
• Note how the UPDATE will reach for the 1 row it needs -- no checking for multiple rows, no checking multiple partitions.
• The Updates will be scattered over the table; so will the deletes. On the other hand, the 50 (or fewer) Delete entries in INDEX(updated_at) will be consecutive.
• If you need IODKU, then it will be efficient, like the Update.

Answer 2

Which partition do you drop every day? How does it compute what do drop? Please do EXPLAIN SELECT * FROM ... WHERE .... You may find that all the partitions are being used! If all are used, then that is another nail in the coffin of HASH partitioning.

If you are purging by day, then PARTITION BY RANGE(...) With that, there won't be an issue of which partition to DROP and the Update will prune straight to the right partition. Here is a discussion of how/why to REORGANIZE every day: Partition

The only benefit is Partitioning (that I see here) is for deleting 'old' data. Assuming that updated_at is essentially "NOW()", only the latest partition (whether BY HASH or BY RANGE) will be touched. That is, the Update does not benefit from "partition pruning".

Oh, it looks like each Hash partition has 10 (1000/100) days in it? If you are purging after 15 days, the 1000 and 100 don't make sense. So, does the purge do a big (slow) DELETE? Well, with BY RANGE, If it is really 1000 days, I would break it into monthly partitions (about 30 partitions) and do the DROP + REORGANIZE monthly. It will be virtually instantaneous. For 15 days, I would go with about 18 partitions, as discussed in the link above.

It seems strange to have so many columns in the PRIMARY KEY.

To do the Update, it will find the row(s) (probably exactly one??) that has those 5 values for (q_id,c_id,a_id,e_type,t_d) (excluding updated_at), then delete them and reinsert them (possibly at the same spot in the BTree). This adds to the issues.

The normal "fix" for a deadlock is to first catch it, then replay the transaction. This effectively lets one finish without interfering with the other. I think this is the "right" answer. However, changing the Partitioning may help.

(I'm still scratching my head over whether there might be other ways "fix" the problem.)