InnoDB锁问题

作者: sunpy | 来源:发表于2019-01-22 22:59 被阅读40次

InnoDB锁问题
MySQL技术内幕InnoDB存储引擎阅读相关笔记-锁
校招面试题mysql锁总结
如何解决秒杀场景下的超卖问题
阿里P8大佬带你全面了解—MySQL锁：03.InnoDB行锁
[译]Innodb中的锁
Mysql中的锁
InnoDB行锁
文章总结（6）—数据库
MySQL:Innodb中数字的存储方式

介绍

InnoDB本身遵守DML操作的ACID模型，所以InnoDB本身支持事务。并且InnoDB也支持多种粒度的锁，分为行级锁和表级锁。

InnoDB锁粒度

InnoDB在默认情况下，行锁和表锁都是自动获得。
行级锁：
① 共享锁（S锁）：允许事务读一行数据。
② 排他锁（X锁）：允许事务删除或者更新一行数据。
表级锁（意向锁）：
① 意向共享锁（IS Lock）：允许事务获得一个表中几行的共享锁。
② 意向排他锁（IX Lock）：允许事务获得一个表中几行的排他锁。
我的理解：读读共享，遇见写就互斥。

InnoDB显示锁

因为InnoDB本身支持事务，事务执行过程随时可以锁定，而锁在隐式情况下，随时可以执行锁定操作，当锁执行COMMIT或者ROLLBACK回滚时，锁才会释放，这些过程叫做Mysql的隐式锁。Mysql也提供了显示指定锁的方式。
1. 表信息：

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 显示指定表锁读：

mysql> lock table t read;
Query OK, 0 rows affected (0.00 sec)
## session1
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
##session2
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
##session1
mysql> insert into t select 4;
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
##session2
mysql> insert into t select 4;
##session1
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)

说明：
当获取表锁read锁定，那么当前的session和其他的session都可以读取数据。当前的session不可以写操作，其他session写操作将等待。当前session释放表锁，其他处于阻塞的session的写操作，将自动执行（不一定成功！譬如主键冲突了，其他的session写操作将执行失败！）。
3. 显示指定表锁写：

##session1
mysql> lock table t write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
mysql> insert into t select 5;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
##session2
mysql> select * from t;
##session1
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

说明：
当获取表锁write时，当前的session将可以进行读操作和写操作，其他session都将处于阻塞状态（不可以读也不可以写），其他session只有等当前表锁被释放才可以读写。
4. 显示S锁：

##session1
mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t lock in share mode;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
##session2
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
##session1
mysql> insert into t select 3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
##session2
mysql> insert into t select 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
##session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

说明：
当前session1获得共享锁，其他session也可以继续添加共享锁查询记录。但是其他session想要新增记录，将会处于阻塞状态。超过了指定时间(50s)将报错。虽然session1新增了记录，但是必须要提交事务，其他session才可见。所以select * from t lock in share mode;一般配合事务commit使用。
查询因锁等待时间：

mysql> show variables like '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

5. 显示X锁：

##session1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
##session2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
##session1
mysql> select * from t for update;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
##session2处于阻塞状态
mysql> select * from t lock in share mode;
##session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
##session2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

说明：
当前事务添加了排他锁后，其他事务想要添加锁都将被阻塞。

InnoDB锁实现

Record Lock：单个行记录上加锁。锁定的是索引项，如果没有设置索引，将使用隐式的主键锁定。
Gap Lock：间隙锁，不包含本身记录的锁定范围。
Next-Key Lock：Record Lock+Gap Lock。锁定范围且包含自身记录。
InnoDB默认存储引擎是REPEATABLE READ模式，Next-Key Lock算法是默认的行记录算法。

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

例子：像上面的表中插入记录1、2、3、6、7、8。

mysql> select * from t;
Empty set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select 3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select 6;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Next-key Lock

##session1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id < 6 lock in share mode;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
##session2
mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
4 rows in set (0.00 sec)
mysql> insert into t select 7;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
##session2在下面语句执行后发生阻塞
mysql> insert into t select 5;

说明：Next-Key Lock算法锁定的范围是(-∞, 6)。因为7不在此范围，所以顺利插入，而5在锁定范围中，所以将处于阻塞状态。
Record Lock

##session1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 lock in share mode;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
##session2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 8;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
##session2在下面语句执行后发生阻塞
mysql> update t set id =1 where id = 1;

死锁

死锁一般出现在程序并发执行时，一个事务获取表A的X锁，另一个事务获取表B的X锁；然后获取A表的事务再获取表B阻塞，获取B表的事务再获取表A阻塞；两个事务都处于饥饿状态，就是资源相互等待的情况。
案例：

##session1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t lock in share mode;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
##session2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t lock in share mode;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
##session1
mysql> insert into t select 5;
Query OK, 1 row affected (12.31 sec)
Records: 1  Duplicates: 0  Warnings: 0
##session2
mysql> insert into t select 6;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

解决办法：

innodb_lock_wait_timeout

说明：事务等待行锁定的时间，默认值为50秒。发生锁定等待超时，将回滚当前语句。

innodb_rollback_on_timeout

说明：InnoDB默认情况下只会回滚事务超时的最后一个语句。指定这个参数将回滚整个事务。
解决方法2：
我们直接手动将整个事务进行回滚。

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

InnoDB锁问题
介绍 InnoDB本身遵守DML操作的ACID模型，所以InnoDB本身支持事务。并且InnoDB也支持多种粒度的...
MySQL技术内幕InnoDB存储引擎阅读相关笔记-锁
一、InnoDB锁对于MyISAM引擎来说，其锁是表锁。InnoDB引擎提供行锁。 1、InnoDB行锁 1）、...
校招面试题mysql锁总结
目录锁定义锁分类读锁和写锁表锁和行锁 InnoDB共享锁和排他锁 InnoDB意向锁和排他锁 InnoDB...
如何解决秒杀场景下的超卖问题
1、利用排他锁解决超卖问题 MySQL InnoDB存储引擎支持事务和行锁。如果不手动开启事务的话，InnoDB会...
阿里P8大佬带你全面了解—MySQL锁：03.InnoDB行锁
目录 InnoDB 行锁锁排查可以用的视图和数据字典InnoDB 行锁兼容性 InnoDB行锁之共享锁共享锁：查...
[译]Innodb中的锁
原文地址InnoDB Locking 本章节描述了InnoDB中使用的锁. 共享锁和排它锁意向锁记录锁间隙锁...
Mysql中的锁
锁的类型有哪些基于锁的属性分类：共享锁、排他锁。基于锁的粒度分类：行级锁(INNODB)、表级锁(INNODB...
InnoDB行锁
十、 InnoDB行锁0、查看方式1、共享锁2、排他锁3、意向锁4、行锁案列5、InnoDB锁实现 0、 ...
文章总结（6）—数据库
InnoDB和MyISAM存储引擎的区别 InnoDB使用的是行锁，MyISAM使用的是表锁； InnoDB支持事...
MySQL:Innodb中数字的存储方式
一、问题来源如果我们查看show egnine innodb查看锁记录的时候往往会看到Innodb的数字使用类似...