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
