Mysql-InnoDB 锁机制及测试

1. 死锁1.1 死锁概念:死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产 生了死锁,这些永远在互相等待的进程称为死锁进程.表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB 1.2 mysql处理死锁的方式等待,直到超时(innodb_lock_wait_timeout=50s)。
发起死锁检测,主动回滚一条事务,让其他事务继续执行(innodb_deadlock_detect=on)。
1.3 死锁检测死锁检测的原理是构建一个以事务为顶点、锁为边的有向图,判断有向图是否存在环,存在即有死锁。
 1.4 回滚机制检测到死锁之后,选择插入更新或者删除的行数最少的事务回滚,基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。
如果插入更新或者删除的行数一样则回滚后面执行的那条事务。
 1.5 测试表及数据:下面不再展示【p_transaction 表】id  int(11) NO  PRI     auto_incrementcount   int(11) YES         version int(11) YES     0   ---id  count   version1   1   16   11  010  14  011  14  012  10  013  15  014  16  015  17  016  18  021  22  024  24  025  25  026  26  027  27  028  28  029  29  030  300 031  301 01.6 测试用例-- 查看日志文件设置状态show variables like "%innodb_flush_log_at_trx_commit%";-- 更改日志文件设置状态set @@global.innodb_flush_log_at_trx_commit = 0; -- 0,1,2 -- 锁等待时间show VARIABLES like "%innodb_lock_wait_timeout%";-- 死锁自动回滚show VARIABLES like "%innodb_deadlock_detect%"; -- 死锁测试begin;select * from p_transaction where id = 32 for update;update p_transaction set count = 1 where id = 1;insert into p_transaction (id, count) values (32, 300);commit;rollback;2. MVCC 乐观锁2.1 英文全称为Multi-Version Concurrency Control,翻译为中文即「多版本并发控制」。
MVCC使得InnoDB的事务隔离级别下执行一致性读操作有了保证,换言之,就是 为了查询一些正在被另一个事务更新的行, 并且可以看到它们被更新之前的值。
这是一个可以用来增强并发性的强大的技术,因为这样一来的话查询就不用等 待另一个事务释放锁。
这项技术在数据库领域并不是普遍使用的。
一些其它的数据库产品, 以及mysql其它的存储引擎并不支持它。
 2.2 mysql的innodb表除了实际的数据之外,还会加上3个隐藏的字段,如下:实际数据 | create_no(创建版本号或者创建时间) | update_no(每次修改的版本号或者修改时间) | delete_no(删除版本号或者删除时间)insert:当我们新增一条数据时,这条数据会加上创建的版本号update:修改当前的字段,每修改一次数据,修改版本号都会依次增加一次delete:删除当前的数据,其实并不会真实的删除,他会先在删除版本号字段记录下删除的版本号,在过了一段时间后会进行清除或者刷新2.3 MVCC是乐观锁的一种实现方式,但并不是MVCC就等于乐观锁。
2.4 测试用例-- 乐观锁测试select count, version  from p_transaction where id = 1;update p_transaction set count = count - 1,version = version + 1 where id = 1 and version = 0; 3. 乐观锁与悲观锁:乐观锁与悲观锁都属于是一种思想,而非实际的 mysql 锁机制。
乐观锁:不使用锁机制悲观锁:只要使用了锁机制都属于悲观锁 3.1 测试用例-- 悲观锁测试begin;select count from p_transaction where id = 1;update p_transaction set count = count - 1 where id = 1;commit;rollback;4. 间隙锁当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但不存在的 记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。
间隙锁类次与页级锁,但是实际是行级锁。
 4.1 测试用例-- 悲观锁测试begin;select count from p_transaction where id = 1;update p_transaction set count = count - 1 where id = 1;commit;rollback;5. 行级锁升级为表级锁InnoDB 行级锁是通过给索引上的索引项加锁来实现的,InnoDB行级锁只有通过索引条件检索数据,才使用行级锁;否则,InnoDB使用表锁 在不通过索引(主 键)条件查询的时候,InnoDB是表锁而不是行锁。
 5.1 测试用例-- 间隙锁测试begin;select * from p_transaction where id >=1 and id <= 10 for update;select * from p_transaction where id between 1 and 10 for update;-- 排它锁select * from p_transaction where id = 6 for update;-- 共享锁select * from p_transaction where id = 6 lock in share mode;-- 无锁select * from p_transaction where id = 6;update p_transaction set count = 10 where id = 6;update p_transaction set count = 10 where id = 12;commit;rollback;6. 事务的使用建议控制事务大小,减少锁定的资源量和锁定时间长度。
人所有的数据检索都通过索引来完成,从而避免因为无法通过索引加锁而升级为表锁。
减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的数据。
在业务条件允许下,尽量使用较低隔离级别的事务隔离。
减少隔离级别带来的附加成本。
合理使用索引,让innodb在索引上面加锁的时候更加准确。
在应用中尽可能做到访问的顺序执行如果容易死锁,就可以考虑使用表锁来减少死锁的概率 来源:https://www.jianshu.com/p/658078924a88

返回列表
上一篇:
下一篇: