Mysql锁机制及测试

简介：InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁；否则，InnoDB使用表锁 在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。

1. 行级锁与表级锁

InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁；否则，InnoDB使用表锁 在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。

• 测试表结构

create table transaction_test(
`id` int unsigned primary key auto_increment,
`count` int unsigned not null default 0 comment '测试字段',
`version` int unsigned not null default 0 comment '测试字段',
)engine=innodb default charset=utf8 comment '事务测试表';

实际sql测试

-- 间隙锁测试
begin;
select * from transaction_test where id >=1 and id <= 10 for update;
select * from transaction_test where id between 1 and 10 for update;
-- 排它锁
select * from transaction_test where id = 6 for update;
-- 共享锁
select * from transaction_test where id = 6 lock in share mode;
-- 无锁
select * from transaction_test where id = 6;
update transaction_test set count = 10 where id = 6;
update transaction_test set count = 10 where id = 12;
commit;
rollback;

死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB

• MySQL处理死锁的方式

  等待，直到超时（innodb_lock_wait_timeout=50s）。

  发起死锁检测，主动回滚事务，让其他事务继续执行（innodb_deadlock_detect=on）.

• 死锁检测

  死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

• 回滚机制

  检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。如果插入更新或者删除的行数一样则回滚后面执行的那条事务。

-- 查看日志文件设置状态
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 transaction_test where id = 32 for update;
update transaction_test set count = 1 where id = 1;
insert into transaction_test (id, count) values (32, 300);
commit;
rollback;

MVCC使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是 为了查询一些正在被另一个事务更新的行， 并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样一来的话查询就不用等 待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品， 以及mysql其它的存储引擎并不支持它。

• MySQL的innodb表除了实际的数据之外，还会加上3个隐藏的字段

  实际数据 | create_no(创建版本号或者创建时间) | update_no(每次修改的版本号或者修改时间) | delete_no(删除版本号或者删除时间)

  insert:当我们新增一条数据时，这条数据会加上创建的版本号update:修改当前的字段，每修改一次数据，修改版本号都会依次增加一次delete:删除当前的数据，其实并不会真实的删除，他会先在删除版本号字段记录下删除的版本号，在过了一段时间后会进行清除或者刷新

• MVCC是乐观锁的一种实现方式，但并不是MVCC就等于乐观锁

  -- 乐观锁测试
  select count, version  from transaction_test where id = 1;
  update transaction_test set count = count - 1,version = version + 1 where id = 1 and version = 0;

4. 乐观锁和悲观锁都属于一种思想，而非实际的MySQL锁机制

begin;
select count from transaction_test where id = 1;
update transaction_test set count = count - 1 where id = 1;
commit;
rollback;

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。间隙锁类似于页级锁，但是实际是行级锁。

-- 悲观锁测试
begin;
select count from transaction_test where id = 1;
update transaction_test set count = count - 1 where id = 1;
commit;
rollback;

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

• 所有的数据检索都通过索引来完成，从而避免因为间隙锁带来的负面影响而锁定了不该锁定的数据

• 在业务条件允许下，尽量使用较低隔离级别的事务，减少隔离级别带来的附加成本

• 如果容易死锁，就可以考虑使用表级锁来减少死锁的概率

有遗漏或者不对的可以在我的公众号留言哦