mysql 的隔离级别

如果事务的隔离级别为REPEATABLE READ（默认），同一个事务中的所有一致性读都是读的事务的第一次读操作创建的快照。你可以提交当前事务，然后在新的查询中即可看到最新的快照，如上所示。
如果事务的隔离级别为READ COMMITTED，一致性读只是对事务内部的读操作和它自己的快照而言的，结果如下：

Session 1	Session 2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
	mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> update t set i=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
	mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.06 sec)
	mysql> select * from t; +------+ | i    | +------+ |    5 | +------+ 1 row in set (0.00 sec)

Session 1	Session 2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
	mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> update t set i=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
	mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.06 sec)
	mysql> select * from t; +------+ | i    | +------+ |    5 | +------+ 1 row in set (0.00 sec)

Session 1	Session 2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
	mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> update t set i=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
	mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.06 sec)
	mysql> select * from t; +------+ | i    | +------+ |    5 | +------+ 1 row in set (0.00 sec)

 

Session 1	Session 2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
	mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> update t set i=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
	mysql> select * from t; +------+ | i    | +------+ |    3 | +------+ 1 row in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.06 sec)
	mysql> select * from t; +------+ | i    | +------+ |    5 | +------+ 1 row in set (0.00 sec)

 

 

set autocommit = 0;    //关闭自动提交

                                      当自动提交被禁用后，执行的SQL语句只是修改了自己的快照，只有通过 commit 来提交数据修改真实的数据库！