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 ) 不可能 不可能 不可能

举例的表:

id score
5 100
6 100
7 100
8 100
9 100

脏读

脏读:脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中(没有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
// 初始表的数据
mysql> select * from transaction;
+------+-------+
| id | score |
+------+-------+
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 10 | 99 |
| 11 | 98 |
| 12 | 70 |
| 13 | 60 |
+------+-------+
9 rows in set (0.00 sec)

// 终端1:修改终端1当前回话的的隔离级别成未提交读
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.01 sec)

// 终端2:修改终端2当前回话的的隔离级别成未提交读
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

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

// 终端1:第一次读id=13的score
mysql> select score from transaction where id =13;
+-------+
| score |
+-------+
| 60 |
+-------+
1 row in set (0.00 sec)

// 终端2:update数据,没有commit
mysql> update transaction set score=100 where id=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// 终端1:终端1能看见终端2update但未commit的数据
mysql> select score from transaction where id=13;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row 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
// 初始表的数据
mysql> select * from transaction;
+------+-------+
| id | score |
+------+-------+
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 10 | 99 |
| 11 | 98 |
| 12 | 70 |
| 13 | 60 |
+------+-------+
9 rows in set (0.00 sec)

// 终端1:修改终端1当前会话的隔离级别成提交读
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

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

// 终端2:修改终端2当前会话的隔离级别也变成提交读
mysql> begin;
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 @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 row in set (0.00 sec)

// 终端1:第一次读id=13的score
mysql> select score from transaction where id=13;
+-------+
| score |
+-------+
| 60 |
+-------+
1 row in set (0.00 sec)

// 终端2:修改id=13的score
mysql> update transaction set score=59 where id =13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

// 终端1:第二次读id=13的score出现不可重复读
mysql> select score from transaction where id=13;
+-------+
| score |
+-------+
| 59 |
+-------+
1 row in set (0.00 sec)

幻读

幻读:第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。主要是指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
// 初始数据表
mysql> select * from transaction;
+------+-------+
| id | score |
+------+-------+
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 10 | 99 |
| 11 | 98 |
| 12 | 70 |
| 13 | 100 |
+------+-------+
9 rows in set (0.00 sec)

// 终端1:设置当前会话的隔离级别是可重复读
mysql> begin;
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 @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)

// 终端2:设置当前会话的隔离级别是可重复读
mysql> begin;
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 @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)

// 终端1:查看当前所有数据
mysql> select * from transaction;
+------+-------+
| id | score |
+------+-------+
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 10 | 99 |
| 11 | 98 |
| 12 | 70 |
| 13 | 100 |
+------+-------+
9 rows in set (0.00 sec)

// 终端2:新插入一行数据
mysql> insert into transaction values(14,80);
Query OK, 1 row affected (0.00 sec)

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

// 终端1:再次查看所有数据,发现了一条刚刚没有的数据,像出现了幻觉一样
mysql> select * from transaction;
+------+-------+
| id | score |
+------+-------+
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 10 | 99 |
| 11 | 98 |
| 12 | 70 |
| 13 | 100 |
| 14 | 80 |
+------+-------+
10 rows in set (0.00 sec)

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

查看当前会话隔离级别

1
select @@transaction_isolation;

查看系统当前隔离级别

1
select @@global.transaction_isolation;

设置当前会话隔离级别

1
set session transaction isolation level repeatable read;

设置系统当前隔离级别

1
set global transaction isolation level repeatable read;

锁的分类

  • 按操作划分,可分为DML锁DDL锁

    DML锁(data locks,数据锁),用于保护数据的完整性,其中包括行级锁(Row Locks (TX锁))、表级锁(table lock(TM锁))。 DDL锁(dictionary locks,数据字典锁),用于保护数据库对象的结构,如表、索引等的结构定义。其中包括排他DDL锁(Exclusive DDL lock)、共享DDL锁(Share DDL lock)、可中断解析锁(Breakable parse locks)

  • 按锁的粒度划分,可分为行级锁(分为共享锁和排他锁)表级锁页级锁

    MyISAM和MEMORY采用表级锁(table-level locking)

    BDB采用页面锁(page-level locking)或表级锁,默认为页级锁

    InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁(只有通过索引条件检索数据时使用行级锁,因为行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁把整张表锁住)

    从行级锁->页级锁->表级锁,锁定粒度小->大,开销大->小,加锁慢->快,发生锁冲突的概率低->高,并发度高->低,行级锁和页级锁会出现死锁,表级锁不会出现死锁。

  • 按锁级别划分,可分为共享锁(读锁)排他锁(写锁)

    1
    2
    3
    4
    // 加共享锁
    SELECT ... LOCK IN SHARE MODE;
    // 加排他锁
    SELECT ... FOR UPDATE;

    在查询语句后面增加FOR UPDATE,mysql会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。

  • 按加锁方式划分,可分为自动锁显示锁

  • 按使用方式划分,可分为乐观锁悲观锁

    悲观锁通过排他锁实现;乐观锁通过在提交时与第一次取出来的数据版本标识进行比对实现,实现数据版本有两种方式,第一种是使用版本号,第二种是使用时间戳

谢谢小天使请我吃糖果
0%