Re: Global read lock on delete

[shawn l.green]

On 12/9/2015 9:59 AM, Artem Kuchin wrote:
Hello!

I am actually using MariaDB, but they do not seem to have any public
discussion system
and i suppose that engine is the same basically, so, problems are
probably  the same.

Today i setup the server to show locks and notice this:

|THREAD_ID       LOCK_MODE       LOCK_DURATION   LOCK_TYPE
TABLE_SCHEMA    TABLE_NAME
268871  MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT    Global read lock
270022  MDL_INTENTION_EXCLUSIVE MDL_STATEMENT   Global read lock
268871  MDL_SHARED_NO_READ_WRITE        MDL_EXPLICIT    Table metadata
lock     spru    searchsobjects
268871  MDL_SHARED_NO_READ_WRITE        MDL_EXPLICIT    Table metadata
lock     spru    searches
268871  MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT    Schema metadata lock
spru
270022  MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lock
spru

The threads are
ID: 270022 TIME:185 COMMAND:Query STATE:Waiting for table metadata lock
USER:spru DB:spru
LOCK TABLES searchsobjects WRITE, searches WRITE

ID: 268871 TIME:3 COMMAND:Query STATE:updating USER:spru DB:spru
DELETE FROM searchsobjects WHERE search_id IN (
3680622,3677720,3679348,3679347,3680621,3678106,3678105,3680597,3680596,3680595,3676915,3676914,3676913,36777

19,3677718,3677717,3677716,3676984,3677795,3677794,3677793,3677792,3677796,3677802,3677801,3677800,3677799,3677798,3677797,3680580,3676988,3677791,3680589,36

77790,3677789,3677788,3677787,3677786,3677785,3677784,3677783,3677782,3680575,3677781,3677780,3677779,3677778,3677777,3677776,3677775,3677774,3677773,3677772

,3677771,3677770,3677769,3677768,3677767,3677766,3677765,3677764,3680619,3680620,3682405,3677763,3677762,3677761,3677760,3677759,3677758,3680601,3677757,3680

627,3680628,3680576,3680577,3680625,3680626,3680624,3680623,3677754,3679280,3679279,3679278,3679277,3679276,3679867,3679890,3680588,3677753,3677064,3677752,3

677751,3677750,3677749,3679608,3679607,3679606,3679605,3680613 )


So, by thread id it seems like DELETE started first and the LOCK TABLES
was issued.
However, i do not understand how GLOBAL READ LOCK became involved in
this all? And both lock tables and delete requested global read lock.

All tables are myisam. MariaDB  is 10.0.22 (mysql 5.6 based as i
understand)

Artem


You gave the answer in your last statement: "All tables are myisam" .

The MyISAM storage engine is not transactional and it does not do 
row-level locking. All UPDATE and DELETE operations require a full table 
lock to perform and those must wait for all earlier readers or writers 
to exit the table before they can start.  INSERT operations are special 
as you can enable a mode to allow INSERTs to happen only at the end of 
the file and not be blocked while one of the other two operations are in 
progress.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql