# Transactions Locked

**URL:** <https://forums.percona.com/t/transactions-locked/26860>\
**Category:** MySQL & MariaDB\
**Created:** [November 29, 2023, 4:25pm UTC](https://forums.percona.com/t/transactions-locked/26860 "2023-11-29T16:25:35Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChrisDK](https://avatars.discourse-cdn.com/v4/letter/c/a6a055/32.png) [@ChrisDK](https://forums.percona.com/u/ChrisDK)\
**Post date:** [November 29, 2023, 4:25pm UTC](https://forums.percona.com/t/transactions-locked/26860/1 "2023-11-29T16:25:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [November 30, 2023, 2:14pm UTC](https://forums.percona.com/t/transactions-locked/26860/2 "2023-11-30T14:14:25Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![ChrisDK](https://avatars.discourse-cdn.com/v4/letter/c/a6a055/32.png) [@ChrisDK](https://forums.percona.com/u/ChrisDK)\
**Post date:** [November 30, 2023, 6:19pm UTC](https://forums.percona.com/t/transactions-locked/26860/3 "2023-11-30T18:19:31Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![ChrisDK](https://avatars.discourse-cdn.com/v4/letter/c/a6a055/32.png) [@ChrisDK](https://forums.percona.com/u/ChrisDK)\
**Post date:** [November 30, 2023, 6:20pm UTC](https://forums.percona.com/t/transactions-locked/26860/4 "2023-11-30T18:20:40Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Mughees\_Ahmed](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mughees_ahmed/32/9028_2.png) [@Mughees\_Ahmed](https://forums.percona.com/u/Mughees_Ahmed)\
**Post date:** [December 1, 2023, 6:44am UTC](https://forums.percona.com/t/transactions-locked/26860/5 "2023-12-01T06:44:33Z")

</div>

@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](https://www.percona.com/blog/how-to-deal-with-mysql-deadlocks/#:~:text=%E2%80%93%20Make%20changes%20to%20the%20table,or%20transaction%20to%20avoid%20it).