Transactions Locked

MySQL & MariaDB
1

Hello,

Currently seeing a lot of update/insert statements locked for many seconds when checking out ‘show engine innodb status’.

Here is 1 example of many:

—TRANSACTION 34054669731, ACTIVE 475 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1413812, OS thread handle 139497777927936, query id 22396585500 10.10.55.55 schema_name updating
update schema.table set stuff=1 where primary_key=1
Trx read view will not see trx with id >= 34054669731, sees < 34050892606
------- TRX HAS BEEN WAITING 174 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4171 page no 228028 n bits 136 index PRIMARY of table schema.table trx id 34054669731 lock_mode X locks rec but not gap waiting

When I query the performance_schema:
select lock_table, lock_mode, count() from innodb_locks group by lock_table, lock_mode;
±----------------------------------±----------±---------+
| lock_table | lock_mode | count() |
±----------------------------------±----------±---------+
| schema.table-a | S | 38 |
| schema.table-a | X | 25 |
| schema.table-b | S | 124 |
| schema.table-b | X | 121 |
| schema.table-c | X | 9 |
±----------------------------------±----------±---------+
5 rows in set, 1 warning (0.01 sec)

Suggestions on how to drill down into these locking issues? Some of the update/insert statements (which are updating single records, on primary keys so they should be super fast) are waiting around for over 100 seconds.

Thanks.

2

Hi, you have a long transaction locking the table. You should be able to see it with something like:
select trx_id, trx_state, trx_started, current_time, trx_mysql_thread_id, trx_query, trx_isolation_level, trx_rows_modified, trx_rows_locked, trx_lock_structs from information_schema.innodb_trx where trx_started < now() - interval 10 second order by trx_started desc limit 5;

Also you can check locks with
SELECT * FROM sys.innodb_lock_waits;

3

thanks. I will definitely use these. We are seeing deadlocking which would be great for some feedback.

LATEST DETECTED DEADLOCK

2023-11-30 11:55:31 0x7eda973e1700
*** (1) TRANSACTION:
TRANSACTION 34121644418, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3090628, OS thread handle 139493935970048, query id 33235402581 10.20.61.157 schemaname update
INSERT INTO table(columns) VALUES(values) → PLEASE NOTE THE INSERT STATEMENT HERE IS EXACTLY THE SAME AS INSERT BELOW, ALL SAME VALUES
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3832 page no 99177 n bits 344 index indexname of table schema.table trx id 34121644418 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 34121180830, ACTIVE 206 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3090901, OS thread handle 139477305399040, query id 33208125138 10.20.60.195 schema update
INSERT INTO table(columns) VALUES(values)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3832 page no 99177 n bits 344 index indexname of table schemaname.tablename trx id 34121180830 lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3832 page no 99177 n bits 344 index uk_users_email_org_id of table schemaname.tablename trx id 34121180830 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

4

How best to troubleshoot and fix this situation?

Also, last line *** WE ROLL BACK TRANSACTION (2) → Is this a confirmation that mysql has resolved the deadlock and the situation has been resolved?

LATEST DETECTED DEADLOCK

2023-11-30 11:55:31 0x7eda973e1700
*** (1) TRANSACTION:
TRANSACTION 34121644418, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3090628, OS thread handle 139493935970048, query id 33235402581 10.20.61.157 schemaname update
INSERT INTO table(columns) VALUES(values) → PLEASE NOTE THE INSERT STATEMENT HERE IS EXACTLY THE SAME AS INSERT BELOW, ALL SAME VALUES
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3832 page no 99177 n bits 344 index indexname of table schema.table trx id 34121644418 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 34121180830, ACTIVE 206 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3090901, OS thread handle 139477305399040, query id 33208125138 10.20.60.195 schema update
INSERT INTO table(columns) VALUES(values)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3832 page no 99177 n bits 344 index indexname of table schemaname.tablename trx id 34121180830 lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3832 page no 99177 n bits 344 index uk_users_email_org_id of table schemaname.tablename trx id 34121180830 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

5

@ChrisDK This is mostly because of the application/schema design, please read our blog this will help you understand how it works and how to resolve it thanks How to Deal with and Resolve MySQL Deadlocks - Percona.