innodb锁的基本介绍
mysql的innodb引擎,支持行锁和表锁,但默认情况下是采用行锁。
innodb实现了两种类型的行锁:共享锁(S)、排它锁(X)
另外,为了允许行锁和表锁共存,innodb还有两种内部使用的意向锁:意向共享锁(IS)、意向排他锁(IX),这两种意向锁都是表锁。意向锁是innodb自动加的,用户不需要干预。
对于update、delete、insert语句,innodb会自动给涉及数据家排它锁;对于普通的select语句,innodb不会加锁;事务可以通过以下方式给记录集加锁。
共享锁:SELECT * FROM table WHERE …LOCK IN SHARE MODE
排它锁:SELECT * FROM table WHERE…FOR UPDATE
innodb行锁实现方式
innodb行锁是通过给索引上的索引项加锁来实现的,如果没有索引,innodb将通过隐藏的聚簇索引来对记录加锁,innodb行锁分为3种情形
Record lock:对索引项加锁
Gap lock:对索引项之间的范围加锁
Next-key lock:前两种的组合
innodb这种行锁实现特点意味着:如果不通过索引条件检索数据,那么innodb将对表中的所有记录加锁。
另外需要注意的是,因为是针对索引加的锁,所以可能出现虽然访问的是不同行的数据,但由于使用的是相同的索引键而出现锁冲突。
例如:
id为普通索引
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 1 | b |
+------+------+
2 rows in set (0.00 sec)
事务1:
mysql> select * from test where id=1 and name='a' for update;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)
事务2:
mysql> select * from test where id=1 and name='b' for update;
等待
再来说一下Next-key lock,当我们用范围条件而不是等于条件检索数据,并且请求共享或者排他锁时,innodb就会给符合条件呢的已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做间隙(GAP),innodb也会对这个间隙加锁。
例如:
其中id为主键
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 3 | b |
| 5 | b |
+------+------+
5 rows in set (0.00 sec)
事务1
mysql> select * from test where id between 3 and 5 for update;
+------+------+
| id | name |
+------+------+
| 3 | b |
| 5 | b |
+------+------+
2 rows in set (0.00 sec)
事务2
mysql> insert into test values(4,'d');
等待
innodb会对不存在的值,也就是间隙进行上锁。
还要注意的是,innodb除了对主键或者唯一键的范围查找外,对普通索引的等于查找也会产生Next-key lock
例如:
id为普通索引
mysql> select * from ceshi;
+----+------+
| id | id1 |
+----+------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
+----+------+
3 rows in set (0.00 sec)
事务1:
mysql> select * from ceshi where id=3;
+----+------+
| id | id1 |
+----+------+
| 3 | 3 |
+----+------+
1 row in set (0.00 sec)
事务2:
insert into ceshi values(0,4);
Query OK, 1 row affected (0.00 sec)
insert into ceshi values(1,4);
等待
insert into ceshi values(2,4);
等待
insert into ceshi values(4,4);
等待
insert into ceshi values(3,4);
等待
insert into ceshi values(5,4);
Query OK, 1 row affected (0.00 sec)
可以看到,锁定的范围是[1,5) 锁上不锁下
恢复和复制,对锁机制的影响
在备份恢复和主从复制的场景中,我们会使用到binlog,为了保证数据的一致性,在下面这种情况时也会发生锁冲突:
mysql> select * from t1;
+----+------+
| id | id1 |
+----+------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
+----+------+
3 rows in set (0.00 sec)
事务1:
mysql> insert into t2 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
事务2:
update t1 set id1=10 where id=3;
等待
这里加锁是为了保证使用binlog恢复出来的时候数据的一致性(如果事务2先提交,事务1再提交就会产生不一致)
进行一下演示:
把innodb_locks_unsafe_for_binlog参数调为1,重复上面的操作,这时,事务2不会发生所冲突。
事务2:
mysql> commit; //先对事务2进行提交
Query OK, 0 rows affected (0.01 sec)
事务1:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
select * from t2; //查看t2表的内容,没有问题,是事务2修改之前的数据
+----+------+
| id | id1 |
+----+------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from t1; //查看t1表的数据,也没有问题,已经修改过
+----+------+
| id | id1 |
+----+------+
| 1 | 1 |
| 3 | 10 |
| 5 | 5 |
+----+------+
3 rows in set (0.00 sec)
但是我们来看一下binlog的内容
可以看出来,binlog中先记录了update操作,然后才是insert操作,从而会导致数据的不一致性(可以通过改成row模式解决)
参考:《深入浅出MySQL》