[MDEV-16137] In_use does not increment with LOCK TABLES ... WRITE requests Created: 2018-05-10  Updated: 2018-05-23  Resolved: 2018-05-23

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Locking
Affects Version/s: 5.5, 10.0, 10.1, 5.5.60, 10.2, 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Ragle Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: upstream
Environment:

CentOS 6 ( 2.6.32-696.23.1.el6.x86_64 )


 Description   

Per the SHOW OPEN TABLES documents (both here and in the MySQL docs) the In_use entry is supposed to show "Number of table locks or lock requests there are for the table." The MySQL docs even go so far as to give the example:

For example, if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client will block waiting for the lock, but the lock request causes In_use to be 2.

But that's not what I'm seeing in my testing. Rather, if I LOCK TABLES ... WRITE on a table then In_use simply shows as 1 until I UNLOCK TABLES, regardless of how many additional LOCK TABLES statements I issue from other clients for the same table.

The tables I'm testing with are MyISAM tables. If this is a limitation of that particular engine then perhaps the docs just need to be updated.



 Comments   
Comment by Elena Stepanova [ 2018-05-13 ]

Thanks for the report.

Repeatable with all of MariaDB 5.5-10.3 and MySQL 5.5-8.0. The latest released MySQL 5.1 still had the documented behavior.

My guess is that it changed with introducing MDL in 5.5.3, and the new behavior is possibly expected, e.g. the second thread is waiting for a higher level lock and not yet the table lock as such; but then, the MariaDB documentation could be clearer and more specific (MySQL manual is specific, but apparently it doesn't match the reality anymore – probably it wasn't updated after the change).

svoj, could you please confirm that it works as intended and pass it over to documentation? (Or object and keep it for fixing, if that's what's needed).

Comment by Sergey Vojtovich [ 2018-05-13 ]

Right, Elena.

Documentation says:

Number of table locks or lock requests there are for the table.

But strictly speaking it is:

Number of being used table instances (aka TABLE). MDL lock is acquired before table instance becomes used, table level lock (aka THR_LOCK) - after.

Even in 5.1 it meant the above and not "number of table locks or lock requests". It just didn't hang on MDL.

Starting with 5.5 LOCK TABLES ... WRITE acquires strong MDL lock. Concurrent connections will wait on this MDL lock and not be able to go that far and use table instance. So in_use is not incremented.

Comment by Ian Gilfillan [ 2018-05-23 ]

https://mariadb.com/kb/en/library/show-open-tables/ updated

Generated at Thu Feb 08 08:26:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.