Mysql默认事务隔离级别是：REPEATABLE-READ
--查询当前会话事务隔离级别
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) --全局查询 mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) mysql>
mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set, 1 warning (0.00 sec) mysql> set @@session.tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'tx_isolation'; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | tx_isolation | SERIALIZABLE | +---------------+--------------+ 1 row in set, 1 warning (0.00 sec) mysql>
事务隔离分析
请参考文章：http://www.zsythink.net/archives/1233
1.可重复读分析(Repeatable-read)
举例：
A，B两个事务同时开启,事务A插入一条数据后提交事务,此时事务B所在会话是查不到A录入的数据的,但是会话B此时执行更新操作(包括了A录入的数据)时,会话B会看到A录入的那条数据.
总结：同样的SQL查询,多出一条数据,即产生幻读--会话A-事务A mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 12 | | 3 | 小四 | 22 | +----+------+------+ 3 rows in set (0.00 sec) mysql> insert into user (name,age) values ('小五',55); Query OK, 1 row affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.08 sec) mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 12 | | 3 | 小四 | 22 | | 4 | 小五 | 55 | +----+------+------+ 4 rows in set (0.00 sec) mysql>
--会话B,事务B mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 12 | | 3 | 小四 | 22 | +----+------+------+ 3 rows in set (0.00 sec) mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 12 | | 3 | 小四 | 22 | +----+------+------+ 3 rows in set (0.00 sec) mysql> update user set age=111 where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 111 | | 2 | 小三 | 12 | | 3 | 小四 | 22 | +----+------+------+ 3 rows in set (0.00 sec) mysql> update user set age=11 ; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 11 | | 3 | 小四 | 11 | | 4 | 小五 | 11 | +----+------+------+ 4 rows in set (0.00 sec) mysql>
2.串行化分析(Serializable )
举例：
事务A，B,事务A对表user执行删除操作,事务B查询表user会导致超时.事务A提交后,B顺利完成查询.
总结：串行化没有并发处理能力,谨慎使用mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from user where id=5; Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 11 | | 3 | 小四 | 11 | | 4 | 小五 | 11 | +----+------+------+ 4 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.04 sec) mysql>
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 11 | | 3 | 小四 | 11 | | 4 | 小五 | 11 | | 5 | 小六 | 66 | +----+------+------+ 5 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> mysql> select * from user; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 小二 | 11 | | 2 | 小三 | 11 | | 3 | 小四 | 11 | | 4 | 小五 | 11 | +----+------+------+ 4 rows in set (0.00 sec) mysql>
3.读已提交分析(READ-COMMITTED)
总结：出现 幻读、不可重读
mysql> set @@session.tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql>
4.读未提交分析(READ-UNCOMMITTED)
总结：隔离性最低,会导致 幻读、不可重读、脏读
脏读：当前事务能看到其他事务中未提交的数据
mysql> set @@session.tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) mysql> select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec) mysql>
脏读、幻读、不可重读
脏读：当前事务可查看其他事务未提交的数据(重点是未提交)
幻读：同一SQL再执行，会多出或者少了一部分数据(重点在增加、减少)
不可重读：同一事务,查询相同数据范围,数据被更改(重点在更改)