Analysis on MySQL table lock

The storage engine layer can decide how to lock data by accepting the lock type passed by the Server layer.
Table locks are implemented at the Server layer. If MyISAM is not implemented by itself, the table locks passed at the Server layer are used completely.

The advantage of table lock is that the additional cost is low, and the disadvantage is that the concurrency supported is very small.
Currently, MySQL recognizes two classic versions as 5.1 and 5.5. However, the introduction of metadata locks in 5.5 makes the information of table locks more complex.
In 5.1, we use show full processlist. In the output result, if the State is Locked, the table lock is used.
5.5.3 Locked is replaced by Table lock, and Table lock 5.5.6 is replaced by waiting for table level lock.
In addition, some 5.1 are table locks, but 5.5 is no longer. Instead, they are replaced by "waiting for table metadata lock"
Although it is also a table lock, it is an alternative. It is a newly introduced table metadata lock. These locks are not counted in Table lock monitoring.
For example, use lock tables to display the added lock, and the lock created by the server when renaming or deleting a table.

(I) Find out who holds the table lock

Mysqladmin debug
Or, we can use show processlist, but this information is incomplete.

(Ii) Monitoring table lock

Show status like 'table _ locks %'
There are 2 output values
Table_locks_immediate: Number of table locks applied since the last start, accumulative. initialized after restart
Table_locks_waited: Number of blocked table locks, accumulative
The two variables should be combined to see how many table locks are applied and several are locked.

(Iii) Table lock Optimization

Table locks can be optimized in two ways.
1) Shorten the lock time

① The fastest way to execute a query is not to execute it, And the cache is king.
② Create an efficient MyISAM Index
③ Rational design of MyISAM Schema

2) Try to parallel

Enable the concurrent MyISAM insert feature (concurrent insert)
When concurrent_insert = 0, concurrent insert is not allowed regardless of whether empty blocks exist in the data file.
When concurrent_insert = 1, concurrent insert can be performed from the end of the data file only when no empty block exists in the middle of the data file.
When concurrent_insert = 2, concurrent insert can be performed from the end of the data file regardless of whether there are empty blocks in the data file.
We recommend that you set concurrent_insert = 1

3) read/write priority

By default, MySQL has a higher write priority than read, but if our business is dominated by read, such as www, blog, etc.
In this case, we can set low_priority_updates = 1 to tell MySQL to process read requests first.


By DBA_WaterBin
2013-09-28
Good Luck