mysql事务默认隔离级别_Mysql 事务隔离级别分析

Mysql默认事务隔离级别是：REPEATABLE-READ

--查询当前会话事务隔离级别

mysql> select @@tx_isolation;+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00sec)

--全局查询

mysql> select @@global.tx_isolation;+-----------------------+

| @@global.tx_isolation |

+-----------------------+

| REPEATABLE-READ |

+-----------------------+

1 row in set (0.00sec)

mysql>

修改事务权限的语句是：set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable | Serializable;

列：set global transaction isolation level Read committed

mysql> show variables like 'tx_isolation';+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set, 1 warning (0.00sec)

mysql> set @@session.tx_isolation='serializable';

Query OK,0 rows affected (0.00sec)

mysql> show variables like 'tx_isolation';+---------------+--------------+

| Variable_name | Value |

+---------------+--------------+

| tx_isolation | SERIALIZABLE |

+---------------+--------------+

1 row in set, 1 warning (0.00sec)

mysql>

View Code

事务隔离分析

1.可重复读分析(Repeatable-read)

举例：

A，B两个事务同时开启,事务A插入一条数据后提交事务,此时事务B所在会话是查不到A录入的数据的,但是会话B此时执行更新操作(包括了A录入的数据)时,会话B会看到A录入的那条数据.

总结：同样的SQL查询,多出一条数据,即产生幻读

--会话A-事务A

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 12 |

| 3 | 小四 | 22 |

+----+------+------+

3 rows in set (0.00sec)

mysql> insert into user (name,age) values ('小五',55);

Query OK,1 row affected (0.01sec)

mysql> commit;

Query OK,0 rows affected (0.08sec)

mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 12 |

| 3 | 小四 | 22 |

| 4 | 小五 | 55 |

+----+------+------+

4 rows in set (0.00sec)

mysql>

View Code

--会话B,事务B

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 12 |

| 3 | 小四 | 22 |

+----+------+------+

3 rows in set (0.00sec)

mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 12 |

| 3 | 小四 | 22 |

+----+------+------+

3 rows in set (0.00sec)

mysql> update user set age=111 where id=1;

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 111 |

| 2 | 小三 | 12 |

| 3 | 小四 | 22 |

+----+------+------+

3 rows in set (0.00sec)

mysql> update user set age=11;

Query OK,4 rows affected (0.00sec)

Rows matched:4 Changed: 4 Warnings: 0mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 11 |

| 3 | 小四 | 11 |

| 4 | 小五 | 11 |

+----+------+------+

4 rows in set (0.00sec)

mysql>

View Code

2.串行化分析(Serializable )

举例：

事务A，B,事务A对表user执行删除操作,事务B查询表user会导致超时.事务A提交后,B顺利完成查询.

总结：串行化没有并发处理能力,谨慎使用

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> delete from user where id=5;

Query OK,1 row affected (0.01sec)

mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 11 |

| 3 | 小四 | 11 |

| 4 | 小五 | 11 |

+----+------+------+

4 rows in set (0.00sec)

mysql> commit;

Query OK,0 rows affected (0.04sec)

mysql>

View Code

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 11 |

| 3 | 小四 | 11 |

| 4 | 小五 | 11 |

| 5 | 小六 | 66 |

+----+------+------+

5 rows in set (0.00sec)

mysql> commit;

Query OK,0 rows affected (0.00sec)

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * from user;

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>mysql> select * from user;+----+------+------+

| id | name | age |

+----+------+------+

| 1 | 小二 | 11 |

| 2 | 小三 | 11 |

| 3 | 小四 | 11 |

| 4 | 小五 | 11 |

+----+------+------+

4 rows in set (0.00sec)

mysql>

View Code

3.读已提交分析(READ-COMMITTED)

总结：出现 幻读、不可重读

mysql> set @@session.tx_isolation='read-committed';

Query OK,0 rows affected (0.00sec)

mysql> select @@tx_isolation;+----------------+

| @@tx_isolation |

+----------------+

| READ-COMMITTED |

+----------------+

1 row in set (0.00sec)

mysql>

4.读未提交分析(READ-UNCOMMITTED)

总结：隔离性最低,会导致 幻读、不可重读、脏读

脏读：当前事务能看到其他事务中未提交的数据

mysql> set @@session.tx_isolation='read-uncommitted';

Query OK,0 rows affected (0.00sec)

mysql> select @@tx_isolation;+------------------+

| @@tx_isolation |

+------------------+

| READ-UNCOMMITTED |

+------------------+

1 row in set (0.00sec)

mysql>

脏读、幻读、不可重读

脏读：当前事务可查看其他事务未提交的数据(重点是未提交)

幻读：同一SQL再执行，会多出或者少了一部分数据(重点在增加、减少)

不可重读：同一事务,查询相同数据范围,数据被更改(重点在更改)

Spring中的事务有个默认值Default，默认使用数据库的事务隔离级别

packageorg.springframework.transaction.annotation;public enumIsolation {

DEFAULT(-1),

READ_UNCOMMITTED(1),

READ_COMMITTED(2),

REPEATABLE_READ(4),

SERIALIZABLE(8);private final intvalue;private Isolation(intvalue) {this.value =value;

}public intvalue() {return this.value;

}

}

项目中的设置

事务隔离界别越高,并发性越弱,而过低的级别存在安全问题,所以项目中事务隔离级别多设置为 READ_COMMITTED

不可重复与幻读

不可重复读和幻读的区别

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。

但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。

需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

Repeatable Read隔离级别下 间隙锁避免了幻读

表user中 主键id，age添加了索引，可以看到，在左边事务未提交之前,是看不到右侧事务添加的数据的。间隙锁避免了幻读