How can I show mysql locks?

Question

Is there anyway to show all the locks that are active in a mysql database?

You can query INNODB_LOCK_WAITS and INNODB_LOCKS tables. – Jan 29 '15 at 17:15 — , Jan 29 '15 at 17:15

score 62 · Accepted Answer · answered Jul 06 '09 at 10:47

62

See Marko's link for InnoDB tables and the caveats.

For MyISAM, there isn't a dead easy "this is the offending query" solution. You should always start with a processlist. But be sure to include the full keyword so that the printed queries aren't truncated:

SHOW FULL PROCESSLIST;

This will show you a list of all current processes, their SQL query and state. Now usually if a single query is causing many others to lock then it should be easy to identify. The affected queries will have a status of Locked and the offending query will be sitting out by itself, possibly waiting for something intensive, like a temporary table.

If it's not obvious then you will have to use your powers of SQL deduction to determine which piece of offending SQL may be the cause of your woes.

answered Jul 06 '09 at 10:47

Dan Carley

25,189
5
52
70

what I run this but there are so many rows that I cant reach the top after it finishes? – Rafael Piccinelli Feb 09 '22 at 15:53
@Dan Carley When a query is waiting for execution because the row it's meant to operate is locked by another transaction - the processlist shows 'updating' in the processlist not 'locked' in the MySQL (v5.7) instance. I'm not sure if I'm seeing 'locked' status even through the row is locked. – Vishnu Pedireddi Jun 01 '22 at 18:36
@VishnuPedireddi yeah, that's my experience. IMO the lock detection is broken on MySQL. There should be a lock wait event and status should be show waiting for a lock. On all other major relational databases Postgres, Oracle, SQL Server, DB2 there are specfic lock waits. on MySQL it is hidden under wait/io/sql/table/handler which also covers CPU reading buffers, I/O waiting for buffers and row level locking! – Kyle Hailey Aug 15 '22 at 20:30

score 50 · Answer 2 · answered Jun 05 '14 at 07:42

50

If you use InnoDB and need to check running queries I recommend

show engine innodb status;

as mentioned in Marko's link. This will give you the locking query, how many rows/tables are locked by it etc. Look under TRANSACTIONS.

The problem with using SHOW PROCESSLIST is that you won't see the locks unless other queries are queueing up.

answered Jun 05 '14 at 07:42

Polymorphix

601
5
5

This should be the accepted answer. It will have a section for "LATEST DETECTED DEADLOCK" – Gabriel Furstenheim May 11 '20 at 10:24

score 39 · Answer 3 · edited Oct 25 '18 at 00:41

39

Try SHOW OPEN TABLES:

show open tables where In_Use > 0 ;

edited Oct 25 '18 at 00:41

answered May 09 '13 at 04:55

M Sleman

1,091
9
5

I think this is the best way to identify in use locks immediately especially if you have multiple databases and hundreds of connections. – nelaaro Apr 12 '18 at 14:38

score 32 · Answer 4 · answered Nov 30 '18 at 00:05

32

None of the answers can show all the locks that are currently held.

Do this e.g. in mysql in a terminal.

start transaction;
update someTable set name="foobar" where ID=1234;
-- but no rollback or commit - just let it sit there

Clearly the transaction above holds a lock, because the transaction is still active. But no query is going on right now and nobody is waiting for a lock anywhere (yet at least).

INFORMATION_SCHEMA.INNODB_LOCKS is empty, which makes sense given the documentation, because there is only one transaction and currently nobody waiting for any locks. Also INNODB_LOCKS is deprecated anyway.

SHOW ENGINE INNODB STATUS is useless: someTable is not mentioned at all

SHOW FULL PROCESSLIST is empty, because the culprit is not actually running a query right now.

You can use INFORMATION_SCHEMA.INNODB_TRX, performance_schema.events_statements_history and performance_schema.threads to extract the queries that any active transactions have executed in the past as outlined in my other answer, but I haven't come across any way to see that someTable is locked in the above scenario.

The suggestions in the other answers so far won't help at least.

Disclaimer: I don't have innotop installed and I didn't bother. Perhaps that could work.

answered Nov 30 '18 at 00:05

Peter V. Mørch

812
7
15

I was just planning to write a question specifically about this scenario (start transaction, run update, and wait), and then I noticed your answer. I have this problem in production. I had the idea to detect a "stuck" lock in code when getting the "Lock wait timeout exceeded" error, and then do two things at the same time: re-run the query, and run SHOW ENGINE INNODB STATUS (from the other thread or process, on a second connection). But I would love to have a simpler way... – obe Jan 21 '20 at 20:05
I am no expert on this, but the example transaction is not dependent on anything, so why would it hold a lock? It will only require a short lock during commit to do the write. You need for example `SELECT ... FOR UPDATE` to explicitly lock. – Mattias Wallin May 12 '22 at 13:15
@MattiasWallin `SELECT FOR UPDATE` will create a lock, because `SELECT` otherwise wouldn't. But `UPDATE` also creates a lock until the transaction is rolled back or submitted. (I'm no expert either) – Peter V. Mørch May 13 '22 at 09:32
@PeterV.Mørch I tried it, and you are correct. Other `UPDATE` gets blocked, so there _is_ a lock. Also `SHOW ENGINE INNODB STATUS` hints about a lock: `2 lock struct(s), heap size 1128, 1 row lock(s)`. But as you already stated, nothing about _what_ is locked.. :-( – Mattias Wallin May 13 '22 at 10:19
This is the only answer I needed to know. Thank you for doing the research. I wasn't sure if the dataset in my application would cache it's own copy in memory and then "commits" everything all at once. Everything get's pushed to the database immediately. A simple commit command will then process all the cached (transactional) data in the database. – Ben May 31 '22 at 15:25

score 9 · Answer 5 · answered Jun 20 '16 at 09:10

Reference taken from this post.

You can use below script:

SELECT 
    pl.id
    ,pl.user
    ,pl.state
    ,it.trx_id 
    ,it.trx_mysql_thread_id 
    ,it.trx_query AS query
    ,it.trx_id AS blocking_trx_id
    ,it.trx_mysql_thread_id AS blocking_thread
    ,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl 
INNER JOIN information_schema.innodb_trx AS it
    ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
    ON it.trx_id = ilw.requesting_trx_id 
        AND it.trx_id = ilw.blocking_trx_id

On MySQL 5.7: `Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release.` — dolmen, Aug 31 '21 at 10:10

score 7 · Answer 6 · answered Jul 06 '09 at 10:15

7

Using this command

SHOW PROCESSLIST

will show all process currently running, including process that has acquired lock on tables.

answered Jul 06 '09 at 10:15

Arie K

1,583
5
18
27

That doesn't says which locks on which table. – dolmen Aug 31 '21 at 10:11

score 7 · Answer 7 · answered Jul 06 '09 at 10:39

7

AFAIK there's still no native way in MYSQL, but I use innotop. It's free and has plenty of other functionality as well.

Also see this link for more information on using the innotop tool.

answered Jul 06 '09 at 10:39

Marko Carter

4,092
1
29
38

How can I show mysql locks?

7 Answers7

Linked