0

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.

5
  • I see a try, but I don't see a catch where you repeat it.
    – Rick James
    May 24, 2023 at 20:26
  • One of them has "lastUpdatedTime": 1684944319187 in the JSON; this is the same as for updated_at. Is it reasonable to be doing the UPDATE when that value is not changing?
    – Rick James
    May 24, 2023 at 21:13
  • @RickJames I have wrote a catch to handle the SqlException Just didn't share here. Also the UPDATE column we enter the value of current timestamp and it's not related to the lastUpdatedTime in the json.
    – owl
    May 25, 2023 at 15:08
  • @RickJames I was wondering if I create an Index on (q_id,c_id,a_id,e_type,t_d) will that help in this scenario ?
    – owl
    May 25, 2023 at 17:29
  • No. Such an index will be eschewed in favor of the Primary Key. And Partitioning gets in the way of making the PK just those 5 columns. (Which I think would otherwise be optimal.)
    – Rick James
    May 25, 2023 at 19:28

2 Answers 2

0

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.
1
  • Thank You so very much @RickJames for your inputs & your time on this issue.
    – owl
    May 26, 2023 at 15:36
0

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.)

10
  • We Run a stored procedure to drop the partition which has data 15 days older. We have a custom script/logic which decides which partition is to be dropped out of the 100. We Do currentTimeinMillis / (1000 * 60 *60 * 24) to get number of days since 1970, That's our partition logic. We wanted to maintain uniqueness based on (q_id,c_id,a_id,e_type,t_d) and also identify the row to updated based on that. Updating that row & the UPDATE column also puts the row to the recent most partition . In this way we delete the rows which haven't been updated for 15 days
    – owl
    May 25, 2023 at 15:32
  • Our Goal is to maintain 1 row based on (q_id,c_id,a_id,e_type,t_d) and also clear up old data (15 days) . The partition drop is faster that purging the db based on rows or running a big delete query. We have another similar DB minus the partitions and we don't see any deadlocks over there. I was just wondering if there's any other / better way to go about it. We have a deadlock -retry logic but when the load increases we might still run into deadlocks
    – owl
    May 25, 2023 at 15:36
  • @owl - Oh, I thought 1000 was days, not milliseconds. That means you have 100 partitions, but only 15 have any data in them?
    – Rick James
    May 25, 2023 at 19:15
  • @owl - If you are doing DROP PARTITION, then you have tackled the "delete speed issue". I don't remember if 5.7 has EXPLAIN UPDATE...; if it does, please run that. I want to see whether it is touching all partitions.
    – Rick James
    May 25, 2023 at 19:19
  • @owl - If you have the deadlock-retry logic, and it is infrequent enough and fast enough, then I would not worry. Deadlocks will occur. You simply have not encountered them yet in the non-partitioned table.
    – Rick James
    May 25, 2023 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.