点击上方蓝字关注我们 文末有惊喜
lock table tableName1 read[write],tableName2 read[write];
show open tables where in_use > 0;
UNLOCK tables;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `locak_table`;
CREATE TABLE `locak_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `locak_table` VALUES (1, '刘备', 350);
INSERT INTO `locak_table` VALUES (2, '张飞', 16000);
INSERT INTO `locak_table` VALUES (3, '关羽', 2400);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
图中圆圈在转表示被阻塞
为每一行数据添加锁,加锁慢,容易出现死锁竞争,因为锁的每一行数据,锁的力度小,所以并发高,Innodb支持行级锁,行级锁是支持事务的。
如何解决这些并发问题呢?mysql使用四大隔离级别来解决,一级更比一级强,并发量一级比一级低。
mysql默认的事务隔离级别是:读已提交(REPEATABLE-READ)设置隔离级别:set tx_isolation='REPEATABLE-READ'; 查询支持的事务隔离级别:show variables like 'tx_isolation';
初始化SQL:
CREATE TABLE `locak_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `xnyh`.`locak_table`(`id`, `name`, `age`) VALUES (1, '刘备', 350);
INSERT INTO `xnyh`.`locak_table`(`id`, `name`, `age`) VALUES (2, 'hanmei', 16000);
INSERT INTO `xnyh`.`locak_table`(`id`, `name`, `age`) VALUES (3, 'lucy', 2400);
事务A操作
事务B对数据库进行了操作,但是还没有提交事务
在事务A中再执行一下查询语句,发现事务B的修改已经被事务A查询到了
在业务操作中,我们一般会将读到的数据取出来,做更新操作,如果现在需要对age+100,那么事务A更新到库中的结果是400,但是如果事务B回滚了,库中age就会恢复到350的状态,如果这时候事务B把400更新到库中,400相当于350并没有执行加100的操作,所以就出现了脏读,造成业务数据出现问题。
如果需要读到别人确保不会回滚的数据,那就得设置隔离级别为read-committed
事务B
事务A再次执行查询语句发现并没有查询到B未提交的语句,解决了脏读问题
事务B提交事务
事务A再进行查询,发现读到了别人提交的修改数据
虽然这个解决了脏读的问题,但是又会造成一个新的问题,就是相同的查询条件,查询结果不一致,产生了不可重复读的问题,这又得设置隔离级别为可重复读来解决。
set tx_isolation='repeatable-read';
因为在5.7中,mysql默认的事务隔离级别就是可重复读,所以可以不用进行声明设置。需要注意的是验证可重复读,不能再使用navcat客户端来执行sql的,我试了几次发现在navcat验证可重复读会失效,具体原因没有研究,我们现在使用mysql命令行来验证。事务A设置事务为可重读,并查询事务B修改数据并提交
事务A再次查询:
发现查询结果和之前一致,如果这时候我们在事务A中对age进行修改操作会出现什么效果呢?
大家觉得,现在库里面应该是多少呢,是300+300还是321+300?
可以看到是300+300=600,使用了事务B的修改数据,也就是当事务A执行修改操作的时候,会使用库中最新的状态来进行修改,从而保证了数据的一致性,这里面的机制是在可重复读的隔离级别中,mysql使用MVCC(多版本并发控制)机制来实现的。具体实现机制,我会在最后介绍。 现在我们已经把可重复读验证完了,但是你不要觉得可重复读就是完美的了,它也是有缺陷的,有可能会出现幻读的现象,幻读的理解在前面已经解释过,不理解的同学可以回头再看一遍。
既然有幻读这个问题,mysql又是如何解决的呢?1.使用可串型化,这个没有什么可解释的,像“单线程”一样按照顺序依次执行每个事务;2.在某些场景下可以使用间隙锁解决
select * from locak_table where id = 10 for update;
当mysql发现索引中有这条记录,就会加上行锁,如果没有就会使用间隙锁:如果数据库中最大的自增ID是8,那么这个时候间隙锁的范围就是8-11区间的值都被锁住,其他数据无法插入。如果当前字段没有创建索引则会升级为表锁。MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突,做到即使有读写冲突时,也能做到不加锁,非阻塞并发读。
Innodb 的MVCC机制会在底层为数据库中的每行记录都扩展两个虚拟字段(创建事务ID、删除事务ID),我们对数据进行添加操作时,会在记录的创建事务ID字段中保存当前事务ID;删除记录的时候,会将当前事务ID保存在删除数据中的删除事务ID中,并不会直接删除数据;更新数据的时候,会将当前修改后的数据复制一条出来插入库中,然后记录的创建事务ID为当前事务ID,并且将旧记录的删除事务ID更新当前事务ID;我们接下来看增删改查是如何操作的。假设当前库中的最新事务ID为1
begin;
INSERT INTO `xnyh`.`locak_table`(`id`, `name`) VALUES (1, '张飞');
INSERT INTO `xnyh`.`locak_table`(`id`, `name`) VALUES (2, '刘备');
commit;
执行完上面的语句后,库中最新的事务ID为2了,需要注意的是,事务分配ID并不是在begin之后就会被分配新的事务ID,只有你在执行了update or insert or delete语句之后,才会向mysql申请新的事务ID
这时候表中的数据应该是这样的
id | name | 创建事务ID | 删除事务ID |
---|---|---|---|
1 | 张飞 | 2 | Undefined |
2 | 刘备 | 2 | Undefined |
如果这个时候事务ID为1进行查询,是查询不到的,因为库中记录的创建事务ID是大于1 的。
begin;
update locak_table set name= '刘禅' where id =1;
commit;
当前已经为事务ID为3 了
id | name | 创建事务ID | 删除事务ID |
---|---|---|---|
1 | 张飞 | 2 | 3 |
2 | 刘备 | 2 | Undefined |
1 | 刘禅 | 3 | Undefined |
如果这个时候事务ID为2 进行查询,是查询不到新修改的数据,它的查询条件是这样的 创建事务ID要小于等于2并且删除事务ID大于2 或者为undefined。所以事务2查出来的数据还是2条
作者:乐哉
图片:来源于网络,如有侵权,联系删除。