MySQL locks every day

I. MySQL locks the MyISAM and MEMORY engines to implement row-table locks, the BDB storage engine to implement page-level locks, and the InnDB storage engine to implement row-level locks. Granularity: from large to small (MySQL only supports table-level locks, row locks need to be completed by the storage engine; all engines I. Locks

MySQL implements row-table locks for the MyISAM and MEMORY engines, implements page-level locks for the BDB storage engine, and RoW-level locks for the InnDB storage engine tables.

Granularity: from large to small (MySQL only supports table-level locks, row locks need to be completed by the storage engine; all engines have their own lock policies)

Table lock:Lock the entire table, with low overhead and fast locking; no deadlock; large lock granularity, the highest probability of lock conflict, and the lowest concurrency.

Page Lock: Lock a data block (data page ). The overhead and lock time are between the table lock row locks, and deadlocks may occur. the lock granularity is between the table lock and the row lock, and the concurrency is normal.

Row lock:Lock a row. High overhead, slow locking; deadlock may occur; the lock granularity is between the table lock and the row lock, and the concurrency is normal.

Generally, table locks are suitable for queries, and only a small number of applications that love your data update based on index conditions, such as web applications.

Row-level locks are suitable for a large number of applications that concurrently update a small amount of different data based on index conditions and have concurrent query capabilities, such as some online transaction systems (OLTP)

1. lock statement

Manual locking: lock tables Table name [read | write]

Read-only lock for table t9

Mysql> lock table t9 read;

Query OK, 0 rows affected (0.00 sec)

View Lock

Mysql> show global status like "table_locks % ";

+ ----------------------- + ------- +

| Variable_name | Value |

+ ----------------------- + ------- +

| Table_locks_immediate | 122 | the table is locked, but is released immediately after the table is locked.

| Table_locks_waited | 0 | a table lock occurs and a lock wait occurs.

+ ----------------------- + ------- +

2 rows in set (0.00 sec)

Write a lock to a t9 table

Mysql> lock table t9 write;

Query OK, 0 rows affected (0.00 sec)

Unlock table

Mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

2. MyISAM table lock

The MyISAM storage engine only supports table locks.

View the requisition of table-level locks

Mysql> show status like 'table % ';

+ ---------------------------- + ------- +

| Variable_name | Value |

+ ---------------------------- + ------- +

| Table_locks_immediate | 1258 |

| Table_locks_waited | 0 | Lock wait

| Table_open_cache_hits | 99 |

| Table_open_cache_misses | 1 |

| Table_open_cache_overflows | 0 |

+ ---------------------------- + ------- +

5 rows in set (0.00 sec)

If the value of TABLE_LOCKS_WAITED is relatively high, it indicates a serious competition.

Mysql> lock table emp read local

Query OK, 0 rows affected (0.00 sec)

If local is added to locktables, the function is to allow other users to insert records at the end of the MyISAM table when the concurrent insert conditions are met.

Mysql> select * from emp1;

ERROR 1100 (HY000): Table 'emp1' was notlocked with LOCK TABLES

When using locktables to lock a table, you must obtain all the locks related to the table, and MySQL does not support lock upgrade. That is to say, if it is a read lock, you can only perform the query operation, but not the update operation.

Mysql> update emp set store_id = 30 where id = 25;

ERROR 1099 (HY000): Table 'emp' was lockedwith a READ lock and can't be updated

And cannot be accessed through the alias, so you need to lock the alias.

Mysql> select a. id from emp;

ERROR 1100 (HY000): Table 'A' was notlocked with LOCK TABLES

Concurrent inserts

The read and write operations of MyISAM tables are sequential. However, under certain conditions, MyISAM tables also support concurrent queries and insert operations.

The MyISAM storage engine has a system variable concurrent_insert, which is used to control its concurrent insertion behavior. the values are 0, 1, and 2, respectively.

When the value is 0:Concurrent inserts are not allowed.

When the value is 1:If the MyISAM table has no holes, MyISAM allows a process to read the table, and another process to insert records from the end of the table. (Default) records can be inserted concurrently at the end of the MyISAM table regardless of whether there are holes in the table.

MyISAM scheduling lock

The read/write locks of the MyISAM storage engine are mutually exclusive. if a process requests a read lock for a MyISAM table and another process requests a write lock for the same table, MySQL will first obtain the lock for the write process. Not only that, even if the read request first goes to the lock wait queue, after the write request arrives, the write lock is inserted before the read lock request. This is because MySQL regards write requests as more important than general read requests. Therefore, a large number of update operations may make it difficult for query operations to obtain read locks, and thus may be blocked forever. This situation is terrible!

Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

By specifying the startup parameter low-priority-updates, the MyISAM engine gives the read request by default, with the right of wired access.

Run SETLOW_PRIORITY_UPDATES = 1 to lower the priority of the update request sent by the link.

You can specify the LOW_PRIORITY attribute of an insert, update, or delete statement to reduce the priority of the statement.

You can also set a proper value for the system parameter max_write_lock_count. when the read lock of a table reaches this value, MySQL will temporarily lower the write request priority, give the read process a chance to get the lock.

It is emphasized that a query operation that requires a long period of operation will also starve the write process "! Therefore, try to avoid long query operations.