mysql for lock_mysql select for update, row lock, table lock

mysql select for update, row lock, table lock

一些前置条件

打开连个控制台模拟

mysql> show create table table_test;

| Table | Create Table

| table_test | CREATE TABLE `table_test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`id_index` int(11) DEFAULT '0',

`id_no_index` int(11) DEFAULT '0',

`common` int(11) DEFAULT '0',

PRIMARY KEY (`id`),

KEY `index_id_index` (`id_index`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |

mysql> select * from table_test;

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

| id | id_index | id_no_index | common |

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

| 1 | 0 | 0 | 1 |

| 2 | 0 | 0 | 2 |

| 3 | 0 | 0 | 3 |

| 4 | 0 | 0 | 4 |

| 5 | 0 | 0 | 5 |

| 6 | 1 | 1 | 5 |

| 7 | 1 | 1 | 6 |

| 8 | 1 | 1 | 7 |

| 9 | 1 | 1 | 8 |

| 10 | 1 | 1 | 9 |

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

10 rows in set (0.00 sec)

mysql> set autocommit = 0;

索引字段

session1

mysql> begin;

mysql> select * from table_test where id_index=1 for update;

session2

mysql> select * from table_test where id_index=0 for update;

正常执行

mysql> select * from table_test where id_index=1 for update;

阻塞，当session1执行 "commit" or "rollback" 释放锁，session2继续执行

结论：有索引的字段用row lock

无索引字段

session1

mysql> begin;

mysql> select * from table_test where id_no_index=1 for update;

session2

select * from table_test where id_no_index=0 for update;

此时会阻塞，当session1执行 "commit" or "rollback" 释放锁，session2继续执行

结论：无索引的字段用table lock

范围条件

mysql> select * from table_test where id>8 for update;

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

| id | id_index | id_no_index | common |

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

| 9 | 1 | 1 | 8 |

| 10 | 1 | 1 | 9 |

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

mysql 会将这两条记录加锁

也会对大于10的加锁，这种锁机制就是所谓的间隙锁(Next-Key 锁)

session1

mysql> begin;

mysql> select * from table_test where id_index>8 for update;

session2

mysql> select * from table_test where id_index<8 for update;

正常执行

mysql> insert into table_test(id,id_index,id_no_index,common) values (11,1,1,10);

此时会阻塞，当session1执行 "commit" or "rollback" 释放锁，session2继续执行

InnoDB行锁和表锁的分析