MySQL中的锁实例

表结构：

id：自增主键，a：无索引，b：普通索引

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(4) NOT NULL DEFAULT '0',
  `b` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `b` (`b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

等值查询加锁

1. 主键索引字段加锁查询

窗口A:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id = 1 for update;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 2 | 1 |
+----+---+---+
1 row in set (0.00 sec)

窗口B:
mysql> update test set a = 11 where id = 1;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 11 where id = 2;（正常执行）

证明根据主键索引字段加锁查询，会锁住该条数据（记录锁）。

2. 普通索引字段加锁查询

窗口A:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where b = 1 for update;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  3 | 3 | 1 |
|  5 | 5 | 1 |
+----+---+---+
3 rows in set (0.00 sec)

窗口B:
mysql> update test set a = 11 where b = 1;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 11 where id = 3;（被阻塞，等待窗口A中commit后才执行）
mysql> insert into test(a,b) value(1,1);（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 11 where id = 2;（正常执行）
mysql> insert into test(a,b) value(1,2);（正常执行）

证明根据普通索引字段加锁查询，会锁住符合该条件的所有记录包括不存在的，因此得出锁的是索引，而非数据本身。

3. 无索引字段加锁查询

窗口A:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where a = 1 for update;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
+----+---+---+
1 row in set (0.00 sec)

窗口B:
mysql> update test set a = 22 where id = 1;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 22 where a = 1;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 22 where b = 1;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 22 where a = 2;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 22 where b = 2;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 22 where id = 2;（被阻塞，等待窗口A中commit后才执行）
mysql> insert into test(a,b) value(22,22);（被阻塞，等待窗口A中commit后才执行）

证明根据无索引字段加锁查询，会锁住整张表，表现形式类似于表锁。

范围查询加锁

窗口A:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id > 3 and id < 6 for update;
+----+---+---+
| id | a | b |
+----+---+---+
|  5 | 5 | 5 |
+----+---+---+
1 row in set (0.00 sec)

窗口B:
mysql> insert into test(id,a,b) value(4,4,4);（被阻塞，等待窗口A中commit后才执行）
mysql> insert into test(id,a,b) value(6,6,6);（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 77 where id = 7;（被阻塞，等待窗口A中commit后才执行）
mysql> update test set a = 33 where id = 3;（正常执行）
mysql> update test set a = 22 where id=8;（正常执行）

证明锁的范围为**(3,7]，既锁住了记录本身（记录锁），也锁住了索引之间的间隙（间隙锁），而且把索引后相邻的两个记录也锁住了（临键锁）**。

通过无索引字段范围查询时，同样是锁住整张表。