事务隔离级别示例

mysql 数据库事务并发产生的问题：

脏读：读到没有提交的事务产生的数据

不可重复度：一个事务因为别的事务修改或者删除数据导致前后重复读的数据不一致

幻读：一个事务因为别的事务提交了数据，导致前后重复读第二次读取的数据更多

为了解决这些问题，mysql提供了以下事务隔离级别，不同隔离级别分别可以解决上述的问题。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	不可能（ innodb_locks_unsafe_for_binlog 关闭可能）
串行化	不可能	不可能	不可能

分别验证隔离级别

读未提交

会话A	会话B
mysql> set session transaction isolation level read uncommitted;	mysql> set session transaction isolation level read uncommitted;	设置当前会话的隔离级别。要把全局隔离级别也设置成读未提交
mysql> select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+	mysql> select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+	查看当前会话的隔离级别
mysql> select * from user ; Empty set	mysql> select * from user ; Empty set	当前user表为空
mysql> start transaction;	mysql> start transaction;	两个会话都开启事务
mysql> insert into user values(1, "mff"); Query OK, 1 row affected (0.12 sec)		会话A事务插入一条数据
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.17 sec)	会话B事务读取到一条事务A的未提交的数据，即为脏读。未提交的都能读取到，那么提交的自然更能读取到，读取到提交的数据称之为幻读
mysql> insert into user values(2, "mff2"); Query OK, 1 row affected (0.06 sec)		会话A事务再插入一条数据
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | | 2 | mff2 | +----+------+	会话B事务还是能读到数据。
mysql> update user set name="mff_update" where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0		会话A事务更新一条数据
	mysql> select * from user; +----+------------+ | id | name | +----+------------+ | 1 | mff_update | | 2 | mff2 | +----+------------+ 2 rows in set (0.00 sec)	会话B事务读取到会话A事务更新的数据也为脏读。会话A事务提交之后会话B读取到，和之前读取结果不一致，称之为不可重复读。
mysql> delete from user where id=2; Query OK, 1 row affected (0.00 sec)		会话A事务删除一条数据
	mysql> select * from user; +----+------------+ | id | name | +----+------------+ | 1 | mff_update | +----+------------+ 1 row in set (0.00 sec)	会话B事务读取到会话A事务删除的数据，也为脏读。会话A事务提交之后会话B事务仍然能读取到，称之为不可重复读。
	delete from user where id=2 limit 1;	会话B事务删除一条数据，被锁住了
mysql> rollback; Query OK, 0 rows affected (1.61 sec)		会话A事务回滚
	Query OK, 0 rows affected (32.15 sec)	会话B事务删除成功，0行被删除
	mysql> commit; Query OK, 0 rows affected (0.13 sec) mysql> select * from user; Empty set

读已提交

会话A	会话B
mysql> set global transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)	mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> set global transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)	设置事务隔离级别为读已提交
mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set, 1 warning (0.00 sec)	mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set, 1 warning (0.00 sec)	查看当前事务隔离级别为读未提交
mysql> select * from user; Empty set (0.00 sec)	mysql> select * from user; Empty set (0.00 sec)	当前user表为空
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)	会话A事务和会话B事务开启事务
mysql> insert into user value(1, "mff"); Query OK, 1 row affected (0.00 sec)		会话A事务插入数据
mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.00 sec)		会话A事务当中可以看到插入的数据
	mysql> select * from user; Empty set (0.00 sec)	会话B事务中读取不到会话A事务插入但是未提交的数据。即不存在脏读现象
mysql> commit; Query OK, 0 rows affected (0.00 sec)		会话A事务提交
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.00 sec)	会话A事务提交之后，会话B事务读到了会话A事务插入提交的数据。上次查询是没有的，第二次查询数据更多，这说明存在幻读
mysql> update user set name="mff_update" where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0		会话A事务（重新开启的事务，单条sql为一个事务）更新数据。
	mysql> select * from user; +----+------------+ | id | name | +----+------------+ | 1 | mff_update | +----+------------+ 1 row in set (0.00 sec)	会话B事务读取到会话A事务更新并且提交的数据，存在重复读的现象。
mysql> delete from user where id=1; Query OK, 1 row affected (0.01 sec)		会话A事务（重新开启的事务，单条sql为一个事务）删除数据。
	mysql> select * from user; Empty set (0.00 sec)	会话B事务读取到会话A事务删除并且提交的数据，存在重复读的现象。
mysql> insert into user value(1, "mff"); Query OK, 1 row affected (0.00 sec)		会话A事务（重新开启的事务，单条sql为一个事务）插入数据。
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.00 sec)	会话B事务读取到会话A事务新增并且提交的数据，存在幻读的现象。
	mysql> commit; Query OK, 0 rows affected (0.00 sec)	会话B事务提交

可重复读

会话A	会话B
mysql> set global transaction isolation level repeatable read; Query OK, 0 rows affected (0.13 sec) mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.05 sec)	mysql> set global transaction isolation level repeatable read; Query OK, 0 rows affected (0.13 sec) mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.05 sec)	两个会话的隔离级别设置为可重复读
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.11 sec)	mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.11 sec)	查看当前会话的隔离级别为可重复读
mysql> select * from user; Empty set	mysql> select * from user; Empty set	当前user表为空
mysql> start transaction; Query OK, 0 rows affected (0.01 sec)	mysql> start transaction; Query OK, 0 rows affected (0.01 sec)	会话A，会话B同时开启事务
mysql> insert into user values(1, "mff"); Query OK, 1 row affected (0.11 sec)		会话A的事务插入数据
	mysql> select * from user; Empty set	会话B的事务读取不到数据，说明不存在脏读的现象
mysql> commit; Query OK, 0 rows affected (1.68 sec)		会话A的事务提交
	mysql> select * from user; Empty set	会话B的事务读取不到会话A事务新增且提交的事务，所以不存在幻读的现象
	mysql> commit; Query OK, 0 rows affected (0.01 sec)	会话B的事务提交
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.09 sec)	会话B的事务结束后读取到之前会话A事务提交的数据
	mysql> start transaction; Query OK, 0 rows affected (0.02 sec)	会话B再次开启事务
mysql> update user set name="mff_update"; Query OK, 1 row affected (1.42 sec)		会话A更新数据
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.08 sec)	会话B事务中读取不到会话A更新的数据。说明不存在不可重复读现象
mysql> delete from user ; Query OK, 1 row affected (1.98 sec)		会话A删除数据
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (0.07 sec)	会话B事务中读取不到会话A删除的数据。说明不存在不可重复读现象
	mysql> commit; Query OK, 0 rows affected (0.02 sec)	会话B事务提交
	mysql> select * from user; Empty set	会话B读取到之前会话A对于数据的删除。

串行化

会话A	会话B
mysql> set global transaction isolation level serializable; Query OK, 0 rows affected (0.01 sec) mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.01 sec)	mysql> set global transaction isolation level serializable; Query OK, 0 rows affected (0.01 sec) mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.01 sec)	设置会话隔离级别串行化
mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+ 1 row in set (0.08 sec)	mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+ 1 row in set (0.08 sec)	查询会话隔离级别为串行化
mysql> select * from user; Empty set	mysql> select * from user; Empty set
mysql> start transaction; Query OK, 0 rows affected (0.87 sec)	mysql> start transaction; Query OK, 0 rows affected (0.87 sec)	同时开启事务
mysql> insert into user value(1, "mff"); Query OK, 1 row affected (3.95 sec)		会话A事务插入数据
	mysql> select * from user;	会话B事务查询user数据,等待锁。事务A占用写锁，事务B读锁无法执行
mysql> commit; Query OK, 0 rows affected (1.22 sec)		会话A提交
	mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | mff | +----+------+ 1 row in set (10.79 sec)	会话A提交后，会话B的事务才能读取到会话A事务的数据。这也说明不会出现脏读现象。
mysql> start transaction; Query OK, 0 rows affected (0.18 sec)		会话A开启事务
mysql> update user set name="mff-name";		会话A事务更新数据，等待锁。事务B占用读锁，事务A写锁无法执行
	mysql> commit; Query OK, 0 rows affected (0.01 sec)	会话B事务提交
mysql> update user set name="mff-name"; Query OK, 1 row affected (7.89 sec) Rows matched: 1 Changed: 1 Warnings: 0		会话A事务更新成功
		由此可见事务之间的对于同一数据的读写时互斥的。那么肯定不可能产生脏读，不可重复读和幻读的现象。

 

 

 

事务隔离的原理

 

数据库在RR 级别下隔离不同事务的提交内容，是因为数据库的快照读。可以理解为数据库开启时会产生当前事务的数据快照视图，之后的读取只在当前快照视图读取。

但是当对数据进行 update delete insert 时,会对最新内容进行修改，此时要进行当前读。当前读会对数据加锁。因此不同事务对同一数据进行写操作是要获取锁的。