I just had an issue with with one of our Percona servers (5.7.33) where we had a connection that was in ‘Sleep’ mode but still had 10 lock structures.
Here’s what I know, the server in question is a replica of another server in a different physical location. And looking at the SHOW ENGINE INNODB STATUS output (see below) I found there was a connection “thread id 683542” that had 10 locks. SHOW PROCESSLIST indicated that the connection 683542 was in ‘Sleep’ mode with no queries.
Trying the least intrusive thing first I attempted “KILL QUERY 683542;”, but of course nothing happened. I followed that with “KILL CONNECTION 683542;” and the locks released.
Now I have the mystery of why a connection would hold locks without a query being run??
The last line in the Transaction section of the INNODB STATUS output showed this “Trx read view will not see trx with id >= 3340244945, sees < 3340244943” (again see below for more). I’m guessing that line is trying to tell me what’s wrong, but I’m lost as to what it means.
Can anyone fill me in?
SHOW ENGINE INNODB STATUS\G:
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 683644, OS thread handle 140594649036544, query id 17701083614 System lock
INSERT INTO foo.who (table1_key, table2_key) VALUES (1150691, 1591)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 876085 page no 60 n bits 328 index PRIMARY of table
table1 trx id 3340357280 lock mode S locks rec but not gap waiting
—TRANSACTION 3340244944, ACTIVE 2159 sec
10 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
MySQL thread id 683542, OS thread handle 140594882787072, query id 17697251551 111.777.888.999 Username
Trx read view will not see trx with id >= 3340244945, sees < 3340244943
| 683542 | Username | 111.777.888.999:54167 | NULL | Sleep | 2780 | | NULL | 0 | 1 |