Understanding mysql locks (2) Table-level locks

Understanding mysql locks (2) Table-level locks the MyISAM storage engine uses a locking mechanism completely implemented by the table-level locks 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 on 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, and then 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 check the table_locks_waited and table_locks_immediate status variables to analyze the table lock contention on the system: [SQL] mysql> show status like 'table % '; + parameters + ------- + | Variable_name | Value | + ----------------------- + ------- + | Table_locks_immediate | 18 | Table_locks_waited | 0 | + --------------------- + ------- + in MySQL, the two locks are maintained by 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) • Pe Nding 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. in addition to READ_HIGH_PRIORITY, write WRITE locks in 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. the 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 on www.2cto.com: [SQL] session A is displayed to add read locks to table t1. mysql> lock table t1 read; Query OK, 0 rows affected (0.00 sec: mysql> select * from t1; + ------ + | I | + ------ + | 1 | 2 | 5 | + ------ + www.2cto.com 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 A mysql> update t1 set I = 3 limit 1; ERROR 1099 (HY000 ): table 'T1 'was locked with a READ lock and can't be updated session B mysql> update t1 set I = 3 limit 1; directly blocked session A unlocks mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) www.2cto.com session B releases the locked resource in session A, and session B obtains the resource, 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 obtaining the read lock; Query OK, 0 rows affected (0.00 sec) insert of other sessions in session B is not blocked mysql> insert into t1 values (6); Query OK, 1 row affected (0.00 sec) www.2cto.com 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)
You can continue reading your own sessions: mysql> select * from t1; + ------ + | I | + ------ + | 3 | 2 | 5 | 6 | + ------ + www.2cto.com 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 | + ------ + www.2cto.com 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)