Monday, March 27, 2006

Partitioning and Locking

In MySQL tables are locked as part of the MySQL Server code during each
statement. For a SELECT statement each table in the SELECT statement is
locked using a read lock. For UPDATE/INSERT/DELETE statements a write
lock is used. For ALTER TABLE a special lock that excludes writers but
allows readers to be processed is taken.

However what these locks actually do is dependent on the underlying
engine. For MyISAM, a read lock means no updater is allowed during the
statement and a write lock means that no other writers is allowed and
also no other readers.

For other engines such as NDB and InnoDB that employs row-level locking
internally these locks are mostly ignored except for the special ALTER
TABLE lock that ensures that no writers is allowed during the ALTER TABLE
statement.

(There is quite a lot more details to locking in MySQL that is not covered in
this blog)

Using partitioned tables, there is one lock per partition instead of one lock
per table. However for each statement a lock is taken on all partitions and
thus the behaviour is the same as if there was one lock using one table
and this lock is only held during the time of a statement and is dependent
on the storage engine used by the partitions.

It is quite certain that there will be changes of lock behaviour in 5.2. It is
quite likely that less partitions will be locked to ensure that only those
partitions touched are actually locked.

3 comments:

Anonymous said...

I have a question related to dropping partition. You said that the SELECT will have a read lock but it does not prevent a drop to occur. What happen to a query when the partition is dropped (imagine the scenario that a query is querying a partition and it's this partition that is dropped).

Thanks

Eric Her
ericher00@yahoo.com

bergithon@hotmail.com said...

Fins det nogon Svensk översetning?
eller Norsk?

bergithon@hotmail.com said...

Fins det nogon Svensk översetning?
eller Norsk?