原标题: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.数据量大考虑分库分表。返回搜狐,查看更多
责任编辑: