mysql innodb 解锁_mysql数据库innodb行锁讲解（代码实例）

Innodb 行锁

行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别

行锁

锁类型

InnoDB表实现了以下两种形式的行锁。

(1)共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

(2)排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁的共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

(a)、意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前，必须先取得该表的意向共享锁(IS)。

(b)、意向排它锁(IX)：事务打算给数据行加行排它锁，事务在给一个数据行加排它锁前，必须先取得该表的意向排它锁(IX)。

锁机制

如果一个事务请求的锁模式和当前的锁兼容，InnoDB就将锁给予该事务；反之，如果两者不兼容，该事务必须等待锁释放。意向锁是InnoDB自己加的，不需用户干预。

锁使用

自动使用锁

UPDATE、DELETE、INSERT语句，Innodb会自动给涉及的数据集加排他锁(X)；

普通SELECT语句，Innodb不会加任何锁。

手动使用

(a)共享锁(S)：

SELECT * FROM table_name WHERE .... LOCK IN SHARE MODE.

主要用在需要数据依存关系时来确认某行记录是否存在，并确保没人对这行记录进行UPDATE 或者 DELETE 操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁.应该使用SELECT .... FOR UPDATE 方式，获取排它锁,如死锁示例2。

(b)排他锁(X)：

SELECT * FROM table_name WHERE .... FOR UPDATE.

锁实现

锁实现

InnoDB行锁是通过给索引上的索引项来加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB行锁分三种情形。

(1)Record lock ：对索引项加锁。

(2)Grap lock：对索引项之间的”间隙”、第一条记录前的“间隙”，或者最后一条记录后的“间隙”加锁。

(3)Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

锁缺点

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而Innodb给出的解释是为了组织幻读的出现，所以他们选择的间隙锁来实现锁定。

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果和表锁一样。

不走索引时，行锁变表锁

当Query无法利用索引的时候，Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

如下图，session1只给一行添加排他锁，但session2在请求其他排他锁的时，却出现了锁等待！原因就是定位数据的id列没有加索引.

如果表有索引时，InnoDB就只锁定了符合条件的行

索引指定多条记录时

被锁定的索引对应的全部记录都会被锁定

不同索引锁定相同数据

间隙锁锁定间隙

当我们使用范围条件而不是相等条件去检索数据，并请求共享锁和排他锁时，InnoDB会给符合条件的已有的数据添加上锁；对于键值在条件范围内，但不存在的记录，叫间隙。InnoDB此时会给这个部分添加上锁，这种锁机制就是Next-key锁。

比较 > <

假如：员工表emp中只有101条数据，其empid为1,2,3，.... ，101,下面SQL

SELECT * FROM emp WHERE empid>100 for update ;

是一个条件范围的检索，InnoDB不仅会对符合条件的empid值为101的记录添加上锁，也会对大于101(这些记录不存在)的”间隙“加锁。

相等 =

gap锁 将锁定相邻的间隙

如图所示，InnoDB使用的是聚集索引，teacher_id身为二级索引，就要维护一个索引字段和主键id的树状结构(这里用链表形式表现)，并保持顺序排列。

Innodb将这段数据分成几个个区间

(negative infinity, 5],

(5,30],

(30,positive infinity)；

update class_teacher set class_name='初三四班' where teacher_id=30;不仅用行锁，锁住了相应的数据行；同时也在两边的区间，(5,30]和(30，positive infinity)，都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。

gap 锁

死锁

行级锁定肯定会带来死锁问题，Innodb也不可能例外。

示例1

开启事物以后,互相锁定了资源

示例2

死锁处理

在Innodb的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。

当Innodb检测到系统中产生了死锁之后，Innodb会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。

在Innodb发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。

但是有一点需要注意的就是，当产生死锁的场景中涉及到不止Innodb存储引擎的时候，Innodb是没办法检测到该死锁的，这时候就只能通过锁定超时限制来解决该死锁了。

innodb_lock_wait_timeout,默认安装时这个值是50s

锁分析

查看行锁争用

show status like 'innodb_row_lock%';可查看行锁争用情况；

如果Innodb_row_lock_waits和Innodb_row_lock_time_avg 比较大，说明锁争用比较严重，可以查询information_schema数据库中相关表查看锁情况，或者通过设置InnoDB Monitors来进一步观察锁冲突的表、数据行等，分析原因。

分析锁争用

(1)、通过查询information_schema数据库中innodb_locks表了解锁等待情况。

SELECT * FROM Innodb_locks G; 和 SELECT * FROM Innodb_locks_waits G ;

(2)通过设置InnoDB Monitors观察锁冲突情况。

解除死锁

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程(如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程)

show processlist

3.杀死进程id(就是上面命令的id列)

kill id

第二种：

1.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id(就是上面命令的trx_mysql_thread_id列)

kill 线程ID

锁优化

高效锁定

尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；

合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

避免死锁

a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；