首页 > PHP技术 > php高级 > Mysql-InnoDB 锁机制及测试
2020
08-01

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_increment
count   int(11) YES         
version int(11) YES     0   
---
id  count   version
1   1   1
6   11  0
10  14  0
11  14  0
12  10  0
13  15  0
14  16  0
15  17  0
16  18  0
21  22  0
24  24  0
25  25  0
26  26  0
27  27  0
28  28  0
29  29  0
30  300 0
31  301 0

1.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 w

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在索引上面加锁的时候更加准确。
  • 在应用中尽可能做到访问的顺序执行
  • 如果容易死锁,就可以考虑使用表锁来减少死锁的概率

扫码芷若 获取免费视频学习资料

编程学习

查 看2019高级编程视频教程免费获取