2

I have a table called SummaryTable and below is the show create table:

CREATE TABLE `SummaryTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqueId` bigint(10) unsigned NOT NULL,
  `date` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `requested` bigint(20) unsigned DEFAULT '0',
  `accepted` bigint(20) unsigned DEFAULT '0',
  `sent` bigint(20) unsigned DEFAULT '0',
  `failed` bigint(20) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `SummaryTable_uniqueId_date` (`uniqueId`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Only 1 query goes to this table which is below:

INSERT INTO SummaryTable(userId,date,requested,accepted,sent,failed) values(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE requested = requested + ?,accepted = accepted + ?, sent = sent + ?, failed = failed + ?;

I have multiple threads upserting into this table. This table currently has approx 0.5 million records. Initially when this table was created, all the upserts were quick and clean.

But as and how the table grew upto 0.5 million, all the upserts are experiencing LOCK WAIT TIMEOUT error

I thought may be the issue is due to the multiple threads accessing the table, so i reduced it to single thread but the issue still persisted.

I am not knowing where the issue is. I manually inserted into this table using the same above query and that too took a large amount of time. Even simple insert without on duplicate key update was getting timed-out.

Below is the sample data currently residing in the table:

+-------+------------+---------------+-----------+----------+-------+--------+
| id    | uniqueId   | date          | requested | accepted | sent  | failed |
+-------+------------+---------------+-----------+----------+-------+--------+
| 10464 | 1241325597 | 2019-12-05 14 |     52698 |    51541 | 42754 |   1164 |
| 10466 | 1227703657 | 2019-12-05 14 |      4094 |     4074 |  3958 |   2130 |
| 10467 | 1241897654 | 2019-12-05 14 |      1404 |     1311 |  1207 |    768 |
| 10468 | 1235335116 | 2019-12-05 14 |       127 |       62 |    46 |     97 |
| 10470 | 1241873029 | 2019-12-05 14 |      1969 |     1903 |  1641 |     66 |
| 10471 | 1240867311 | 2019-12-05 14 |      5169 |     3666 |  3254 |   1502 |
| 10476 | 1240850853 | 2019-12-05 14 |       183 |      181 |   168 |      2 |
| 10477 | 1235492827 | 2019-12-05 14 |      1237 |     1236 |  1140 |      0 |
| 10482 | 1242128197 | 2019-12-05 14 |       157 |      130 |   110 |     27 |
| 10484 | 1241323122 | 2019-12-05 14 |       292 |      251 |   183 |     35 |
+-------+------------+---------------+-----------+----------+-------+--------+

Now i tried to inserted the below query which executed very quickly..

INSERT INTO SummaryTable set userId = 1242128197, date = unix_timestamp("2020-01-05 12:00:00")*1000, requested = 1, accepted = 0, sent = 0, failed = 0;

Could someone help what's causing the insert on duplicate key update to break here

Jayvijay Shah
  • 31
  • 4
  • A transaction cannot lock itself, so at least the single-thread-approach should not experience locks if it is the only relevant factor in this problem. So [try to figure out](https://stackoverflow.com/q/6000336) what else is going on, as it seems something else than that insert is adding locks to that table. – Solarflare Jan 05 '20 at 09:22
  • Thanks, any idea why the upsert is taking more time, is it the indexed date field causing the issue? – Jayvijay Shah Jan 05 '20 at 09:37
  • The way you describe it (the upsert reaches a lock time out) means: something else is holding a lock that conflicts with your upsert. The relevant point I tried to make is: something else. You need to figure out (via the options in the linked question) what else is doing the locks. – Solarflare Jan 05 '20 at 09:43
  • Ok will figure it out, but why did my manual insert query worked perfectly then. Why did the lock factor didn't arrive there. – Jayvijay Shah Jan 05 '20 at 09:56
  • I cannot tell you that. There are 200000 possible options. My best guesses are just timing luck, a misconfigured configuration pool or some other error in your java code. But really, instead of me guessing, just check what other process is holding a lock, it gives you a starting point where to look. Even if you don't understand a single thing of that output, add it to your question so we can tell you what to look for next or what to test next. – Solarflare Jan 05 '20 at 10:28

0 Answers0