How can I see global locks in mysql (innodb)?

Question

If in understand correctly, running FLUSH TABLES WITH READ LOCK acquires a global read lock.

Is there any command I can run in the mysql client which shows me that lock is currently acquired?

http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html — ajreal, Mar 19 '12 at 11:49
I've tried `show full processlist`. It can show that an "update" query is waiting for the lock to be released, but it doesn't show who holds the lock. — davidbrai, Mar 19 '12 at 12:01
It does, `the FIRST command before all the write (insert,update,delete) is the owner of the lock` — ajreal, Mar 19 '12 at 12:04
@rkosegi that thread is talking about table locks. My question was about a global lock. — davidbrai, Mar 19 '12 at 16:16

score 10 · Accepted Answer · answered Mar 19 '12 at 12:25

10

Try this :

SHOW ENGINE INNODB STATUS;

It will show owner of locks and waiters (and lot of other stuff related to innodb)

answered Mar 19 '12 at 12:25

rkosegi

2

I'm familiar with the command, but it doesn't show global locks acquired by `flush tables with read lock` unless I missed it. – davidbrai Mar 19 '12 at 13:36
1

You need at least one of the PROCESS privileges for this operation. Any way I can do this without any PROCESS privileges? – wheeleruniverse Feb 12 '19 at 12:09

score 1 · Answer 2 · answered Mar 28 '14 at 10:15

1

This is not a global lock.once you come out of session ,it releases it

answered Mar 28 '14 at 10:15

Shahid Hussain

Given that the edit queue is full and not clearing... "FLUSH TABLES WITH READ LOCK" is a global lock, but it is also tied to the current login session only. To clear the READ LOCK, exit the session. There is not a command to run that clears it. Then log back in to a new SQL session and run SHOW FULL PROCESSLIST; and you will see that closing the previous session releases the lock, as all transactions will no longer be waiting. – sarlacii Jun 21 '22 at 09:10

2 Answers2