linux事务隔离级别,linux Mysql 事务隔离级别测试

Mysql 5.5默认使用innodb存储引擎(表类型)，innodb是支持事务处理的，对innodb下各事务隔离级别进行测试，可以更好的理解各级别产生的各种问题~

设置innodb的事务级别方法是：set 作用域 transaction isolation level 事务隔离级别，例如~

mysql > set global transaction isolation level read committed;  //全局的

mysql > set session transaction isolation level read committed; //当前会话

测试使用的表名为student ,表结构为id(int)，name(varchar(16))，age(int)~

1.测试未授权读取(read uncommitted)：允许脏读取，但不允许更新丢失

事务T1：

mysql> select @@tx_isolation;

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

| @@tx_isolation   |

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

| READ-UNCOMMITTED |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

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

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update student set name = 'tom' where id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务T2：

mysql> select @@tx_isolation;

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

| @@tx_isolation   |

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

| READ-UNCOMMITTED |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T1：

mysql> update student set name = 'tom' where id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务T2：

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | tom  |  18 |

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

2 rows in set (0.00 sec)

事务T1：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

事务T2：

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

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

2 rows in set (0.00 sec)

以上可以看出，脏读了~

事务T2：mysql> update student set name = 'bob' where id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务T1：

mysql> update student set name = 'tom' where id = 2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

以上可以看出事务T1和事务T2之间更新没有丢失，直到一个更新提交后另一个更新才能进行，这里发生了等待超时错误~

2.测试授权读取(read committed)：允许不可重复读取，但不允许脏读取

事务T1：

mysql> select @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

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

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T2：

mysql> select @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

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

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T1：mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

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

2 rows in set (0.00 sec)

事务T2：

mysql> insert into student values(3,'bob',20);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

事务T1:

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | bob  |  20 |

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

3 rows in set (0.00 sec)

可以看出，在T1事务中，条件相同的两次读取出现不同的结果~~脏读测试这里没有进行，可以自己测试看看~

3.可重复读取(Repeatable Read)：禁止不可重复读取和脏读取，但是有时可能出现幻影数据，innodb中使用了措施来防止幻读

事务T1：

mysql> select @@tx_isolation;

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

| @@tx_isolation  |

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

| REPEATABLE-READ |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | bob  |  20 |

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

3 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T2：

mysql> select @@tx_isolation;

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

| @@tx_isolation  |

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

| REPEATABLE-READ |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | bob  |  20 |

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

3 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T2：

mysql> update student set name = 'mary' where id = 3;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务T1：

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | bob  |  20 |

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

3 rows in set (0.00 sec)

可以看出，没有出现脏读~事务T2：

mysql> insert into student values(4,'lucy',18);

Query OK, 1 row affected (0.00 sec

事务T1：

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | bob  |  20 |

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

3 rows in set (0.00 sec)

事务T2：

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

事务T1：

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | bob  |  20 |

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

3 rows in set (0.00 sec)

可以看出，在同一个事务中没有出现不可重复读(更新提交后)和幻读(插入提交后)~

4.序列化(Serializable)：提供严格的事务隔离，锁表，读取时会自动设置读锁，然后所有会话只能进行读操作；写时自动设置写锁，然后所有会话只能等待~

事务T1：

mysql> select @@tx_isolation;

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

| @@tx_isolation |

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

| SERIALIZABLE   |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | mary |  20 |

|  4 | lily |  18 |

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

4 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T2：

mysql> select @@tx_isolation;

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

| @@tx_isolation |

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

| SERIALIZABLE   |

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

1 row in set (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | mary |  20 |

|  4 | lily |  18 |

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

4 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T2：mysql> update student set name = 'lucy' where id = 4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务T1：

mysql> select * from student;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事务T1：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从上可以看出,T2存在写锁，所有T2的读操作会等待超时~

事务T2：

mysql> COMMIT;

Query OK, 0 rows affected (0.01 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

事务T1：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | mary |  20 |

|  4 | lucy |  18 |

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

4 rows in set (0.01 sec)

事务T2：mysql> select * from student;

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

| id | name | age |

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

|  1 | kate |  18 |

|  2 | jim  |  18 |

|  3 | mary |  20 |

|  4 | lucy |  18 |

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

4 rows in set (0.00 sec)从上面可以看出，T1设置了读锁，T2还可以继续读取

事务T2：

mysql> update student set name = 'bob' where id = 2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 从上面可以看出，T1设置了读锁，T2不能进行写操作，写操作等待超时~