Re: Global read lock on delete
Artem - > |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA > TABLE_NAME > 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock > 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock > 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock > sprusearchsobjects > 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock > sprusearches > 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lockspru > 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lockspur The global read lock is in MDL_INTENTION_EXCLUSIVE mode, as an intention lock it's not an actual global read lock in effect, but rather something to prevent another thread to take GRL while GRL-incompatible statements (DELETE, LOCK TABLES) are still running. -- Laurynas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
On 12/9/2015 11:59 AM, Artem Kuchin wrote: 09.12.2015 19:35, shawn l.green пишет: On 12/9/2015 9:59 AM, Artem Kuchin wrote: Hello! |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lock spru 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lock spru 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. TABLE LOCK is okay, i understand that. But i see GLOBAL READ LOCK - not table lock, but GLOBAL. As i understand that it means ALL TABLES IN ALL DATABASES. Why? Artem That is something the official MySQL does not do. You would need to research the MariaDB fork's documentation to see why they report it as a global lock. I'm thinking that it might not be a full lock on all tables, just on the one, to prevent someone from changing the table's design before the queued UPDATE or DELETE could complete. We do that, too. We lock the definition while any writer is using the table. That is performed using a metadata lock (MDL). I, personally, have not had any time at all to dig that deeply into how the forks differ from the original in terms of lock management. Maybe another person on this list will know? Yours, -- 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
Re: Global read lock on delete
On 12/9/2015 12:06 PM, Artem Kuchin wrote: 09.12.2015 19:35, shawn l.green пишет: 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. Cannot find anything about that. Can you be a little more specific? It is unrelated the my question, but would be great to have too. Artem It's been a long while since I had to think about the variable --concurrent-insert so I blurred the lines just a little. I apologize. Changing the mode cannot allow concurrent UPDATE or DELETE with an INSERT. The UPDATE or DELETE will always ask for a full table lock. It only allows for concurrent SELECT and INSERT commands to happen at the same time to the same MyISAM table. http://dev.mysql.com/doc/refman/5.6/en/concurrent-inserts.html -- 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
Re: Global read lock on delete
09.12.2015 19:35, shawn l.green пишет: 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. Cannot find anything about that. Can you be a little more specific? It is unrelated the my question, but would be great to have too. Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
09.12.2015 19:35, shawn l.green пишет: On 12/9/2015 9:59 AM, Artem Kuchin wrote: Hello! |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lock spru 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lock spru 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. TABLE LOCK is okay, i understand that. But i see GLOBAL READ LOCK - not table lock, but GLOBAL. As i understand that it means ALL TABLES IN ALL DATABASES. Why? Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
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_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema 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,369,368,367,366,365,364,363,362 ,361,360,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
