InnoDB中事务隔离级别的说明

版权声明:欢迎转载,请注明出处 https://blog.csdn.net/suneqing/article/details/79246901

我们都知道事务的几种性质,数据库中的一致性和隔离性等是实现事务的基本思想,在系统有大量的并发访问的情况下,了解和熟练应用数据库的本身的事务隔离级别,对于写出健壮性,并发处理能力强的代码还是起关键的作用。这里通过分析mysql InnoDB中事务的隔离级别,来抛砖引玉,让读者更好的理解,事务的隔离级别对于数据库数据的处理做了什么。

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能

·未提交读(Read Uncommitted):一个事务可以读取到另一个事务中未提交的数据。
·提交读(Read Committed):一个事务从开始直到提交之前,所做的任何修改对于其他事务都是不可见的。这个级别也叫不可重复读,因为两次执行的同样的查询,可能会得到不一样的结果。
·可重复读(Repeated Read):该级别保证了在同一个事务中多次对去同样结果是一致的。但是该级别还是无法解决幻读的问题:当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内容插入新的记录,则之前的事务提交事务后,再次读取该范围的记录会产生幻行。
·串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

下面就使用真实的例子来说明各个级别的情况

CREATE TABLE `city` (
`id`  int(1) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`state`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='市级信息'
AUTO_INCREMENT=2147483647
ROW_FORMAT=COMPACT
;

1、未提交读(Read uncommitted)

session 1

mysql> select @@session.tx_isolation; --查询当前事务的隔离级别为RR
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT INTO `city` (`id`, `name`, `state`) VALUES (3, '沧州', '河北');
Query OK, 1 row affected
session 2

mysql> select @@session.tx_isolation; --session2 当前回话隔离级别是RR
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set

mysql> set session transaction isolation level read uncommitted; --设置session2隔离级别为RU
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set
mysql> select * from city;  --RU级别下是可以查到没有提交事务的数据的,这里出现了脏读
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

由上可以得到,在未提交读(Read uncommitted)这个级别下,session2事务是可以读到session1中未提交事务的数据,这也就是脏读。所以这级别下是不能避免脏读的,所以这种隔离级别用到的可能性不大。

2、已提交读(Read committed)

session 1

mysql> set session transaction isolation level read committed; -- 修改session 1隔离级别为RC    
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+                                                     
session 2
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set

mysql> INSERT INTO `city` (`id`, `name`, `state`) VALUES (3, '沧州', '河北');
Query OK, 1 row affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set
session 1
--session 2虽然插入了数据,但是未提交数据。
--sesion 1在RC级别下是查不到未提交数据,所以RC级别避免了脏读
mysql> select * from city; 
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set
session 2
mysql> commit;
Query OK, 0 rows affected
session 1
-- session 2事务提交,session 1在未提交事务情况下,
-- 查询数据查出了不同的结果,所以RC级别不能避免重复读导致的数据不一致
mysql> select * from city; 
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

由上面内容可以得出,在RC级别下虽然避免的脏读的情况,但是不可避免重复读导致的两次查询不一致的情况。

3、可重复读(Repeatable read)

session 1
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set

mysql> INSERT INTO `city` (`id`, `name`, `state`) VALUES (3, '沧州', '河北');
Query OK, 1 row affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

mysql> commit;
Query OK, 0 rows affected
session 2 
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set
-- 直到sesion 1提交事务后,session 2也未查询到提交的新数据,所以RR级别下可以重复读。
mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set
session 2
-- 当session 2也提交事务后,再次查询,发现多了一条数据,这就说明RR级别下不能避免幻读的情况。
mysql> commit;
Query OK, 0 rows affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

我们知道mysql的默认的隔离级别是RR。那在这种隔离级别下是怎么避免幻读的呢?

session 1

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set

mysql> INSERT INTO `city` (`id`, `name`, `state`) VALUES (3, '沧州', '河北');
Query OK, 1 row affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

mysql> commit;
session 2

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from city;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
+----+--------+-------+
2 rows in set

mysql> select * from city for update;  --加X锁查询
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

mysql> select * from city  LOCK IN SHARE MODE; 加S锁查询
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 石家庄 | 河北  |
|  2 | 邯郸   | 河北  |
|  3 | 沧州   | 河北  |
+----+--------+-------+
3 rows in set

由上可以得出,一个事务中如果使用快照读,会得到一致性的结果,如果使用了加锁的读,就会读到其他事务提交的提交的最新的数据。

本身,已提交读(Read committed) 和可重复读(Repeatable read) 两个级别是矛盾的。在同一个事务里,如果保证了可重复读,就会看不到其他事务的提交,违背了提交读;如果保证了已提交读,就会导致前后两次读到的结果不一致,违背了可重复读。所以针对这种情况可以使用加锁去查询最新的数据。
MySQL InnoDB的可重复读并不保证避免幻读,需要应用使用加锁读来保证。

总结:
四个级别逐渐增强,每个级别解决一个问题。事务级别越高,性能越差,记住4个隔离级别的特点(上面的例子);

对于加锁查询后的数据其他事务想操作这些事务又会出现怎样的情况,这些就涉及到了数据库中S锁X锁的问题t,这些情况下还会有行锁,表锁等待的问题,这些内容会在下一篇文章中去详细的说明。

展开阅读全文

没有更多推荐了,返回首页