Web lists-archives.com

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_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



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