Linux - Mysql 事务

#事务分四个级别，分别为
1> 读未提交，READ-UNCOMMENTTED
2> 读已提交，READ-COMMITTED
3> 已重复读,  REPEATABLE-READ
4> 串行读，SERIALIZABLE

# 测试READ-UNCOMMENTTED

09:24:24(root@localhost) [test1]> set global transaction_isolation="READ-UNCOMMITTED";
Query OK, 0 rows affected (0.00 sec)

09:32:37(root@localhost) [(none)]> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

# 开启两个屏幕，并开启两个事务A,B

# 事务开始之前表student数据
09:28:50(root@localhost) [test1]> select * from student;
+----+--------+------+--------+-------+
| id | name   | age  | gender | class |
+----+--------+------+--------+-------+
|  1 | noise  |   18 | F      |     2 |
|  2 | noise2 |   19 | M      |     3 |
|  3 | noise3 |   20 | M      |     4 |
|  4 | noise4 |   21 | M      |     5 |
|  5 | noise5 |   22 | M      |     6 |
+----+--------+------+--------+-------+

#事务A对表student进行插入
09:34:27(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)
09:35:56(root@localhost) [test1]> insert student (name,age,class) value ("noise5",23,7);
Query OK, 1 row affected (0.00 sec)
09:36:18(root@localhost) [test1]> select * from student;
+----+--------+------+--------+-------+
| id | name   | age  | gender | class |
+----+--------+------+--------+-------+
|  1 | noise  |   18 | F      |     2 |
|  2 | noise2 |   19 | M      |     3 |
|  3 | noise3 |   20 | M      |     4 |
|  4 | noise4 |   21 | M      |     5 |
|  5 | noise5 |   22 | M      |     6 |
|  7 | noise5 |   23 | M      |     7 |
+----+--------+------+--------+-------+
6 rows in set (0.00 sec)

#事务B进行查询
09:27:33(root@localhost) [test1]> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

09:36:40(root@localhost) [test1]> select * from student;
+----+--------+------+--------+-------+
| id | name   | age  | gender | class |
+----+--------+------+--------+-------+
|  1 | noise  |   18 | F      |     2 |
|  2 | noise2 |   19 | M      |     3 |
|  3 | noise3 |   20 | M      |     4 |
|  4 | noise4 |   21 | M      |     5 |
|  5 | noise5 |   22 | M      |     6 |
|  7 | noise5 |   23 | M      |     7 |
+----+--------+------+--------+-------+
6 rows in set (0.00 sec)

#READ-UNCOMMITTED: 此时事务A未提交，但是事务B已经能查询到。即为脏读。

# 测试READ-COMMENTTED

09:40:05(root@localhost) [test1]> set global transaction_isolation="READ-COMMITTED";
Query OK, 0 rows affected (0.00 sec)

#更改完毕，请退出重新进入mysql.

09:45:02(root@localhost) [(none)]> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 row in set (0.00 sec)

#事务A--------->END，事务B---------->END
#事务C----------------------------->END

 

# 事务A

09:47:15(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

09:47:22(root@localhost) [test1]> insert student (name,age,class) value("noise10",10,10)
-> ;
Query OK, 1 row affected (0.00 sec)

09:49:01(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
+----+---------+------+--------+-------+
7 rows in set (0.00 sec)

09:49:13(root@localhost) [test1]> commit;
Query OK, 0 rows affected (0.00 sec)

# 事务B

09:50:11(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

09:50:34(root@localhost) [test1]> insert student (name,age,class) value("noise11",11,11);
Query OK, 1 row affected (0.00 sec)

09:50:47(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
+----+---------+------+--------+-------+
8 rows in set (0.00 sec)

09:50:49(root@localhost) [test1]> commit;
Query OK, 0 rows affected (0.00 sec)

 

# 事务C

09:47:40(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

# 在事务A插入以后，未提交，查询不到当前插入的record

09:49:16(root@localhost) [test1]> select * from student;
+----+--------+------+--------+-------+
| id | name | age | gender | class |
+----+--------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
+----+--------+------+--------+-------+
6 rows in set (0.00 sec)

# 在事务A提交以后，查询到刚刚插入的record

09:49:27(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
+----+---------+------+--------+-------+
7 rows in set (0.00 sec)

# 事务B插入record以后未提交，事务C查询表数据不变

09:50:14(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
+----+---------+------+--------+-------+
7 rows in set (0.01 sec)

# 事务B提交以后，事务C查询表数据发生变化

09:51:29(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
+----+---------+------+--------+-------+
8 rows in set (0.00 sec)

# 事务C提交
09:51:59(root@localhost) [test1]> commit;
Query OK, 0 rows affected (0.00 sec)

 

# READ-COMMITTED: 每次读取的数据都不太一样，会造成不可重复读。

 

# 测试REPEATABLE-READ

09:58:24(root@localhost) [test1]> set global transaction_isolation="REPEATABLE-READ";
Query OK, 0 rows affected (0.00 sec)

09:58:50(root@localhost) [test1]> exit

09:59:19(root@localhost) [test1]> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)

 

#事务A--------->END，事务B---------->END
#事务C----------------------------->END

# 事务A

09:59:30(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

10:01:37(root@localhost) [test1]> insert student (name,age,class) value ("s1",20,20);
Query OK, 1 row affected (0.00 sec)

10:03:30(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
| 10 | s1 | 20 | M | 20 |
+----+---------+------+--------+-------+
9 rows in set (0.00 sec)

10:03:37(root@localhost) [test1]> commit;
Query OK, 0 rows affected (0.00 sec)

 

# 事务B

10:04:08(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

10:04:38(root@localhost) [test1]> insert student (name,age,class) value ("s2",21,21);
Query OK, 1 row affected (0.00 sec)

10:04:52(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
| 10 | s1 | 20 | M | 20 |
| 11 | s2 | 21 | M | 21 |
+----+---------+------+--------+-------+
10 rows in set (0.00 sec)

10:04:55(root@localhost) [test1]> commit;
Query OK, 0 rows affected (0.00 sec)

# 事务C

10:00:09(root@localhost) [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

 

# 事务A插入record以后未提交，事务C查询表数据不变

10:01:41(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
+----+---------+------+--------+-------+
8 rows in set (0.00 sec)

# 事务A提交以后，事务C查询表数据不变

10:03:54(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
+----+---------+------+--------+-------+
8 rows in set (0.00 sec)

# 事务B插入record以后未提交，事务C查询表数据不变

10:04:59(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
+----+---------+------+--------+-------+
8 rows in set (0.00 sec)

# 事务B提交以后，事务C查询表数据不变

10:05:45(root@localhost) [test1]> select * from student;
+----+---------+------+--------+-------+
| id | name | age | gender | class |
+----+---------+------+--------+-------+
| 1 | noise | 18 | F | 2 |
| 2 | noise2 | 19 | M | 3 |
| 3 | noise3 | 20 | M | 4 |
| 4 | noise4 | 21 | M | 5 |
| 5 | noise5 | 22 | M | 6 |
| 7 | noise5 | 23 | M | 7 |
| 8 | noise10 | 10 | M | 10 |
| 9 | noise11 | 11 | M | 11 |
+----+---------+------+--------+-------+
8 rows in set (0.00 sec)

10:06:02(root@localhost) [test1]> commit;
Query OK, 0 rows affected (0.00 sec)

# 由此可见，REPEATABLE-READ 会产生幻读，无论其他事务如何更改数据，都是会查询事务之前的数据。

# SERIALIZATABLE并发效能最低，不建议使用