Mysql table lock demo statement

Welcome to the Linux community forum and interact with 2 million technical staff to access the table structure first, engine innodb + ---- + ---------- + ------ + | id | name | seat | + ---- + ---------- + ------ + | 1 | administrator | 98 | 2 | maintenance personnel | 98 | 3 | Director | 97 | 4 | class teacher | 96 | + ---- + ---

Welcome to the Linux community forum and interact with 2 million technicians> go to the first page to view the table structure, engine innodb + ---- + ---------- + ------ + | id | name | seat | + ---- + ---------- + ------ + | 1 | administrator | 98 | 2 | maintenance personnel | 98 | 3 | Director | 97 | 4 | class teacher | 96 | + ---- + ---

Welcome to the Linux community forum and interact with 2 million technicians>

First look 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 and there are no other indexes.

First look at the table lock situation

SQL statement:

Set autocommit = 0;

Select * from role where seat = 98 for update;

Note that set autocommit = 0 is essential because the lock will be automatically released if the data is submitted soon.

Run another SQL query: select * from role where id = 1;

Display result:

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

| 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;

As you can see, after a long wait (after the default execution time of mysql), the following results are displayed:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Now let's look at the lock record:

SQL statement: 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 |

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

---- +

The Time 6th in the 1403 column indicates the lock Time, in 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.

Kill the lock and the write operation will continue:

SQL statement: kill 7;

The effect will not be demonstrated here.