Understanding MySQL locks (2) Table-level locks

The locking mechanism used by the MyISAM storage engine is fully implemented by the table-level locking mechanism provided by MySQL.

MySQL has two types of table-level locks: Write locks and read locks.

For write locks, MySQL uses the following table locking method:
* If there is no lock in the table, put a write lock on it.
* Otherwise, the lock request is placed in the write lock queue.
For read locks, MySQL uses the following table locking method:
* If there is no write lock on the table, place a read lock on it.
* Otherwise, put the lock request in the read lock queue.

When a lock is released, the lock can be obtained by the threads in the write lock queue, followed by the threads in the read lock queue. This means that if you have many updates on a table, the SELECT statement will wait until there are no more updates.
You can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables:

mysql> show status like 'table%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Table_locks_immediate | 18    || Table_locks_waited    | 0     |+-----------------------+-------+

If the value of table_locks_waited is relatively high, it indicates a serious table-Level Lock contention.

 

In MySQL, the two locks are maintained through four Queues: The two stores the Read and Write lock information currently being locked, and the other two stores the read/write lock information in the waiting state, as follows:

• Current read-lock Queue (lock-> Read)
• Pending read-lock Queue (lock-> read_wait)
• Current write-lock Queue (lock-> write)
• Pending write-lock Queue (lock-> write_wait)

When the client requests a write lock, MySQL first checks whether the same resource has been locked in the current write-lock queue to the information. If the current write-lock queue does not exist, then check the pending write-lock queue. If the pending write-lock queue is found, you also need to enter the waiting queue; otherwise, if the pending write-lock queue cannot be found, the current read-lock queue will be detected. If a lock exists, the pending write-lock queue will also be entered. If the current write-lock queue is detected to have a write lock that locks the same resource at the beginning, it will directly enter the pending write-lock queue.

The priority rules of write lock requests in read requests and write wait queues are determined by the following rules:

1. Apart from read_high_priority's read lock, the write lock in the pending write-lock queue can block all other read locks;
2. read_high_priority read lock requests can block write locks in all pending write-lock queue;
3. In addition to write lock, any write lock in the pending write-lock queue has a lower priority than read lock.

Table-level locks are superior to row-level locks in the following situations:
1. Many operations are read tables.
2. Read and update indexes with strict conditions. When updating or deleting indexes, you can use a separate index to read them:
3. Update tbl_name set column = value where unique_key_col = key_value;
4. Delete from tbl_name where unique_key_col = key_value;
5. Select and insert statements are executed concurrently, but there are only a few update and delete statements.
6. Many scan tables and group by operations on the entire table, but there is no write table.

Test:

Session A adds read locks to table T1 to MySQL> lock table T1 read; query OK, 0 rows affected (0.00 Sec). Its read operations are not blocked: mysql> select * from T1; + ------ + | I | + ------ + | 1 | 2 | 5 | + ------ + 3 rows in SET (0.00 SE) the read operations of other processes in session B are not blocked either: mysql> select * from T1; + ------ + | I | + ------ + | 1 | 2 | 5 | + ------ + 3 rows in SET (0.00 Sec) session amysql> Update T1 set I = 3 limit 1; error 1099 (hy000 ): table 'T1 'was locked with a read lock and can't be updatedsession bmysql> Update T1 set I = 3 limit 1; directly blocked session a unlocks mysql> unlock tables; query OK, 0 rows affected (0.00 Sec) Session B releases the locked resource in session A, and session B obtains the resource. The update succeeds. mysql> Update T1 set I = 3 limit 1; query OK, 1 row affected (0.00 Sec) rows matched: 1 changed: 1 Warnings: 0 session a adds the local option mysql> lock table T1 read local when getting the read lock; query OK, 0 rows affected (0.00 Sec) Session B insert of other sessions is not blocked mysql> insert into T1 values (6); query OK, 1 row affected (0.00 Sec) However, update of other sessions is blocked mysql> Update T1 set I = 3 limit 1; directly blocked bird session a adds the write lock mysql> unlock tables; query OK, 0 rows affected (0.00 Sec) mysql> lock table T1 write; query OK, 0 rows affected (0.00 Sec) Your session can continue to read: mysql> select * from T1; + ------ + | I | + ------ + | 3 | 2 | 5 | 6 | + ------ + 4 rows in SET (0.00 Sec) Session B: read of other sessions is blocked mysql> select * from T1; directly blocked bird session a releases locked resources mysql> unlock tables; query OK, 0 rows affected (0.00 Sec) session B other sessions can obtain resources from mysql> select * from T1; + ------ + | I | + ------ + | 3 | 2 | 5 | 6 | + ------ + 4 rows in SET (0.00 Sec) session A acquires write_allow_read-type write locks through DDL mysql> alter table T1 add constraint t1_pk primary key (I); query OK, 4 rows affected (0.07 Sec) records: 4 duplicates: 0 Warnings: 0 session B the reading of other sessions is not blocked mysql> select * from T1; + --- + | I | + --- + | 2 | 3 | 5 | 6 | + --- + 4 rows in SET (0.00 Sec)