第六章 事务和锁机制
1.简介
MySQL作为多用户的数据库管理系统,可以允许多个用户同时登录数据库进行操作。当不同的用户访问同一份数据时,在一个用户更改数据的过程中可能会其他用户同时发起更改请求,这样不仅会造成数据的不准确,而且会导致服务器死机。为了解决这一问题,使数据可以从一个一致的状态变为另一个一致的状态,MySQL在设计的过程中加入了事务这一概念,以此来保证数据的准确性和一致性。

2.事务管理
事务处理机制在程序开发和后期的系统运行维护中起着非常重要的作用,既保证了数据的准确,也使得整个数据系统更加安全。

3.事务的概念
在现实生活中,人们通过银行互相转账和汇款,从数据的角度来看,这实际上就是数据库中两个不同账户之间的数据操作。例如,用户A向用户B转账了1000元,则A账户的余额减去1000元,B账户的余额加上1000元,整个过程需要使用两条SQL语句来完成操作,若其中一条语句出现异常没有被执行,则会导致两个账户的金额不同步,从而使数据出现错误。为了避免上述情况的发生,MySQL中可以通过开启事务来进行数据操作。
事务实际上指的是数据库中的一个操作序列,由一组DML语句(INSERT、DELETE、UPDATE)组成。这些语句不可分割,只有在所有的SQL语句都执行成功后,整个事务引发的操作才会更新到数据库中,如果有至少一条语句执行失败,所有操作都会被取消。以用户转账为例,将需要执行的语句定义为事务,具体的转账流程如图所示。
图片 1.png
用户转账过程中,只有事务执行成功后数据才会变更,如果事务执行失败,数据库中的值将不会变更。

4.事务的创建和回滚
在默认设置下,MySQL中的事务为自动提交模式,每一条语句都处于一个单独的事务中,在这条语句执行完毕后,如果执行成功则隐式提交事务,如果执行失败则隐式回滚事务。正常的事务管理是一组相关的操作处于一个事务之中,因此在执行正常的事务时,可以使用相关指令关闭数据库的自动提交模式,随后用户将一直处于某个事务中,直到用户执行一条COMMIT(提交)或者ROLLBACK(回滚)命令后才会结束当前事务。如果用户不想关闭事务的自动提交模式,可以使用BEGIN或者START TRANSACTION命令开启事务,事务开启后便可以执行相关的事务语句,事务提交后自动回到自动提交模式。另外,事务的回滚操作只能撤销所有未提交的修改,对已经提交的事务不能使用ROLLBACK命令进行回滚。接下来将通过具体案例演示转账汇款的事务操作。创建一个账户表account,表结构如表所示。
图片 2.png
根据表中提供的参数创建account表,并向其中插入测试数据,具体的SQL语句如下所示。
MariaDB [yun]> create table account(id int primary key,name varchar(30),money float);
Query OK, 0 rows affected (0.014 sec)
MariaDB [yun]> show tables;
+---------------+
| Tables_in_yun |
+---------------+
| account |
+---------------+
1 row in set (0.001 sec)
MariaDB [yun]> insert into account values(1,'A',1000),(2,'B',1000),(3,'C',1000);
Query OK, 3 rows affected (0.004 sec)
Records: 3 Duplicates: 0 Warnings: 0

从上方代码的执行结果可以看出,测试数据插入成功。接下来使用SELECT命令查看账户表account。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 1000 |
| 2 | B | 1000 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.001 sec)
MariaDB [yun]>
账户表account中存在 A、B和C三个账户, 每个账户的存款金额都为1000。 接下来,使用SHOW VARIABLES句查看MySQL是否为事务自动提交模式,具体的SQL语句如下所示。
MariaDB [yun]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.002 sec)
MariaDB [yun]>
从上方代码的执行结果可看出,MySQL自动提交模式处于开启状态。因为此处事务需要手动提交,所以要关闭自动提交模式。
MariaDB [yun]> set autocommit=0;
Query OK, 0 rows affected (0.001 sec)
MariaDB [yun]>
set autocommit=0中的0代表OFF状态(反之1代表ON状态)。再次通过SHOW VARIABLES语句查看自动提交模式是否关闭。
MariaDB [yun]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.002 sec)
MariaDB [yun]>
从执行结果可看出,MySQL的自动提交模式已关闭。下面将通过具体的实例演示事务的操作过程。
通过事务,实现账户A转账给账户B,100元钱的操作。
MariaDB [yun]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [yun]> update account set money=money-100 where name='A';
Query OK, 1 row affected (0.004 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]> update account set money=money+100 where name='B';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]> commit;
Query OK, 0 rows affected (0.001 sec)
MariaDB [yun]>
以上执行结果证明汇款成功并执行了更新操作,A账户的余额减少了100元,B账户的余额增加了100元,此时可查看表中数据进行验证。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.000 sec)
MariaDB [yun]>
从以上执行结果可看出,通过事务操作实现了转账。需要注意,如果在执行转账操作过程中数据库出现故障,为了保证事务的同步性,则事务不会提交。接下来通过具体实例演示这种情况。
通过事务操作,实现账户A转账给账户C,100元钱,在账户A的数据操作完成后,关闭数据库客户端,以此来模拟数据库死机,并查看数据是否被修改。
MariaDB [yun]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [yun]> update account set money=money-100 where name='A';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]>
从以上执行结果可看出,账户A的余额减少了100元。使用SELECT命令查看表中数据,验证数据是否被修改
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.001 sec)
MariaDB [yun]>
从以上执行结果可看出,账户A的余额从900元变为了800元,账户A的转账操作完成。此时关闭MySQL的客户端,并重新进入,再次查看account表中的数据。
MariaDB [yun]> exit
Bye
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use yun;
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
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.001 sec)
MariaDB [yun]>
从执行结果可以看出,重新登录数据库后,account表中账户A的余额又恢复为900元,这是因为在进行事务操作时,未在最后提交事务,转账操作并没有全部完成,为了保证事务的同步性,系统对数据操作进行了回退,因此,用户可以利用这种隐式的方式实现未提交事务的数据的回滚。上文中也提到,在实际的事务操作中,如果发现某些操作是不合理的,就可以通过ROLLBACK命令回滚未提交的事务,这种方式并不需要退出数据库。接下来通过具体实例演示使用ROLLBACK进行事务的回滚。
通过事务操作,实现账户B转账给账户C,100元,在转账操作完成后,使用ROLLBACK语句进行转账回滚操作。
MariaDB [yun]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [yun]> update account set money=money-100 where name='B';
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]> update account set money=money+100 where name='C';
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]>
从上方代码的执行结果可以看出,账户B和账户C之间进行了转账操作。下面查看账户表验证数据是否被修改。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1000 |
| 3 | C | 1100 |
+----+------+-------+
3 rows in set (0.000 sec)
MariaDB [yun]>
可以看出,账户C的余额增加了100元。因为还未提交事务,此时使用ROLLBACK命令将事务操作进行回滚。
MariaDB [yun]> rollback;
Query OK, 0 rows affected (0.003 sec)
MariaDB [yun]>
回滚成功后,查看账户表中的数据是否发生改变,SQL语句如下所示。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.001 sec)
MariaDB [yun]>
从以上执行结果可看出,账户B和账户C的金额又回滚到了转账操作之前的数值。

5.并发与并行
在现实生活中经常会听到“高并发”“高可用”这种词语,在MySQL系统中,系统是如何实现并发控制的呢?并发和并行又有什么样的区别呢?下面带领读者详细学习并发控制与并行控制的相关知识。
1.并发控制
并发(Concurrency)控制是指通过把时间分成若干段,让多个线程快速交替轮换执行,使得在宏观上具有多个线程同时执行效果的控制方法,如图所示。
图片 3.png
具体来说,当有多个线程时,如果系统只有一个CPU,则根本不可能真正同时执行一个以上的线程,只能把CPU运行时间划分成若干个时间段,再将时间段分配给各个线程,在一个时间段的线程代码运行时,其他线程处于挂起状态,这种方式被称为并发。并发控制的目标是充分利用处理器的每一个核,以达到最高的处理性能。
2.并行
并行(Parallel)指在同一时刻有多条指令在多个CPU上同时执行,无论从微观还是从宏观来看,多个线程都是同时执行的,如图所示。
图片 4.png
当系统有一个以上CPU时,线程的操作有可能非并发。当一个CPU执行一个线程时,另一个CPU可以执行另一个线程,两个线程互不抢占CPU资源,可以同时进行,这种方式我们称之为并行。
并行在多处理器系统中存在,而并发可以在单处理器和多处理器系统中存在,并发能够在单处理器系统中存在是因为并发是并行的假象。并行要求程序能够同时执行多个操作,而并发只要求程序“假装”同时执行多个操作。

6.事务的ACID特性
前面以用户转账为例介绍了事务的基本用法,读者已了解事务的基本属性。事务是一个整体,相互不影响,只有执行成功后才会对数据进行永久性的修改。在MySQL中,事务具有4个特性,如表所示。
图片 5.png
以用户转账为例,假设账户A和账户B的余额都为1000元,如果账户A转账给账户B,100元,
在MySQL中需要进行以下6个步骤。
第一步:从账户A中读取余额,为1000。
第二步:账户A的余额减去100。
第三步:账户A的余额写入,为900。
第四步:从账户B中读取余额,为1000。
第五步:账户B的余额加上100。
第六步:账户B的余额写入,为1100。
上述步骤中,事务的ACID特性表现如下。
(1)原子性:保证所有步骤要么都执行,要么都不执行。一旦在执行某一步骤的过程中出现问题,就需要执行回滚操作。例如,执行到第五步时,账户B突然不可用(比如被注销),那么前面的所有操作都应该回滚到执行事务之前的状态。
(2)一致性:在转账之前,账户A和账户B中共有1000+1000=2000 元。在转账之后,账户A和账户B中共有900+1100=2000元。也就是说,在执行该事务操作之后,数据从一个状态改变为另外一个状态。同时,一致性还能保证账户余额不会变成负数等。
(3)隔离性:在账户A向账户B转账的整个过程中,只要事务还没有提交,查询账户A和账户B时,两个账户中钱的数量都不会有变化。如果在账户A给账户B转账的同时,有另外一个事务执行了账户C给账户B转账的操作,那么当两个事务都结束时,账户B里面的钱应该是账户A转给账户B的钱加上账户C转给账户B的钱,再加上账户B原有的钱。
(4)持久性:一旦转账成功(事务提交),两个账户中的钱就会真正发生变化(会将数据写入数据库做持久化保存)。
事务的ACID特性对数据进行了严格的定义,在实际的开发操作中,也应该遵循这4个特性,以此来保证数据的安全。

7.事务的隔离级别
MySQL作为多线程并发访问的数据库,其明显的特点是资源可以被多个用户共享访问。当多个用户(多个事务)同时访问相同的数据库资源时,如果各事务之间没有采取必要的隔离措施,可能会出现以下几种不确定的情况。
(1)脏读
一个事务读取了某行数据,而另外一个事务已经更新了此行的数据,但没有及时提交,例如,事务A读取了事务B更新的数据,随后事务B因为某些原因进行了回滚操作,那么事务A读取到的数据就是脏数据。这种情况是非常危险的,很可能造成所有的操作都被回滚。
(2)不可重复读
不可重复读是指一个事务的修改和提交造成另一个事务在同一范围内的两次相同查询的返回结果不同。例如,事务A需要多次读取同一个数据,在事务A还没有结束时,事务B访问并修改了该数据,那么,事务A两次读取到的数据就可能不一致,因此称为不可重复读,即原始数据不能重复读。
(3)幻读
幻读是指一个线程中的事务读取到了另外一个线程中提交的INSERT数据。例如,用户A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是用户B此时插入了一条具体分数的记录,用户A修改完成后发现还有一条记录没有改过来,就好像发生了幻觉一样,因此称这种情况为幻读或者虚读。
为了避免上述3种情况的发生,MySQL中为事务定义了不同的隔离级别,以此来保证数据的稳定性。事务的隔离级别由低到高可分为Read Uncommitted、Read Committed、Repeatable Read、Serializable,如表所示。
图片 6.png
事务的隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也会相应增大。另外,不同的隔离级别可能会造成不同的并发异常,如表所示。
图片 7.png
在 MySQL 中,可以使用相关语句查看当前会话的隔离级别,具体如下所示。
select @@tx_isolation;
同样,用户也可以使用 SET 语句设置当前会话的隔离级别,具体的语法格式如下所示。
set session transaction isolation level {read uncommitted | read committed | repeatable read | serializable}
以上语法格式中,SESSION代表设置的是当前会话的隔离级别,LEVEL后跟随着4个可选参数,分别对应4个隔离级别。接下来将通过具体案例分别演示4个隔离级别可能引起的并发问题。
1.脏读问题
当事务的隔离级别为Read Uncommitted(读未提交)时,可能出现数据的脏读问题,即一个事务读取了另一个事务未提交的数据。在演示过程中将打开两个客户端会话窗口,以此来模仿不同的事务操作同一数据的场景,叙述中将这两个客户端分别简称为客户端A和客户端 \B。另外,上文中已经提到,MySQL默认的隔离级别为Repeatable Read(可重复读),这里对A、B客户端默认的隔离级别也未作修改。接下来将通过不断改变客户端A的隔离级别,同时在客户端B修改数据,来演示各个隔离级别出现的问题。
首先,将客户端A的隔离级别设置为Read Uncommitted(读未提交),SQL语句如下所示。
MariaDB [yun]> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.001 sec)
MariaDB [yun]>
以上执行结果证明客户端 A的隔离级别设置为了Read Uncommitted(读未提交)。在客户端A中查询account表的数据,SQL语句如下所示。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.000 sec)
MariaDB [yun]>
然后在客户端B中进行事务操作。在开启事务后,执行账户A转账给账户C,100元的数据操作,但不进行事务的提交,SQL语句如下所示。
MariaDB [yun]> start transaction;
Query OK, 0 rows affected (0.000 sec)
MariaDB [yun]> update account set money=money-100 where name='A';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]> update account set money=money-100 where name='C';
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]>
以上执行结果证明账户A成功转账给账户C,100元。此时通过客户端A查看account表中的数据,SQL语句如下所示。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 900 |
+----+------+-------+
3 rows in set (0.000 sec)
MariaDB [yun]>
从以上执行结果可看出,在客户端A中查询account表中的数据,发现账户A已经给账户C转账了100元,但此时客户端B中的事务还没有提交,客户端A读取到了客户端B还未提交事务修改的数据,即脏数据。这种情况很容易造成数据的混乱,使数据的一致性遭到破坏。
此时,在客户端B对事务进行回滚操作,具体的SQL语句如下所示。
MariaDB [yun]> rollback;
Query OK, 0 rows affected (0.003 sec)
MariaDB [yun]>
从以上执行结果可以看出,客户端B成功进行了事务回滚。此时通过客户端A再次查询account表中的数据,SQL语句如下所示。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.001 sec)
MariaDB [yun]>
从代码的执行结果可以看出,客户端A查询到了客户端B事务回滚后的数据。在实际应用中应该根据实际情况合理地使用Read Uncommitted(读未提交)隔离级别,以此来减少数据的脏读问题。
2.不可重复读问题
当事务的隔离级别设置为Read Committed(读已提交)时,一个事务在对数据进行查询的过程中可能会遇到其他事务对数据进行修改的情况,从而导致事务中的两次查询结果不一致,即出现数据的不可重复读问题。下面将对这种情况进行详细的演示。
首先,将客户端A的隔离级别设置为Read Committed(读已提交),SQL语句如下所示。
MariaDB [yun]> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.007 sec)
MariaDB [yun]>
从上方代码的执行结果可以看出,客户端A的隔离级别设置为了Read Committed(读已提交)。这时在客户端A中开启一个查询account表数据的事务,SQL语句如下所示。
MariaDB [yun]> start transaction;
Query OK, 0 rows affected (0.000 sec)
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 900 |
| 2 | B | 1100 |
| 3 | C | 1000 |
+----+------+-------+
3 rows in set (0.014 sec)
MariaDB [yun]>
然后在客户端B中进行事务操作。开启事务后,进行账户A转账给账户C,100元的数据操作并提交事务,SQL语句如下所示。
MariaDB [yun]> start transaction;
Query OK, 0 rows affected (0.003 sec)
MariaDB [yun]> update account set money=money-100 where name='A';
Query OK, 1 row affected (0.008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]> update account set money=money+100 where name='C';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [yun]> commit;
Query OK, 0 rows affected (0.005 sec)
MariaDB [yun]>
从以上执行结果可看出,客户端B中的事务操作完成,账户A给账户C转账了100元,此时在未提交事务的客户端A中再次查询account表中的数据信息,SQL语句如下所示。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 1100 |
+----+------+-------+
3 rows in set (0.001 sec)
MariaDB [yun]>
从以上代码的执行结果可看出,从客户端A中查询出了客户端B修改后的数据,也就是说客户端A在同一个事务中分别在不同时间段查询同一个表的结果不一致,即数据的不可重复读问题。
3.幻读问题
幻读问题的出现与不可重复读问题的出现原因类似,当事务的隔离级别为Repeatable Read(可重复读)时,一个事务在查询过程中,其他事务可能会对数据继续更新操作,从而导致两次查询的数据条数不一致。下面将针对事务中出现幻读的情况进行详细的演示和说明。
首先,将客户端A的隔离级别设置为Repeatable Read(可重复读),SQL语句如下所示。
MariaDB [yun]> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.001 sec)
MariaDB [yun]>
从上方代码的执行结果可以看出,客户端A的隔离级别成功设置为了Repeatable Read(可重复读)。接下来,在客户端A中开启一个查询account表数据的事务,SQL语句如下所示。
MariaDB [yun]> start transaction;
Query OK, 0 rows affected (0.000 sec)
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 1100 |
+----+------+-------+
3 rows in set (0.000 sec)
MariaDB [yun]>
然后在客户端B中进行更新操作,添加一个余额为500元的账户D,SQL语句如下所示。
MariaDB [yun]> insert into account values(4,'D',500);
Query OK, 1 row affected (0.005 sec)
MariaDB [yun]>
从以上执行结果可看出,客户端B中的数据添加操作执行成功,此时在未提交事务的客户端A中查询account表中的数据,SQL语句如下所示。
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 1100 |
| 4 | D | 500 |
+----+------+-------+
4 rows in set (0.000 sec)
MariaDB [yun]>
从执行结果可以看出,在客户端A中查询account表的数据并没有出现幻读的问题,这与之前预期的结果并不相同。出现这种情况的主要原因是MySQL的存储引擎通过多版本并发控制(MultiVersion Concurrency Control,MVCC)机制解决了数据幻读的问题。因此,当MySQL的隔离级别为Repeatable Read(可重复读)时,是可以避免幻读问题的出现的。
4.可串行化
Serializable(可串行化)为最高的事务隔离级别,该级别会在每一行读取的数据上都加锁,从而使各事务之间不会出现相互冲突,但这种方式会导致系统资源占用过多,出现大量的超时现象。下面将对这种隔离级别进行演示和说明。首先,将客户端A的隔离级别设置为Serializable(可串行化),SQL语句如下所示。
MariaDB [yun]> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.001 sec)
MariaDB [yun]>
然后在客户端A中开启一个查询account表数据的事务,具体的SQL语句如下所示。
MariaDB [yun]> start transaction;
Query OK, 0 rows affected (0.003 sec)
MariaDB [yun]> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 1100 |
| 4 | D | 500 |
+----+------+-------+
4 rows in set (0.001 sec)
MariaDB [yun]>
接着在客户端B中进行更新操作,添加一个余额为800元的账户E,SQL语句如下所示。
MariaDB [yun]> insert into account values(5,'E',800);
Query OK, 1 row affected (0.002 sec)
MariaDB [yun]>
从执行结果可以看出操作超时,导致数据添加失败。造成这种现象的原因是此时客户端A的事务隔离级别为Serializable(可串行化),客户端A中的事务还没有提交,所以客户端B必须等到客户端A中的事务提交后,才可以进行添加数据的操作,当客户端A长时间没有提交事务时,客户端 B便会出现操作超时的错误。这种事务长时间的等待操作会占用很大一部分的系统资源,从而降低系统的性能。因此,在实际应用中很少使用Serializable(可串行化)这种隔离级别。

8.隔离级别的选取
事务的隔离级别越高越能保证数据的完整性和一致性,但是对系统并发性能的影响也越大。对于大多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed,这样可以有效避免数据的脏读,而且具有较好的并发性能。尽管这种级别会导致不可重复读、虚读这些并发问题,但在个别的应用场合中可采用悲观锁或乐观锁来控制。合理地选用不同的隔离等级可以在不同程度上避免前面所提及的在事务处理中所面临的各种问题,所以,数据库隔离级别的选取就显得尤为重要。在选取数据库的隔离级别时,读者可以参考以下几个原则。
(1)首先,需要排除数据脏读的影响,在多个事务之间要避免进行“非授权的读”操作。因为事务的回滚操作或失败将会影响其他的并发事务,第一个事务的回滚会完全将其他事务的操作清除,这可能导致数据库处在一个不一致的状态。
(2)其次,绝大部分应用都无须使用“序列化” 隔离,在实际的应用中,数据的幻读可以通过使用悲观锁这种强行使所有事务都序列化执行的方式来解决。
(3)对于大部分应用,可以优先考虑可重复读。这主要是因为所有的数据访问都是在统一的原子数据库事务中进行的,此隔离级别将消除一个事务在另外一个并发事务过程中覆盖数据的可能性。另外,在MySQL的InnoDB存储引擎中也可以加入MVCC等机制来解决数据的幻读问题。合理地选用隔离级别,不仅可以避免数据的混乱,还可以提高数据库系统在高访问时的健壮性。

9.锁机制
简单来说,数据库的锁机制主要是为了使用户对数据的访问变得有序,保证数据的一致性。锁机制是实现宏观上高并发最简单的方式,但从微观的角度来说,锁机制其实是读写串行化。

10.MySQL锁定机制简介
MySQL数据库中存在多种数据存储引擎,由于每种存储引擎所针对的应用场景不一样,内部的锁定机制也是为它们各自所面对的特定场景而优化设计的,所以各存储引擎的锁定机制之间本质上也存在较大的区别。MySQL中常见的InnoDB引擎支持行级锁,但有时也会升级为表级锁,而MyISAM引擎只支持表级锁。另外,有些存储引擎也支持页级锁(性能介于行级锁和表级锁之间,但不常用)。行级锁和表级锁的特点如下。表级锁的特点是开销小,加锁快,不会出现死锁。 但由于锁粒度大,发生锁冲突的概率较高,抗并发能力较低。行级锁的特点是开销大,加锁慢,会出现死锁的情况。由于锁粒度小,发生锁冲突的概率较低,抗并发能力较高。前面介绍事务时已经提到,多个事务同时访问同一数据会造成并发异常的问题。实际上,各种隔离级别也是靠锁机制实现的。接下来,将针对MySQL中常用的InnoDB引擎进行详细说明。

11.InnoDB锁类型
数据库系统中常见的锁按锁粒度可以划分为表级锁、行级锁和页级锁,行级锁中主要的锁类型有读锁、写锁,而表级别的锁有意向锁。下面将针对常见锁的工作机制进行详细的说明。
1.读锁
读锁又被称为共享锁(Shared Locks,简称为S)。S锁的粒度是行级或者元组级(多个行),多个不同的事务对一个资源共享一把锁。如果事务T1对行R加上S锁,则会产生以下情况。
(1)其他事务T2、T3、……Tn只能对行R加上S锁,不能再加上其的锁。
(2)被加上S锁的数据,用户只能进行读取,不能写入数据(包括修改和删除)。
(3)如果需要修改数据,必须等所有的共享锁释放完。
在MySQL中,设置共享锁的语法格式如下:
select * from 表名 where 条件 lock in share mode;
上方语法格式中,LOCK IN SHARE MODE表示为搜索到的行加上共享锁(S锁)。例如,在一个未结束的事务A中给一条数据加上S锁,如下所示。
MariaDB [yun]> begin;
Query OK, 0 rows affected (0.004 sec)
MariaDB [yun]> select * from test where id=1 lock in share mode;
+----+------+
| id | name |
+----+------+
| 1 | A |
+----+------+
1 row in set (0.001 sec)
MariaDB [yun]>
在事务B中为同样的数据加上共享锁,即可加锁成功。
MariaDB [yun]> select * from test where id=1 lock in share mode;
但是,如果事务A或者事务B更新该数据,则会提示锁等待的错误信息,如下所示。
MariaDB [yun]> update test set id=2 where id=1;
ERROR 1205 (HY000):Lock wait timeout execeeded;try restarting transaction
从上面代码的执行结果可以看出,当一个事务对数据加上S锁时,事务本身和其他事务只能读取该数据,但不能修改数据。
2.写锁
写锁又被称为排他锁(Exclusive Locks,简称为X)。X锁也是作用于行或者元组的。如果一个事务T1对行R加上了X锁,则会产生以下情况。
(1)事务T1可以对行R范围内的数据进行读取和写入操作(包括修改和删除)。
(2)其他事务都不能对行R施加任何类型的锁,而且无法进行增删改操作,直到事务T1在行R上的X锁被释放。在MySQL中设置X锁的语法格式如下
select * from 表名 where 条件 for update;
上方语法格式中,FOR UPDATE表示为指定的行加上排他锁(X锁)。例如,在一个未结束的事务A中给一条数据加上X锁,如下所示。
MariaDB [yun]> begin;
Query OK, 0 rows affected (0.001 sec)
MariaDB [yun]> select * from test where id=1 for update;
+----+------+
| id | name |
+----+------+
| 1 | A |
+----+------+
1 row in set (0.001 sec)
MariaDB [yun]>
在事务A中尝试更新被指定X锁的数据,SQL语句如下所示。
MariaDB [yun]> update test set id=2 where id=1;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 0 Changed: 0 Warnings: 0
MariaDB [yun]>
可以看出,事务A对该数据库进行了修改操作。接下来,在事务B中尝试查看该行数据。
MariaDB [yun]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
+----+------+
1 rows in set (0.001 sec)
MariaDB [yun]>
可以看出,事务B可以查看该数据。接下来在事务B中尝试修改这条数据,SQL语句如下所示。
MariaDB [yun]> update test set id=3 where id=1;
ERROR 1205 (HY000):Lock wait timeout execeeded;try restarting transaction
从上面代码的执行结果可以看出,事务B对加上X锁的数据修改失败。需要注意,排他锁指的是一个事务为一行数据加上排他锁后,其他事务不能再在其上加其他锁。MySQL的InnoDB引擎默认的修改数据语句UPDATE、DELETE、INSERT都会自动给涉及的数据加上排他锁,SELECT语句默认不会加任何锁。另外,加过排他锁的数据行在其他事务中也不能通过FOR UPDATE和LOCK INSHARE MODE的方式查询数据,但可以直接通过SELECT…FROM…查询数据,这是因为普通查询没有任何锁机制。
3.意向锁
在InnoDB引擎中,意向锁(Intention Locks)的粒度为表级。意向锁是数据库的自身行为,不需要人工干预,在事务结束后会自行解除。意向锁分为意向共享锁(IS)和意向排他锁(IX),其主要作用是提升存储引擎的性能。在InnoDB引擎中的S锁和X锁为行级锁,每当事务到来时,存储引擎需要遍历所有行的锁持有情况,这样会增加系统的性能损耗。因此MySQL数据库系统引入了意向锁,在检查行级锁之前会先检查意向锁是否存在,如果存在则阻塞线程。
总的来说,事务要获取表中某行的S锁必须要获取该表的IS锁;同样,事务要获取表中某行的X锁必须要获取表的IX锁。下面将通过演示对意向锁进行说明。
首先事务T1对test表中id=1的行加上S锁。
MariaDB [yun]> begin;
MariaDB [yun]> select * from test where id=1 lock in share mode;
这时,如果事务T2需要对test表中id > 0的行加上X锁,则系统会执行以下步骤。
(1)判断test表中是否有表级锁。
(2)判断test表中每一行是否有行级锁。
当数据量较大时(100 万~ 1000 万条数据),步骤(2)中的判断效率极低。引入意向锁后,步骤(2)就可以变为对意向锁的判断,即如果发现test表中有IS锁,则说明表中肯定 S锁。因此,事务T2申请X锁的请求将会被阻塞。加入意向锁后,系统将不需要再对全表中的数据进行判断,可大大提高判断效率。
4.间隙锁
间隙锁(Gap Lock)是InnoDB引擎在可重复读(Repeatable Read)的隔离级别下为了解决幻读和数据误删问题而引入的锁机制。当使用范围条件检索数据并请求共享或排他锁时,InnoDB引擎会给符合条件的已有数据记录的索引项加锁。键值在条件范围内但不存在的记录叫作“间隙(GAP)”,InnoDB引擎也会对这个“间隙”加锁。例如,在MySQL中存在一张test表,其中id为主键,name为非唯一索引,表中的数据如图所示。
图片 8.png
当事务T1执行范围性语句“select * from test where id > 0 and id < 5 for update;”检索时,会查询出图所示的数据。
图片 9.png
从图可以看出,表中并不存在 id 为 2 的数据,而这种不存在的数据被称为“间隙”, InnoDB引擎也会对这些“间隙”加锁。此时,如果事务 T2执行 INSERT 语句,插入一条 id 为 2 的数据,则需要等到事务 T1结束才可以插入成功;如果事务 T1长时间不结束,则会造成系统中出现死锁,降低数据库性能和数据安全性。
5.锁等待和死锁
锁等待是指在一个事务执行过程中,一个锁需要等到上一个事务的锁释放后才可以使用该资源。如果事务一直不释放,就需要持续等待下去,直到超过锁等待时间,此时系统会报出超时错误。MySQL中锁等待时间是通过innodb_lock_wait_timout参数控制的,该参数的单位为秒,具体SQL语句如下。
MariaDB [yun]> show variables like '%innodb_lock_wait%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.007 sec)
MariaDB [yun]>
可以看出,设置的等待时间为 50 秒。

12.锁监控与优化
在MySQL中可以通过SHOW FULL PROCESSLIST命令和SHOW ENGINE INNODB STATUS命令来监控事务中的锁情况。另外,也可以通过查询information schema库下的INNODB TRX、INNODB LOCKS和INNODB LOCK WAITS这3张表来获取更加详细的锁的信息。因为 InnoDB 存储引擎可以实现行级锁定,所以性能损耗会比表级锁定高。当系统并发量较高时,InnoDB引擎的整体性能会比MyISAM引擎更有优势。但是,如果对行级锁使用不当,也可能会造成InnoDB引擎的整体性能下降。在使用 InnoDB 引擎时,应该注意以下几点。
(1)合理设置索引,尽可能让所有的数据检索都通过索引来完成,从而避免InnoDB引擎因为无法通过索引加锁而升级为表级锁定的现象。
(2)减少基于范围的数据检索,避免因间隙锁带来的负面影响而锁定了不该锁定的记录。
(3)控制事务的大小,缩减锁定的资源量和锁定时间。
(4)在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL实现事务隔离级别的附加成本。
在同一个事务中,尽可能做到一次性锁定需要的所有资源,降低死锁发生的概率。另外,对于非常容易产生死锁的业务部分,可以尝试升级锁定粒度,通过表级别的锁定来降低死锁产生的概率。

MySQL索引和视图
1.创建表的同时创建普通索引department10
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database runtimeedu;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| runtimeedu         |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]> use runtimeedu;
Database changed
MariaDB [runtimeedu]> create table department10(dept_id int,dept_name varchar(30),comment
    ->  varchar(50),index index_dept_name (dept_name));
Query OK, 0 rows affected (0.004 sec)
MariaDB [runtimeedu]>
2.创建表的同时创建唯一索引department11
MariaDB [runtimeedu]> create table department11(dept_id int,dept_name varchar(30),comment
    ->  varchar(50),unique index index_dept_name (dept_name));
Query OK, 0 rows affected (0.005 sec)
MariaDB [runtimeedu]>
3.创建表的同时创建全文索引department12
MariaDB [runtimeedu]> create table department12(dept_id int,dept_name varchar(30),comment
    ->  varchar(50),log text,fulltext index index_log (log));
Query OK, 0 rows affected (0.013 sec)
MariaDB [runtimeedu]>
4.查看索引
MariaDB [runtimeedu]> show create table department10;
+--------------+-------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                               |
+--------------+-------------------------------------------------------------------------------+
| department10 | CREATE TABLE `department10` (
  `dept_id` int(11) DEFAULT NULL,
  `dept_name` varchar(30) DEFAULT NULL,
  `comment` varchar(50) DEFAULT NULL,
  KEY `index_dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [runtimeedu]>
5.删除索引
MariaDB [runtimeedu]> show create table department11;
+--------------+---------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                      |
+--------------+---------------------------------------------------------------------------------+
| department11 | CREATE TABLE `department11` (
  `dept_id` int(11) DEFAULT NULL,
  `dept_name` varchar(30) DEFAULT NULL,
  `comment` varchar(50) DEFAULT NULL,
  UNIQUE KEY `index_dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]> drop index index_dept_name on department11;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [runtimeedu]>
6.单表创建视图
MariaDB [runtimeedu]> create view mysql_user as select user,host,authentication_string from mysql.user;
Query OK, 0 rows affected (0.001 sec)

MariaDB [runtimeedu]> select * from mysql_user;
+------+-----------+-----------------------+
| user | host      | authentication_string |
+------+-----------+-----------------------+
| root | localhost |                       |
| root | 127.0.0.1 |                       |
| root | ::1       |                       |
+------+-----------+-----------------------+
3 rows in set (0.001 sec)
MariaDB [runtimeedu]>
7.多表创建视图,先创建两张表并插入数据,然后创建视图
MariaDB [runtimeedu]> create table product (id int unsigned auto_increment primary key,name varchar(30)not null,
    ->  price double not null);
Query OK, 0 rows affected (0.002 sec)

MariaDB [runtimeedu]> create table sale( id int unsigned auto_increment primary key, name varchar(30) not null,
    ->  quantity int not null default 0, get_time datetime not null);
Query OK, 0 rows affected (0.004 sec)

MariaDB [runtimeedu]> insert into product(name,price) values ('apple',10), ('banana',2.5), ('peach',4); 
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [runtimeedu]>
MariaDB [runtimeedu]> insert into sale(name,quantity,get_time) values ('apple',100,now()),
    ->  ('banana',150,now()),('peach',200,now());
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [runtimeedu]>
8.先用select语句查询总价格,但是每次查询很麻烦,所以创建一个视图方便查询
MariaDB [runtimeedu]> select product.id,product.name,price*quantity as 'total_price' from sale,product where
    ->  product.name=sale.name;
+----+--------+-------------+
| id | name   | total_price |
+----+--------+-------------+
|  1 | apple  |        1000 |
|  2 | banana |         375 |
|  3 | peach  |         800 |
+----+--------+-------------+
3 rows in set (0.000 sec)
MariaDB [runtimeedu]>
MariaDB [runtimeedu]> create view total_price as select product.id,product.name,price*quantity as 'total_price'
    ->  from sale,product where product.name=sale.name;
Query OK, 0 rows affected (0.001 sec)
MariaDB [runtimeedu]> 
MariaDB [runtimeedu]> select * from total_price;
+----+--------+-------------+
| id | name   | total_price |
+----+--------+-------------+
|  1 | apple  |        1000 |
|  2 | banana |         375 |
|  3 | peach  |         800 |
+----+--------+-------------+
3 rows in set (0.000 sec)
MariaDB [runtimeedu]>
9.如果两张表插入数据时,视图也将发生变化
MariaDB [runtimeedu]> insert into product(name,price) values ('orange',5);
Query OK, 1 row affected (0.001 sec)
MariaDB [runtimeedu]> 
MariaDB [runtimeedu]> insert into sale(name,quantity,get_time) values ('orange',120,now());
Query OK, 1 row affected (0.001 sec)
MariaDB [runtimeedu]> select * from total_price;
+----+--------+-------------+
| id | name   | total_price |
+----+--------+-------------+
|  1 | apple  |        1000 |
|  2 | banana |         375 |
|  3 | peach  |         800 |
|  4 | orange |         600 |
+----+--------+-------------+
4 rows in set (0.000 sec)
MariaDB [runtimeedu]>
10.查看视图
MariaDB [runtimeedu]> show tables;
+----------------------+
| Tables_in_runtimeedu |
+----------------------+
| department10         |
| department11         |
| department12         |
| mysql_user           |
| product              |
| sale                 |
| total_price          |
+----------------------+
7 rows in set (0.000 sec)
MariaDB [runtimeedu]>
11.查看数据库mysql中视图及所有表详细信息
MariaDB [runtimeedu]> show table status from mysql\G;
*************************** 1. row ***************************
            Name: column_stats
          Engine: MyISAM
         Version: 10
      Row_format: Dynamic
            Rows: 0
  Avg_row_length: 0
     Data_length: 0
 Max_data_length: 281474976710655
    Index_length: 4096
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2021-01-18 01:58:59
     Update_time: 2021-01-18 01:58:59
      Check_time: NULL
       Collation: utf8_bin
        Checksum: NULL
  Create_options: 
         Comment: Statistics on Columns
Max_index_length: 288230376151710720
       Temporary: N
*************************** 2. row ***************************
            Name: columns_priv
          Engine: MyISAM
         Version: 10
      Row_format: Fixed
            Rows: 0
  Avg_row_length: 0
     Data_length: 0
 Max_data_length: 282037926664077311
    Index_length: 4096
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2021-01-18 01:58:59
     Update_time: 2021-01-18 01:58:59
      Check_time: NULL
       Collation: utf8_bin
        Checksum: NULL
  Create_options: 
         Comment: Column privileges
Max_index_length: 1125899906841600
       Temporary: N
。。。。。。。。。。。。。。。。。省略
*************************** 31. row ***************************
            Name: user
          Engine: MyISAM
         Version: 10
      Row_format: Dynamic
            Rows: 3
  Avg_row_length: 114
     Data_length: 472
 Max_data_length: 281474976710655
    Index_length: 4096
       Data_free: 128
  Auto_increment: NULL
     Create_time: 2021-01-18 01:58:59
     Update_time: 2021-01-18 01:59:45
      Check_time: NULL
       Collation: utf8_bin
        Checksum: NULL
  Create_options: 
         Comment: Users and global privileges
Max_index_length: 288230376151710720
       Temporary: N
31 rows in set (0.003 sec)
ERROR: No query specified
MariaDB [runtimeedu]>
12.查看数据库runtimeedu中视图名total_price的详细信息
MariaDB [runtimeedu]> show table status from runtimeedu like 'total_price'\G;
*************************** 1. row ***************************
            Name: total_price
          Engine: NULL
         Version: NULL
      Row_format: NULL
            Rows: NULL
  Avg_row_length: NULL
     Data_length: NULL
 Max_data_length: NULL
    Index_length: NULL
       Data_free: NULL
  Auto_increment: NULL
     Create_time: NULL
     Update_time: NULL
      Check_time: NULL
       Collation: NULL
        Checksum: NULL
  Create_options: NULL
         Comment: VIEW
Max_index_length: NULL
       Temporary: NULL
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [runtimeedu]>
13.查看视图定义信息
MariaDB [runtimeedu]> show create view total_price\G;
*************************** 1. row ***************************
                View: total_price
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
 `total_price` AS select `product`.`id` AS `id`,`product`.`name` AS `name`,`product`.`price` * `sale`.`quantity` 
AS `total_price` from (`sale` join `product`) where `product`.`name` = `sale`.`name`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [runtimeedu]>
14.查看视图结构
MariaDB [runtimeedu]> desc total_price;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| id          | int(10) unsigned | NO   |     | 0       |       |
| name        | varchar(30)      | NO   |     | NULL    |       |
| total_price | double           | NO   |     | 0       |       |
+-------------+------------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
MariaDB [runtimeedu]>
15.修改视图通过删除后新创建
MariaDB [runtimeedu]> drop view mysql_user;
Query OK, 0 rows affected (0.000 sec)

MariaDB [runtimeedu]> create view mysql_user as select user,host from mysql.user;
Query OK, 0 rows affected (0.001 sec)

MariaDB [runtimeedu]> select * from mysql_user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.000 sec)
MariaDB [runtimeedu]>
16.通过alter修改视图
MariaDB [runtimeedu]> alter view mysql_user as select user,password from mysql.user;
Query OK, 0 rows affected (0.001 sec)
MariaDB [runtimeedu]>
上一篇 下一篇