mysql innodb锁机制_InnoDB锁机制分析及实战

原标题：InnoDB锁机制分析及实战

MySQL锁概述

InnoDB存储引擎介绍

InnoDB原理介绍

InnoDB锁介绍

●基本锁

基本锁：共享锁(Shared Locks：S锁)与排他锁(Exclusive Locks：X锁)

MySQL允许拿到S锁的事务读一行，允许拿到X锁的事务更新或删除一行。

加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁；

加了X锁的记录，不允许其他事务再加S锁或者X锁。

●意向锁(Intention Locks)

InnoDB为了支持多粒度(表锁与行锁)的锁并存，引入意向锁。

意向锁是表级锁，可分为意向共享锁(IS锁)和意向排他锁(IX锁)。

●行锁

记录锁(Record Locks)

记录锁, 仅仅锁住索引记录的一行。

单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚簇主键索引，那么锁住的就是这个隐藏的聚簇主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

间隙锁(Gap Locks)

区间锁, 仅仅锁住一个索引区间(开区间)。

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

next-key锁(Next-Key Locks)

record lock + gap lock, 左开右闭区间。

默认情况下，Innodb使用next-key locks来锁定记录。

但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

插入意向锁(Insert Intention Locks)

Gap Lock中存在一种插入意向锁(Insert Intention Lock)，在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。

InnoDB加锁实例分析

SQL1：select * from t1 where id = 10;

SQL2：delete from t1 where id = 10;

组合一：id列是主键，RR隔离级别

组合二：id列是二级唯一索引，RR隔离级别

组合三：id列是二级非唯一索引，RR隔离级别

组合四：id列上没有索引，RR隔离级别

InnoDB死锁案例分析

死锁情况三

场景：用户连续发起两次添加用户默认收货地址请求，请求的事务包含两步操作

1.清除用户原默认收货地址:update tm_shipping_address set is_default= 0 where pin=#{pin}

2.添加新默认收货地址:insert into tm_shipping_address...

InnoDB SQL优化建议

1.根据业务合理设置索引，控制索引个数。

2.并发访问同一张表时，以相同的，固定的顺序访问，能大大减少产生死锁的机会。

3.建立索引的字段尽量使用数值类型以减少索引大小。

4. update语句一定要带where条件…，并且where条件字段一定要加索引，否则将导致全表加写锁，影响其他事务的执行，严重降低数据库吞吐量。

5.批量插入使用foreach标签，而不是多次insert。

6.尽量不要使用复杂sql，加快锁的释放速度(可以适当增加冗余字段)。

7.数据量大考虑分库分表。返回搜狐，查看更多

责任编辑：