阅读 308

Mysql-InnoDB中锁、事务、隔离级别精讲一

说明,开发中免不了用到数据库事务和锁,为了保证自己能够很好的运用这些知识,需要你对他们有深刻的了解,不然你会遇到各种各样奇怪的现象,究其原因,还是对这些知识似懂非懂,说不会,也能用,说会,用的不太明白,所以本文会结合mysql官方小册文档,总结出一套完整适用的事务和锁的知识,涉及到锁,事务,隔离级别等,并阐述它们之间的关系,在并发中,如何保证数据的安全等,由于篇幅可能过长,所以会分为几个小节,暂定3小节吧。

Mysql官方小册地址:dev.mysql.com/doc/refman/…

建表语句和初始数据的脚本在本文的最下面;

在本文中首先提几个问题

  1. Mysql数据库事务的隔离级别是怎么回事?
  2. Mysql数据中的锁是怎么回事?
  3. 用Mysql中的锁能解决什么问题,为什么要用它?
  4. Mysql数据库事务的隔离级别和锁的关系是什么?

一. Mysql数据库事务的隔离级别是怎么回事?

在使用Mysql数据库的时候,只有用到事务的时候,才会有隔离级别的概念,它指的是多个事务中的sql语句在操作数据的时候,设置不同的隔离级别会得到不同的预期结果.

查看当前会话的隔离级别:SELECT @@tx_isolation;

修改当前会话的隔离级别:

set session transaction isolation level read uncommitted;

set session transaction isolation level read committed;

set session transaction isolation level repeatable read;

set session transaction isolation level serializable;

Mysql-InnoDB存储引擎事务的隔离级别有四种:mysql-innoDB默认的隔离级别是REPEATABLE READ

事务隔离级别说明实际中使用级别
READ UNCOMMITTED未提交读很低
READ COMMITTED提交读很高
REPEATABLE READ可重复读
SERIALIZABLE串行很低

1.1 不同的隔离级别的区别

作者分别使用dbeaver作为其中一个事务的连接客户端,然后本地安装了mysql原始的客户端,它们连接了同一个数据库,然后开始操作,当然,完全可以开启两个原始的客户端来模拟,这根据自己的喜好选择即可

session1:dbeaver客户端:

image.png session2: mysql原始客户端:

image.png

通过表hopegaming_main.test_1234来逐一说明,前提是我们在两个session中分别开启事务,然后操作

1.1.1 READ UNCOMMITTED(读未提交)

顾名思义,就是一个事务中读取了另一个事务中尚未提交的数据

操作:将隔离级别都设置为read uncommitted,在session1开启一个事务,在表中插入一条数据,不提交事务,分别在session1和session2中查询表中的数据

session1:

操作脚本:

set session transaction isolation level read uncommitted;

begin;

select * from hopegaming_main.test_1234;

INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));

select * from hopegaming_main.test_1234;

插入前的数据

image.png

插入后的数据(事务未提交)

image.png

session2:

操作脚本:

set session transaction isolation level read uncommitted;

select * from hopegaming_main.test_1234;(插入前执行)

select * from hopegaming_main.test_1234;(插入后执行)

插入前的数据

image.png

插入后的数据(事务未提交)

image.png

现象分析: 可以看到session2中的select语句读取到了session1中未提交事务的数据,会有啥问题?一旦session1中的事务回滚,那么session2中读取到的就是垃圾数据,因为已经回滚了,这条记录不存在了,这就是常说的脏读

1.1.2 READ COMMITTED(读提交)

上面的读未提交会有脏读的问题,那么为了解决这个问题,就有了读提交的隔离级别,顾名思义,就是一个事务只会读取另一个事务中已经提交的数据.

操作:将隔离级别都设置为read committed,在session1开启一个事务,在表中插入一条数据,不提交事务,分别在session1和session2中查询表中的数据,然后提交事务,在session2中再查看表中的数据

session1:

操作脚本:

set session transaction isolation level read committed

begin;

select * from hopegaming_main.test_1234;

INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));

select * from hopegaming_main.test_1234;

commit;

插入后的数据(事务未提交)

image.png

session2:

操作脚本:

set session transaction isolation level read committed

select * from hopegaming_main.test_1234;(插入后执行,事务未提交)

select * from hopegaming_main.test_1234;(插入后执行,事务提交)

插入后的数据(事务未提交)

image.png

插入后的数据(事务已提交)

image.png

现象分析: 可以看到session2中的select语句,在session1的事务提交之前,读取到的数据还是原来的数据,等事务提交后,再次执行select语句,发现读取到了插入的数据,那么得出此隔离级别解决了脏读的问题,它不会读取到事务未提交的数据,那么此隔离级别有什么问题?可能你们也发现了,session1中事务提交前后,session2读取到的数据不同,事务提交前,是一个数据,事务提交后,读取到的是包含新数据的集合,那么在有些业务中,在session2中可能会重复读取数据,但是读取的结果是不一样的,因为读取的动作可能是在事务提交前后,会造成数据读取不一致,这就是常说的不可重复读

1.1.3 REPEATABLE READ(可重复读)

上面的读提交隔离级别在一些业务中会有不可重复读的问题,那么为了解决这个问题,就有了可重复读的隔离级别,它说的是,A事务中读取B事务中的数据,在B事务提交前后,A读取的结果是一样的,这就是可重复读,原理是,A在第一读取时,形成快照,A事务在后面读取该语句不管B事务是否提交,直接从第一次读取形成的快照读取,因此读取到的数据是一致的

操作:将隔离级别都设置为repeatable read,在session1开启一个事务,在表中插入一条数据,不提交事务,session2中查询表中的数据, 然后session1提交事务,在session2中再次查看表中的数据

session1:

操作脚本:

set session transaction isolation level repeatable read

begin;

select * from hopegaming_main.test_1234;

INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));

commit;

session2:

操作脚本:

set session transaction isolation level repeatable read

begin;

select * from hopegaming_main.test_1234;(事务提交前读)

select * from hopegaming_main.test_1234;(事务提交后读)

插入后的数据(事务未提交)

image.png

插入后的数据(事务已提交)

image.png

现象分析: 可以看到session2中的select语句,在session1中事务提交前后,读取到的数据是一样的,这就是可重复读,它解决了不可重复的问题,那么此隔离级别会有什么问题? 就像上面分析的一样,在可重复读的隔离级别中,session2中读取到的数据,在session1事务提交前后是一样的,以插入数据为例,session2读取到的都是都是插入前的数据,因为session1中的事务已经提交,新的数据实际上是已经入库,而在session2的事务中读取到的都是插入前的数据,一旦session2也提交了事务,然后再次读取的时候,发现数据多了,这样会给人造成一种幻读的感觉,这就是常说的幻读幻读通常指的是有插入和删除操作的时候造成的幻读,那么幻读怎么解决?这样就引出了另一个隔离级别,串行

1.1.4 SERIALIZABLE(串行)

上面可重复读会有幻读的问题,那么就出现了串行化的概念来解决此问题

操作:将隔离级别都设置为serializable,在session1开启一个事务,在表中插入一条数据,不提交事务,session2中查询表中的数据, 然后session1提交事务,在session2中再次查看表中的数据

session1:

操作脚本:

set session transaction isolation level serializable

begin;

select * from hopegaming_main.test_1234;

INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));

commit;

session2:

操作脚本:

set session transaction isolation level serializable

begin;

select * from hopegaming_main.test_1234;(事务提交前读)

select * from hopegaming_main.test_1234;(事务提交后读)

插入后的数据(事务未提交)

发现一直查不出来,只有当session1中的事务提交后,才能查询出数据,这就不会产生幻读了,因为涉及到insert,delete等语句,只有事务提交后,才能读取,这就是串行化,串行的意思就是一个执行完再执行另一个,不能并行,这会严重降低性能,所以,此隔离级别很少用到,如果不是一些特殊的业务需求,这个隔离级别很难用到

现象分析:

串行的隔离级别,不能并行处理,严重降低性能,生产上很少使用,可略过

建表语句和初始数据的脚本

CREATE TABLE `hopegaming_main`.`test_1234` (
  `id` varchar(30) NOT NULL COMMENT '身份证号',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `trade_id` varchar(100) DEFAULT NULL COMMENT '交易id',
  `gender` tinyint(4) DEFAULT NULL COMMENT '性别',
  `birthday` timestamp(6) NOT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_trade_id` (`trade_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO hopegaming_main.test_1234
(id, name, trade_id, gender, birthday)
VALUES('1', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6)),
('2', 'zhaosi', '124', 0, CURRENT_TIMESTAMP(6)),
('3', 'wangwu', '125', 0, CURRENT_TIMESTAMP(6)),
('4', 'maqi', '126', 0, CURRENT_TIMESTAMP(6));

复制代码
文章分类
后端
文章标签