mysql中的事务

关于mysql的事务相关原理。

事务

事务是必须满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

事务的隔离级别

  • 未提交读(Read Uncommitted):一个事务还没提交时,它做的变更就能被别的事务看到。允许脏读,也就是可能读取到其他会话中未提交事务修改的数据

  • 提交读(Read Committed):一个事务提交之后,它做的变更才会被其他事务看到,即只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

  • 可重复读(Repeated Read):一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的,即在同一个事务内的查询都是与事务开始时刻一致的,是InnoDB默认的隔离级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻读

  • 可串行化(Serializable):对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read)【mysql默认的隔离级别】 不可能 不可能 可能
可串行化(Serializable) 不可能 不可能 不可能

实验一

脏读

脏读:脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中(没有commit),这时,另外一个事务也访问这个数据,然后使用了这个数据。主要是指update操作造成的修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
// 终端1
mysql> use sunnietest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED | READ-UNCOMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端2:修改终端2当前回话的的隔离级别成未提交读,并修改数据,未commit
mysql> use sunnietest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED | READ-UNCOMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

mysql> update transaction_test set score=60 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 终端1:能看见终端2修改后未commit的数据,出现脏读
mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 60 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

不可重复读

不可重复读:是指事务A先读一次数据,在这个事务还没有结束时,事务B也访问该同一数据并修改了数据之后commit了,然后事务A再读一次数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。主要是指update操作造成的修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
// 终端1:修改终端1当前会话的隔离级别成提交读
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED | READ-COMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端2:修改终端2当前会话的隔离级别也变成提交读,修改数据
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED | READ-COMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

mysql> update transaction_test set score=60 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 终端1:终端1看不见终端2修改但未commit的数据,未出现脏读
mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端2:commit
mysql> commit;
Query OK, 0 rows affected (0.16 sec)

// 终端1:终端1看见了终端2修改并commit的数据,在一个事务内出现了读到的数据不一致,出现了不可重复读
mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 60 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

幻读

http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html

幻读:第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。主要是指insert/delete操作造成的修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
// 终端1:设置当前会话的隔离级别是可重复读
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端2:设置当前会话的隔离级别是可重复读,并增加数据
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into transaction_test values(3, 40);
Query OK, 1 row affected (0.00 sec)

// 终端1:终端1看不见终端2增加但未commit的数据,未出现脏读
mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端2:commit
mysql> commit;
Query OK, 0 rows affected (0.05 sec)

// 终端1:终端1看不见终端2增加并commit的数据,在一个事务内读到的数据一致,未出现不可重复读
mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端1看不见终端2增加并commit的数据,但是插入的时候却提示已经存在了,像出现了幻觉,出现幻读
mysql> insert into transaction_test values(3, 40);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 看不见的数据但是却能修改
mysql> update transaction_test set score=0 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 修改后能看见了
mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
| 3 | 0 |
+----+-------+
3 rows in set (0.00 sec)

串行化不会出现幻读,增删改的时候根本不允许其他并行的事务存在:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
// 终端1
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| SERIALIZABLE | SERIALIZABLE |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

// 终端2
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| SERIALIZABLE | SERIALIZABLE |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from transaction_test;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 2 | 80 |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into transaction_test values(3, 40);
(卡住)

// 终端1:commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

// 终端2:终端1commit之后终端2开始运行,当一个事务有增删改操作,不能有其他事务并行存在
Query OK, 1 row affected (50.55 sec)

关于隔离级别的常用命令:

查看当前会话隔离级别

1
select @@transaction_isolation;

查看系统当前隔离级别

1
select @@global.transaction_isolation;

查询隔离级别

1
select @@global.transaction_isolation,@@transaction_isolation;

设置当前会话隔离级别

1
set session transaction isolation level repeatable read;

设置系统当前隔离级别

1
set global transaction isolation level repeatable read;

实验二

1
2
3
4
5
6
7
8
9
10
11
mysql> create database sunnietest;
Query OK, 1 row affected (0.05 sec)

mysql> use sunnietest;
Database changed

mysql> create table T(c int) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into T(c) values(1);
Query OK, 1 row affected (0.01 sec)

我们来看看在不同的隔离级别下,事务A会有哪些不同的返回结果,也就是图里面V1、V2、V3的返回值分别是什么。

未提交读

若隔离级别是“读未提交”, 则V1的值就是2。这时候事务B虽然还没有提交,但是结果已经被A看到了。因此,V2、V3也都是2。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
// 终端1
mysql> use sunnietest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED | READ-UNCOMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

// 终端2,修改c=1为c=2
mysql> use sunnietest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED | READ-UNCOMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> update T set c=2 where c=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 终端1,查询V1=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.01 sec)

// 终端2,commit
mysql> commit;
Query OK, 0 rows affected (0.10 sec)


// 终端1,V2=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

// V3=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

提交读

若隔离级别是“读提交”,则V1是1,V2的值是2。事务B的更新在提交后才能被A看到。所以, V3的值也是2。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
// 终端1
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED | READ-COMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

// 终端2
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED | READ-COMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> update T set c=2 where c=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 终端1,V1=1
mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

// 终端2,commit
mysql> commit;
Query OK, 0 rows affected (0.05 sec)

// 终端1,V2=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

// V3=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

可重复读

若隔离级别是“可重复读”,则V1、V2是1,V3是2。之所以V2还是1,遵循的就是这个要求:事务在执行期间看到的数据前后必须是一致的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
// 终端1
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

// 终端2
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> update T set c=2 where c=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 终端1,V1=1
mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

// 终端2
mysql> commit;
Query OK, 0 rows affected (0.07 sec)

// 终端1,V2=1
mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

// V3=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

可串行化

若隔离级别是“串行化”,则在事务B执行“将1改成2”的时候,会被锁住。直到事务A提交后,事务B才可以继续执行。所以从A的角度看, V1、V2值是1,V3的值是2。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
// 终端1
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| SERIALIZABLE | SERIALIZABLE |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

// 终端2
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| SERIALIZABLE | SERIALIZABLE |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> update T set c=2 where c=1;
(卡住)

// 终端1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

// 终端2,终端1commit提交事务之后终端2卡住之后的部分开始执行
Query OK, 1 row affected (21.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.18 sec)

// 终端1,V3=2
mysql> select * from T;
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。在“读提交”隔离级别下,这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有视图概念;而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到在不同的隔离级别下,数据库行为是有所不同的。Oracle数据库的默认隔离级别其实就是“读提交”,因此对于一些从Oracle迁移到MySQL的应用,为保证数据库隔离级别的一致,你一定要记得将MySQL的隔离级别设置为“读提交”。

mysql的事务原理

事务就是要保证一组数据库操作,要么全部成功,要么全部失败。在MySQL中,事务支持是在引擎层实现的。你现在知道,MySQL是一个支持多引擎的系统,但并不是所有的引擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务,这也是MyISAM被InnoDB取代的重要原因之一。

你隔离得越严实,效率就会越低。因此很多时候,我们都要在二者之间寻找一个平衡点。

存在即合理,哪个隔离级别都有它自己的使用场景,你要根据自己的业务情况来定。我想你可能会问那什么时候需要“可重复读”的场景呢?我们来看一个数据校对逻辑的案例。

假设你在管理一个个人银行账户表。一个表存了每个月月底的余额,一个表存了账单明细。这时候你要做数据校对,也就是判断上个月的余额和当前余额的差额,是否与本月的账单明细一致。你一定希望在校对过程中,即使有用户发生了一笔新的交易,也不影响你的校对结果。

这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的,不受其他事务更新的影响。

事务隔离的实现

事务隔离具体是怎么实现的,这里我们展开说明“可重复读”。

在MySQL中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。

假设一个值从1被按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录。

当前值是4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的read-view。如图中看到的,在视图A、B、C里面,这一个记录的值分别是1、2、4,同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)。对于read-view A,要得到1,就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现,即使现在有另外一个事务正在将4改成5,这个事务跟read-view A、B、C对应的事务是不会冲突的。

你一定会问,回滚日志总不能一直保留吧,什么时候删除呢?答案是,在不需要的时候才删除。也就是说,系统会判断,当没有事务再需要用到这些回滚日志时,回滚日志会被删除。

什么时候才不需要了呢?就是当系统里没有比这个回滚日志更早的read-view的时候。

基于上面的说明,我们来讨论一下为什么建议你尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。

在MySQL 5.5及以前的版本,回滚日志是跟数据字典一起放在ibdata文件里的,即使长事务最终提交,回滚段被清理,文件也不会变小。我见过数据只有20GB,而回滚段有200GB的库。最终只好为了清理回滚段,重建整个库。

除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库,这个我们会在后面讲锁的时候展开。

事务的启动方式

如前面所述,长事务有这些潜在风险,我当然是建议你尽量避免。其实很多时候业务开发同学并不是有意使用长事务,通常是由于误用所致。MySQL的事务启动方式有以下几种:

  1. 显式启动事务语句, begin 或 start transaction。配套的提交语句是commit,回滚语句是rollback。
  2. set autocommit=0,这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句,或者断开连接。

有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务。

因此,我会建议你总是使用set autocommit=1, 通过显式语句的方式来启动事务。

但是有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务,第二种方式每个事务在开始时都不需要主动执行一次 “begin”,减少了语句的交互次数。如果你也有这个顾虑,我建议你使用commit work and chain语法。

在autocommit为1的情况下,用begin显式启动的事务,如果执行commit则提交事务。如果执行 commit work and chain,则是提交事务并自动启动下一个事务,这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在information_schema库的innodb_trx这个表中查询长事务,比如下面这个语句,用于查找持续时间超过60s的事务。

1
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

思考题

如果你是业务开发负责人同时也是数据库负责人,你会有什么方案来避免长事务对业务的影响?

这个问题,我们可以从应用开发端和数据库端来看。

首先,从应用开发端来看:

  1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展,把MySQL的general_log开起来,然后随便跑一个业务逻辑,通过general_log的日志来确认。一般框架如果会设置这个值,也就会提供参数来控制行为,你的目标就是把它改成1。
  2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要,但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候,根据业务本身的预估,通过SET MAX_EXECUTION_TIME命令,来控制每个语句执行的最长时间,避免单个语句意外执行太长时间。(为什么会意外?在后续的文章中会提到这类案例)

其次,从数据库端来看:

  1. 监控 information_schema.Innodb_trx表,设置长事务阈值,超过就报警/或者kill;
  2. Percona的pt-kill这个工具不错,推荐使用;
  3. 在业务功能测试阶段要求输出所有的general_log,分析日志行为提前发现问题;
  4. 如果使用的是MySQL 5.6或者更新版本,把innodb_undo_tablespaces设置成2(或更大的值)。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。
谢谢小天使请我吃糖果
0%