Web lists-archives.com

Global read lock on delete




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




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