数据库使用锁是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。

当一个事务获取了行r的共享锁，那么另外一个事务也可以立即获取行r的共享锁，因为读取并未改变行r的数据，这种情况就是锁兼容。但是如果有事务想获得行r的排它锁，则它必须等待事务释放行r上的共享锁—这种情况就是锁不兼容，二者兼容性如下表格所示：

排它锁和共享锁的兼容性

X 排它锁

S 共享锁

X 排它锁

冲突

冲突

S 共享锁

冲突

兼容

InnoDB锁的扩展，由于InnoDB支持的是行级别锁，所以意向锁其实不太会阻塞全表scan以下的任何请求。共享锁、排它锁、意向共享锁、意向排它锁相互之间都是有兼容/互斥关系的，它们之间的兼容关系如下表：

X 排它锁

S 共享锁

IX 意向排它锁

IS 意向共享锁

X 排它锁

冲突

冲突

冲突

冲突

S 共享锁

冲突

兼容

冲突

兼容

IX 意向排它锁

冲突

冲突

兼容

兼容

IS 意向共享锁

冲突

兼容

兼容

兼容

InnoDB有三种行锁的算法：

1，Record Lock：单个行记录上的锁。

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。

3，Next-Key Lock：1+2（Gap Lock+Record Lock），锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

Record Lock 总是会去锁住索引记录，如果innodb存储引擎表在建立的时候没有设置任何一个索引，而且查询的时候没有使用到索引，那么这时就会导致表锁。

下面做下测试，这样能让大家更好理解各种锁算法：

测试一：

在session A操作：

mysql> create table t1 ( id int primary key);

Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(1),(3),(5),(8),(11);

Query OK, 5 rows affected (0.01 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1;

+----+

| id |

+----+

|  1 |

|  3 |

|  5 |

|  8 |

| 11 |

+----+

5 rows in set (0.00 sec)

查看当前隔离级别：

mysql> show variables like '%iso%';

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

| Variable_name | Value           |

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

| tx_isolation  | REPEATABLE-READ |

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

1 row in set (0.00 sec)

在A session执行相关测试操作

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=8 for update;

+----+

| id |

+----+

|  8 |

+----+

1 row in set (0.03 sec)

在B session操作：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 select 4;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 6;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 7;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 9;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

分析：InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，是由于id是主键且唯一，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。所以在B会话中，插入4、6、7、9的值不会阻塞，而且成功插入，锁住记录本身，从而提高并发性。

测试2：

在A seesion 操作:

mysql> create table t2 ( id int, vid int, primary key (id), key(vid));

Query OK, 0 rows affected (0.06 sec)

mysql> insert into t2 select 1,1;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 3,1;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 5,3;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 7,6;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql>  insert into t2 select 10,8;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

在t2中，vid列是辅助索引，还是在A会话执行以下操作：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2 where vid=3 for update;

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

| id | vid  |

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

|  5 |    3 |

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

1 row in set (0.01 sec)

在B seesion操作（为了加快测试，先设置下）：

mysql> set global innodb_lock_wait_timeout=3;

Query OK, 0 rows affected (0.00 sec)

mysql> set global lock_wait_timeout=3;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%lock_wait_timeout%";

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

| Variable_name            | Value |

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

| innodb_lock_wait_timeout | 3     |

| lock_wait_timeout        | 3     |

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

2 rows in set (0.00 sec)
mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2 where id=5 lock in share mode;

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

mysql> insert into t2 select 4,2;

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

mysql> insert into t2 select 6,5;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 select 6,6;

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

分析：这时的SQL语句通过索引列vid进行查询，因此将会使用传统的Next-Key Locking技术加锁，并且由于有两个索引，需要分别进行锁定，对于聚集索引，仅对列id等于5的索引加上Record Lock，即只锁住5这个记录，但对于辅助索引列,则会用上Next-Key Lock算法，上面索引有1，1，3，6，8，被Next-Key Locking的区间为：（-∞,1]，（1，1]，（1，3]，（3，6]，（6，8]，（8，+∞），因此它会锁住范围是（1，3],特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，因为还会锁住范围（3,6]。辅助索引列实际会锁住的值有2,3,4,5,6。
在B seesion操作：

mysql>insert into t2 select 2,1;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 8,6;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 6,7;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

可以看到，没在锁定范围的，可以正常插入。

测试3：

在session A操作：

mysql>  create table t3 ( id int, name char(20));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 select 1,'aa';

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t3 select 2,'bb';

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t3 select 3,'cc';

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t3\G

*************************** 1. row ***************************

       Table: t3

Create Table: CREATE TABLE `t3` (

  `id` int(11) DEFAULT NULL,

  `name` char(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.03 sec)
mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t3 set name='BB' where id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

在B session操作：

mysql> begin;

Query OK, 0 rows affected (0.03 sec)

mysql> update t3 set name='CC' where id=3;

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

mysql> update t3 set name='AA' where id=1;

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

分析：可以看出，只有通过索引检索数据，innodb才会采用行锁，否则，innodb将会使用表锁。生产环境一定要注意。

测试四:

在A seesion操作：

mysql> create table t4 ( id int , uid int, unique key(id,uid))engine=innodb;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t4 select 1,2;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t4 select 1,3;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t4 select 1,5;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t4 select 1,8;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

表t4创建了一个唯一索引。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t4 where uid=5 for update;

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

| id   | uid  |

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

|    1 |    5 |

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

1 row in set (0.00 sec)

在B session操作：

mysql> begin;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t4 select 1,1;

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

mysql> insert into t4 select 1,4;

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

mysql> insert into t4 select 1,7;

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

mysql>

解释：可见依然采用的是Next-Key Lock进行锁定的

总结：

1、对于Next-key Lock算法，锁定的是范围，包含记录本身，对辅助索引下一个键值加上Gap Lock

2、对于唯一索引，其加上的是Record Lock，仅锁住记录本身。但也有特别情况，那就是唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么加锁的情况依然是Next-key Lock。

3、innodb存储引擎是通过给索引上的索引项加锁来实现，这意味着：只有通过索引条件检索数据，innodb才会使用行锁，否则，innodb将使用表锁

参考资料

大牛何登成的博客：http://hedengcheng.com/?p=771

<<MySQL技术内幕--InnoDB存储引擎第2版>>

最新文章

  1. socket.io，命名空间
  2. PHP框架Swoole的一个定时器Timer特性
  3. Codeforces Round #266 (Div.2) B Wonder Room --枚举
  4. SQL Server练习
  5. Collection+JSON 文档
  6. java多态/重载方法——一个疑难代码引发的讨论
  7. keil中使用_at_绝对地址定位
  8. python正则表达式实例
  9. 我知道你不知道的负Margin
  10. Javscript的垃圾回收
  11. wav音频文件格式解析【个人笔记】(自用)
  12. 单片机成长之路（51基础篇） - 004 STC89C52MCU 软件实现系统复位
  13. [hgoi#2019/2/16t3]psolve
  14. 撩课-Web大前端每天5道面试题-Day30
  15. sourceTree跳过注册
  16. 分析占用了大量CPU处理时间的java进程中的进程
  17. Spring 中面向AOP之一系列做法
  18. _x和__all__（有所理解即可）
  19. form表单回车Enter不直接提交，类似tab切换
  20. ChromeExtension 写一个extension

热门文章

  1. java自定义注解学习（三）_注解解析及应用
  2. JAVA类变量（静态变量）
  3. R vs Python，数据分析中谁与争锋？
  4. HGOI 20181028 题解
  5. 洛谷 P1073 最优贸易 解题报告
  6. 个推基于 Apache Pulsar 的优先级队列方案
  7. linux c 编程 ------ 获取时间，计算程序执行时间
  8. node的path.join 和 path.resolve的区别
  9. yolo详解
  10. 鸟哥的Linux私房菜——第十五章：正规表示法