1

I have a simple table with a one-column VARCHAR(32) primary key, with about 8000 rows. It has no other indexes except the primary key. I'm updating individual rows in this table quite rapidly, about 50-60 times per second, via a Tomcat 10 servlet using a jdbc connection pool. Updates to any one particular row generally come in about once every 5 seconds, rarely or never more than that.

The update statement uses only the primary key in the where clause, and changes the value of one of the non-primary key columns. The database is set to READ COMMITTED isolation.

update MY_TABLE set COLUMN6 = UNIX_TIMESTAMP(CURRENT_TIMESTAMP) where NAME = 'whatever';

My problem is that these rapid updates are causing frequent deadlocks, one every 1-3 seconds. This is unexpected since each update should lock only one row. My

show engine innodb status

showed the latest deadlock, indicating that "transaction 1" locked 14 rows that it was not updating and "transaction 2" locked one row that it was not updating. The locked rows overlapped in such a way to cause a deadlock.

I can't understand why my updates are locking more than one row.

MY_TABLE
+----------------+-------------+------+-----+-------------------+-----------------------------+
| Field          | Type        | Null | Key | Default           | Extra                                         |
+----------------+-------------+------+-----+-------------------+-----------------------------+
| NAME           | varchar(32) | NO   | PRI | NULL              |                                               
| COLUMN1        | varchar(32) | NO   |     | Off               |                                               
| COLUMN2        | int         | NO   |     | 0                 |                                               
| COLUMN3        | varchar(16) | NO   |     | h264              |                                               
| COLUMN4        | varchar(8)  | NO   |     | false             |                                               
| COLUMN5        | varchar(16) | NO   |     | 1280,720          |                                               
| COLUMN6        | bigint      | NO   |     | 0                 |                                               
| COLUMN7        | bigint      | NO   |     | 0                 |                                               
| UPD_TIMESTAMP  | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+-------------+------+-----+-------------------+-----------------------------+


-----------------------
LATEST DETECTED DEADLOCK
------------------------
2022-11-02 22:54:54 23275895723776
*** (1) TRANSACTION:
TRANSACTION 152762682898, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 18 lock struct(s), heap size 1128, 72 row lock(s), undo log entries 67
MySQL thread id 30781210, OS thread handle 23275085100800, query id 6650742174 172.30.0.25 user1 updating
update MY_TABLE set COLUMN6 = UNIX_TIMESTAMP(CURRENT_TIMESTAMP) where NAME = 'XXXXXXXX9303'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 383322 page no 32 n bits 248 index PRIMARY of table `MYDB`.`MY_TABLE` trx id 152762682898 lock_mode X locks rec but not gap
Record lock, heap no 22 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303032343239333832; asc XXXXXXXX9382;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71d7b; asc       {;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 8000000063629c6d; asc     cb m;;
 10: len 4; hex 6362f539; asc cb 9;;

Record lock, heap no 25 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303032363439363333; asc XXXXXXXX9633;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e718b8; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 800000006362aa75; asc     cb u;;
 10: len 4; hex 6362f539; asc cb 9;;

Record lock, heap no 34 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303035363139353033; asc XXXXXXXX9503;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e7223e; asc      ">;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53a; asc     cb :;;
 9: len 8; hex 800000006362c2ba; asc     cb  ;;
 10: len 4; hex 6362f53a; asc cb :;;

Record lock, heap no 67 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303132363739353734; asc XXXXXXXX9574;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e7219f; asc      ! ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53a; asc     cb :;;
 9: len 8; hex 800000006362e696; asc     cb  ;;
 10: len 4; hex 6362f53a; asc cb :;;

Record lock, heap no 80 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303136363039353932; asc XXXXXXXX9592;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e72312; asc      # ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53c; asc     cb <;;
 9: len 8; hex 8000000063629c4a; asc     cb J;;
 10: len 4; hex 6362f53c; asc cb <;;

Record lock, heap no 111 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303232333239353637; asc XXXXXXXX9567;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e72347; asc      #G;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53c; asc     cb <;;
 9: len 8; hex 800000006362ae1e; asc     cb  ;;
 10: len 4; hex 6362f53c; asc cb <;;

Record lock, heap no 128 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303234343339353435; asc XXXXXXXX9545;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71f8d; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53a; asc     cb :;;
 9: len 8; hex 800000006362ab4c; asc     cb L;;
 10: len 4; hex 6362f53a; asc cb :;;

Record lock, heap no 133 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303235383839353331; asc XXXXXXXX9531;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71aff; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 800000006362b41d; asc     cb  ;;
 10: len 4; hex 6362f539; asc cb 9;;

Record lock, heap no 134 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303235393139363737; asc XXXXXXXX9677;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71b9e; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 8000000063629b1e; asc     cb  ;;
 10: len 4; hex 6362f539; asc cb 9;;

Record lock, heap no 150 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303238363739363636; asc XXXXXXXX9666;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e723e6; asc      # ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53c; asc     cb <;;
 9: len 8; hex 800000006362b3ee; asc     cb  ;;
 10: len 4; hex 6362f53c; asc cb <;;

Record lock, heap no 155 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303239303239353632; asc XXXXXXXX9562;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71b34; asc       4;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 800000006362ad7c; asc     cb |;;
 10: len 4; hex 6362f539; asc cb 9;;

Record lock, heap no 163 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303330353039373138; asc XXXXXXXX9718;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71eee; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53a; asc     cb :;;
 9: len 8; hex 800000006362df82; asc     cb  ;;
 10: len 4; hex 6362f53a; asc cb :;;

Record lock, heap no 172 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303236303539353333; asc XXXXXXXX9533;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71883; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 800000006362f024; asc     cb $;;
 10: len 4; hex 6362f539; asc cb 9;;

Record lock, heap no 177 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303135393139383433; asc XXXXXXXX9843;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71eb9; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f53a; asc     cb :;;
 9: len 8; hex 800000006362f3d3; asc     cb  ;;
 10: len 4; hex 6362f53a; asc cb :;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 383322 page no 20 n bits 256 index PRIMARY of table `MYDB`.`MY_TABLE` trx id 152762682898 lock_mode X locks rec but not gap waiting
Record lock, heap no 184 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032393137363539333033; asc XXXXXXXX9303;;
 1: len 6; hex 0023915d96a4; asc  # ]  ;;
 2: len 7; hex 0200006a2f2b6b; asc    j/+k;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 7; hex 3332302c323430; asc 320,240;;
 8: len 8; hex 800000006362f53b; asc     cb ;;;
 9: len 8; hex 800000006362f53b; asc     cb ;;;
 10: len 4; hex 6362f53b; asc cb ;;;


*** (2) TRANSACTION:
TRANSACTION 152762685092, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 1128, 19 row lock(s), undo log entries 21
MySQL thread id 30781209, OS thread handle 23274949699328, query id 6650743485 172.30.0.25 user1 updating
update MY_TABLE set COLUMN6 = UNIX_TIMESTAMP(CURRENT_TIMESTAMP) where NAME = 'XXXXXXXX9533'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 383322 page no 20 n bits 256 index PRIMARY of table `MYDB`.`MY_TABLE` trx id 152762685092 lock_mode X locks rec but not gap
Record lock, heap no 184 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032393137363539333033; asc XXXXXXXX9303;;
 1: len 6; hex 0023915d96a4; asc  # ]  ;;
 2: len 7; hex 0200006a2f2b6b; asc    j/+k;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 7; hex 3332302c323430; asc 320,240;;
 8: len 8; hex 800000006362f53b; asc     cb ;;;
 9: len 8; hex 800000006362f53b; asc     cb ;;;
 10: len 4; hex 6362f53b; asc cb ;;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 383322 page no 32 n bits 248 index PRIMARY of table `MYDB`.`MY_TABLE` trx id 152762685092 lock_mode X locks rec but not gap waiting
Record lock, heap no 172 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 12; hex 535032303236303539353333; asc XXXXXXXX9533;;
 1: len 6; hex 0023915d8e12; asc  # ]  ;;
 2: len 7; hex 010000c2e71883; asc        ;;
 3: len 9; hex 73747265616d696e67; asc streaming;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 68323634; asc h264;;
 6: len 5; hex 66616c7365; asc false;;
 7: len 8; hex 313238302c373230; asc 1280,720;;
 8: len 8; hex 800000006362f539; asc     cb 9;;
 9: len 8; hex 800000006362f024; asc     cb $;;
 10: len 4; hex 6362f539; asc cb 9;;

*** WE ROLL BACK TRANSACTION (2)

I looked at Tomcat, JDBC, and InnoDB documentation to try to find some conditions under which a lot of rapid-fire updates can get somehow combined into one transaction or something, but couldn't find anything to that effect, and I can't think of any other explanation for why all these extra rows are apparently locked when I'm just updating one.

John Daley
  • 121
  • 1
  • 5
  • 2
    What else are you doing in the same transaction that performs the update? – Barmar Nov 03 '22 at 21:01
  • I'm doing nothing at all in the transaction that performs the update, as far as I know anyway. I get a connection from the pool, give it the update statement, and close. Auto-commit is on, so I assume the update is committed and that's the transaction. Maybe I'm wrong about how that all works? – John Daley Nov 03 '22 at 21:53
  • 2
    No, you're correct about that. – Barmar Nov 03 '22 at 22:00
  • 2
    One of your UPDATE statements in the deadlock report mentions `node_id`, not `name` in the WHERE clause. But that column is not mentioned in your DESCRIBE. So I guess you're editing either the DESCRIBE or the query for this post. I can't tell which. Is it possible you have other columns, and some of your updates are against unindexed columns, or else non-uniquely indexed columns? Have you run `EXPLAIN` on your UPDATE queries to confirm the index it uses? – Bill Karwin Nov 03 '22 at 23:16
  • 2
    Another strategy is to set your transaction isolation level to READ-COMMITTED, to reduce the amount of extra locking it does (e.g. gap locks). You would think this is not necessary if your query is searching by primary key, but it's worth a try. – Bill Karwin Nov 03 '22 at 23:17
  • The NODE_ID is just tr result of my pathetic attempt to obfuscate, that's the real name of NAME. Shhhh. – John Daley Nov 03 '22 at 23:37
  • Edit: changed NODE_ID to NAME in the deadlock report. For consistent obfuscation. – John Daley Nov 03 '22 at 23:40
  • Transaction isolation is already read committed, but thanks for the tip! – John Daley Nov 03 '22 at 23:41
  • Yes, I ran explain and verified that it was using the primary key, and rows was 1. – John Daley Nov 04 '22 at 01:44
  • 2
    How big is the table (GB)? How much RAM? What is the setting of `innodb_buffer_pool_size`? HDD or SSD? Any other significant db activity going on at the same time? Is the id a hash/md5/UUID/etc? – Rick James Nov 04 '22 at 22:46
  • 1
    They seem to be fighting over getting both xx9533 and xx9303. Are any Triggers or Views involved? – Rick James Nov 04 '22 at 23:04
  • The table is small, only 1490944 bytes. The DB has 64GB of RAM and innodb_buffer_pool_size is 48GB, and it's on SSD storage. There is very significant other DB activity going on concurrently, a few hundred queries per second. There are no Triggers or Views defined that reference this table. The id is a 13 character serial number consisting of letters and digits only. – John Daley Nov 07 '22 at 15:43

1 Answers1

1

I have found the problem. Tomcat 10 ships with two different implementations of a JDBC connection pool. One of them is called DBCP 2 and is from the Apache Commons project. The other is a "home-grown" Tomcat connection pool. Apparently there's something of a history here...I gather that originally there was DBCP 1 which was inadequate (single-threaded, slow, etc.) So they made the Tomcat pool as an alternative that addressed some of these concerns. But now DBCP 2 is available and I think you're "supposed" to use that one now.

Due to some hasty, errant Googling I configured my Tomcat server with the "home-grown" pool initially and experienced the strange problems described in my question above, along with some other random DB flakiness. I finally learned about the DBCP 2 pool, switched to that, and all the problems immediately and completely stopped. It was very easy to switch, I just had to NOT set my DataSourceFactory to org.apache.tomcat.jdbc.pool.DataSourceFactory and adjust a few configuration parameters that are named differently in the default DBCP 2 pool (theses are well documented in the Tomcat 10 docs and the Apache Commons DBCP 2 docs).

I'm not sure if the home-grown Tomcat connection pool is no longer being maintained or just isn't adequate for heavier DB loads, but I sure did have much better results using DBCP 2 instead.

As an aside, I also found that there are a few other JDBC connection pool options that seem to be well regarded, like HikariCP. If you're looking to get great performance from your Tomcat servers, don't overlook the importance of making a thoughtful, informed choice of this key component.

John Daley
  • 121
  • 1
  • 5