Mysql table lock demo statement _ MySQL

Mysql table lock statement bitsCN.com
The mysql table lock statement first looks at the table structure, engine innodb + ---- + ---------- + ------ + | id | name | seat | + ---- + ---------- + ------ + | 1 | administrator | 98 | 2 | maintenance personnel | 98 | 3 | director | 97 | 4 | class teacher | 96 | + ---- + ---------- + ------ + id is the primary key, no other indexes. First, check the table lock. run SQL: set autocommit = 0; select * from role where seat = 98 for update. Note that set autocommit = 0 is essential, because if the data is submitted soon, the lock will be automatically released. Run another SQL query: select * from role where id = 1; the result is as follows: + ---- + -------- + ------ + | id | name | seat | + ---- + -------- + ------ + | 1 | administrator | 98 | + ---- + -------- + ------ + 1 row in set (0.00 sec) this is because the select operation has nothing to do with locking. Then let's look at the write operation: update role set seat = 99 where id = 3; we can see that after a long wait (after the default execution time of mysql ), the following result is displayed: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. now we can see the Lock record: send SQL: SHOW PROCESSLIST; + ---- + ------ + ----------------- + --------- + ------ + ------- + -------------- ---- + | Id | User | Host | db | Command | Time | State | Info | + ---- + ------ + ----------------- + --------- + ------ + ------- + -------------- ---- + | 7 | root | localhost: 50903 | mybatis | Sleep | 1403 | NULL | 16 | root | localhost: 51326 | mybatis | Query | 0 | NULL | show processl ist | + ---- + ------ + ----------------- + --------- + ------ + ------- + -------------- ---- + 6th column Time 1403 indicates the lock Time, unit: seconds. Conclusion: When the column behind the where condition is not an index, adding for update will lock the entire table, so that no subsequent record can be read or written. For example, when the id of this article is 3, seat = 97. it is not the query condition 98. it is still locked and cannot be updated. After the lock is killed, the write operation can continue: SQL: kill 7; the effect will not be demonstrated here. BitsCN.com