Bug #61261 lock tables information

• Submitted: 23 May 2011 13:56
• Modified: 19 Aug 2012 14:27
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: lock table, qc

[23 May 2011 13:56] Roberto Spadim

Description:
hi guys, i´m with a busy server and i don´t know what´s going on
the problem... some LOCKED, and some Waiting for Table, status at show processlist
the question is... what´s table is locked, and how could i know what table is locked (maybe what row is locked if it´s a innodb or other row based lock)

How to repeat:
i will send show processlist output

Suggested fix:
i don´t know, maybe a show lock tables, or something like this

[23 May 2011 14:01] Valeriy Kravchuk

Please, send also the results of SHOW INNODB STATUS. Then run

mysqladmin debug

and send the information it adds to the error log.

[23 May 2011 14:21] Roberto Spadim

they are myisam tables
i will try to get mysqladmin information and error log

[23 May 2011 14:23] Roberto Spadim

mysqladming debug - no information
# mysqladmin debug --socket=/tmp/mysql.AH.sock --user=xxxxx --password
Enter password:yyyy

nothing on error log since 1/4/2011

[25 Dec 2011 19:46] Valeriy Kravchuk

Had you been able to resolve this problem? Are you sure you had checked correct error log file? 

This is what kind of information mysqladmin debug should output for every table locked:

Thread database.table_name          Locked/Waiting        Lock_type

30      mysql.user                  Locked - write        High priority write lock
...

[26 Dec 2011 1:48] Roberto Spadim

no the informations are ok, and no information was found at mysqladmin

[26 Dec 2011 2:16] Roberto Spadim

could this information of mysqladmin be showed at mysql using a SQL command?
like
SHOW LOCKS

[25 May 2012 10:08] Valeriy Kravchuk

Please, check if new I_S tables in MySQL 5.5 solves the problem (from InnoDB tables at least). See http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html.

[25 May 2012 13:59] Roberto Spadim

yes but only for innodb
maybe another information will be need in lock_type
there´s record and table, maybe in future a partition will be needed
anyidea if this will be implemented in myisam? it´s difficult?

[25 May 2012 14:01] Roberto Spadim

another question...
sometimes this show will  be very very big (10000 rows or more)
could it include a LIMIT OFFSET and a COUNT(*) ?

[25 May 2012 14:03] Roberto Spadim

well another one (sorry many comments)
i founded informations about page and table name, but for partition tables, should have a partition name too since we will have partition with different engines (not for now, but could be nice to see this implemented to don´t lose time in future

[25 May 2012 14:15] Roberto Spadim

hum, sorry now i see that´s a table at information schema, no needs to limit and count(*)...

[19 Aug 2012 14:27] Valeriy Kravchuk

OK, let's consider this as a request for some way to get table level locks information (similar to what mysqladmin debug outputs) via I_S tables.

[22 Aug 2012 5:43] MySQL Verification Team

Related: BUG 13819913 - WHO PREVENTS METADATA LOCK ACQUISITION ?