【开发篇】MySQL的事务控制、锁定语句和安全问题_使用数据库提供的锁语句和使用事务实现锁定各自存在的风险

一、锁定语句

​  MySQL 支持对 MyISAM 和 MEMORY 存储引擎的表进行表级锁定，对 BDB 存储引擎的表进行页级锁定，对 InnoDB 存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

1、锁定机制简介

​  数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问时变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计的，所以各存储引擎的锁定机制也有较大区别。
  简单来说（个人理解）：锁就是让同一个表|页|行数据被不同线程同时访问时，排出先后，并规定再前一个没结束之前其他不允许访问的一种规则。

​  MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定、页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。​

行级锁定（row-level）
  行级锁定最大的特点就是锁定对象的颗粒度很小，它是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。

  虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁须要的操作就更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

表级锁定（table-level）
  和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统处理成本最小，所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好地避免困扰我们的死锁问题。

  当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

页级锁定（page-level）
  页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中并不太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

2、LOCK TABLE 和 UNLOCK TABLE

  LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
  UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁，具体语法如下：

表锁定语法：
LOCK TABLES
  tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
  [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] …
  
解锁
UNLOCK TABLES

表锁示例：

  下表是一个简单的表锁和释放锁的例子，其中 session1 和 session2 表示两个同时打开的 session。
  
  

  有关表锁的更多使用方法，将在后面的介绍。

二、事务

​  事务（Transaction） 是指作为一个逻辑工作单元执行的一系列操作，这些操作要么全部成功，要么全部失败。(最经典的例子：小王要给小李转账500块，主要会执行两步数据变更操作： ①从小王的账户减去500元； ②给小李的账户增加500元。那会不会小王的账户减了，小李的没增加呢？，当然不会！这两步要么都成功，要么都失败，所以，转账这个操作就是事务）。
​  在MySQL中，只有使用了Innodb存储引擎的数据库或表才支持事务。

1、事务的特性

​  若数据库声称支持事务，那么该数据库必须具备ACID四个特性，即Atomicity（原子性）、 Consistency（一致性）、Isolation（隔离性）和Durability（持久性）。

• Atomicity（原子性）：要么全部执行，要么全部不执行。（要么两个账号都变，要么都不变）
• Consistency（一致性）：使数据库从一种一致状态转变为另一种一致状态（要么两个账号都是转帐前，要么都是转账后，没有转账中等中间状态）
• Isolation（隔离性）：事务运行独立，互不干扰（上面例子转账时，小张又给小陈转账，不影响小王转账）
• Durability（持久性）：事务执行完成，对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。（小王完成转账后，她的账户余额变成80500，之后小王账号又减少了500，这和之前的转账没有任何关系）

2、事务控制

​  默认情况下，MySQL 是自动提交（Autocommit）的，如果需要通过明确的 Commit 和 Rollback 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和 Oracle的事务管理明显不同的地方。如果应用是从 Oracle 数据库迁移到 MySQL 数据库，则需要确保应用中是否对事务进行了明确的管理。
​  MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务，具体语法如下。

语法：
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

说明：
START TRANSACTION 或 BEGIN ：开始一项新的事务。
COMMIT| ROLLBACK ：用来提交或者回滚事务。
CHAIN 和 RELEASE：分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
SET AUTOCOMMIT： 可以修改当前连接的提交方式，如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

​  如果只是对某些语句需要进行事务控制，则使用 START TRANSACTION 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改 AUTOCOMMIT 来控制事务比较方便，这样不用在每个事务开始的时候再执行 START TRANSACTION 语句。

​  下面通过例子，理解事务控制。

-- 例子1：
--假设有一张actor表，表里没有ctor_id=201 的记录。session1 和 session2都可以查询
select * from actor where actor_id = 201;
Empty set (0.00 sec)

--session1新建一个事务，插入一条201的记录
start transaction;
Query OK, 0 rows affected (0.00 sec)

insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');
Query OK, 1 row affected (0.00 sec)

--session2检查插入结果（结果还是空表）
select * from actor where actor_id = 201;
Empty set (0.00 sec)

--session1执行提交,session2再查询（有记录了）
commit;
Query OK, 0 rows affected (0.04 sec)

select * from actor where actor_id = 201;
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 201 | Tom |
+----------+-----------+
1 row in set (0.00 sec)

-- 例子2：
-- 还是上面的数据表，session1这次直接插入数据
insert into actor (actor_id,first_name,last_name) values(202,'Lisa','Lan');
Query OK, 1 row affected (0.04 sec)
--session2检查结果
select actor_id,last_name from actor where actor_id = 202;
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 202 | Lan |
+----------+-----------+
2 rows in set (0.00 sec)

-- 例子3：
-- 还是上面的数据表，session1新建事务，并使用commit and chain 命令提交
--开启事务
start transaction;
Query OK, 0 rows affected (0.00 sec)
--插入数据（事务）
insert into actor (actor_id,first_name,last_name) values(203,'Lisa','TT');
Query OK, 1 row affected (0.00 sec)
--提交并立即开启新事务
commit and chain;
Query OK, 0 rows affected (0.03 sec)
--插入另一个数据（新事务）
insert into actor (actor_id,first_name,last_name) values(204,'Lisa','Mou');
Query OK, 1 row affected (0.00 sec)

--session2检查数据结果（并没有最后的mou的记录）
select actor_id,last_name from actor where first_name = 'Lisa';
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 178 | MONROE T |
| 201 | Tom |
| 202 | Lan |
| 203 | TT |
+----------+-----------+
4 rows in set (0.00 sec)

--session1再手动提交,然后session2检查
commit;
Query OK, 0 rows affected (0.06 sec)

select actor_id,last_name from actor where first_name = 'Lisa';
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 178 | MONROE T |
| 201 | Tom |
| 202 | Lan |
| 203 | TT |
| 204 | Mou |
+----------+-----------+
5 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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

​​  通过上面三个例子，要知道如下几点（当然，后两条不是）：

1. 手动开启的事务，必须要手动提交，不然操作不会影响表|数据库。
2. 所有的DML语句可以理解成自动提交的事务(没有更改AUTOCOMMIT=1）。
3. commit and chain 方式提交语句之前的事务，后面的要手动提交。
4. 如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock tables 被执行。
5. 对 lock 方式加的表锁，不能通过 rollback 进行回滚。

​​  在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
​​  通常情况下，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型表的更新可以被复制到从（Slave）数据库中。 和 Oracle 的事务管理相同，所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。

​​  在事务中可以通过定义 SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚不同的 SAVEPOINT。需要注意的是，如果定义了相同名字的 SAVEPOINT，则后面定义的SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT，删除后的 SAVEPOINT，不能再执行 ROLLBACK TO SAVEPOINT命令。

​下面通过例子，理解事务回滚。
​​  其中 session1 和 session2 表示两个同时打开的 session。也可以理解是两个用户。

 

3、分布式事务的使用

  MySQL 从 5.0.3 开始支持分布式事务，当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

1. 分布式事务的原理

  在 MySQL 中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

• 资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由 RM 管理的事务。例如，多台 MySQL 数据库作为多台资源管理器或者几台 Mysql 服务器和几台 Oracle 服务器作为资源管理器。
• 事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识。

   MySQL 执行 XA MySQL 时，MySQL 服务器相当于一个用于管理分布式事务中的 XA 事务的资源管理器。与 MySQL 服务器连接的客户端相当于事务管理器。

   用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。

• 在第一阶段，所有的分支被预备好。即它们被 TM 告知要准备提交。通常，这意味着用于管理分支的每个 RM 会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
• 在第二阶段，TM 告知 RMs 是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

   简单来说这两部就是：首先TM询问所有RM是否准备妥当，然后等待RMs的回应，当RMs都准备好久提交，有一个有问题就都回滚。

2. 分布式事务的语法

   分布式事务（XA 事务）的 SQL 语法主要包括：

基本语法：
XA {START|BEGIN} xid [JOIN|RESUME]

   XA START xid 用于启动一个带给定 xid 值的 XA 事务。每个 XA 事务必须有一个唯一的 xid值，因此该值当前不能被其他的 XA 事务使用。
   xid 是一个 XA 事务标识符，用来唯一标识一个分布式事务。xid 值由客户端提供，或由MySQL 服务器生成。xid 值包含 1～3 个部分：

xid: gtrid [, bqual [, formatID ]]

• gtrid 是一个分布式事务标识符，相同的分布式事务应该使用相同的 gtrid，这样可以明确知道 xa 事务属于哪个分布式事务。
• bqual 是一个分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务，bqual 值必须是唯一的。
• formatID 是一个数字，用于标识由 gtrid 和 bqual 值使用的格式，默认值是 1。

   下面其他 XA 语法中用到的 xid 值，都必须和 START 操作使用的 xid 值相同，也就是表示对这个启动的 XA 事务进行操作。

XA END xid [SUSPEND [FOR MIGRATE]]
  
XA PREPARE xid

说明：
上面两个语句使事务进入 PREPARE 状态，也就是两阶段提交的第一个提交阶段。

XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

这两个命令用来提交或者回滚具体的分支事务。也就是两阶段提交的第二个提交阶段，分支事务被实际的提交或者回滚。

XA RECOVER

返回当前数据库中处于 PREPARE 状态的分支事务的详细信息。

   分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时的故障解决。XA 的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等。下面引用原书中的一个例子来理解分布式事务操作。

3. 存在的问题

   虽然 MySQL 支持分布式事务，但是在测试过程中，还是发现存在一些问题。

   如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写 binlog，存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。

   不过好在MySQL 5.7版本之后，对于上述bug’进行了修复，使得MySQL对于分布式事务的支持变得完美了。

三、SQL中的安全问题

  在日常开发过程中，程序员一般只关心 SQL 是否能实现预期的功能，而对于 SQL 的安全问题一般都不太重视。实际上，如果 SQL 语句写作不当，将会给应用系统造成很大的安全隐患，其中最重要的隐患就是 SQL 注入。

1. SQL 注入简介

  结构化查询语言（SQL）是一种用来和数据库交互的文本语言。SQL Injection 就是利用某些数据库的外部接口将用户数据插入到实际的数据库操作语言（SQL）当中，从而达到入侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行。

  SQL 注入（SQL Injection）攻击具有很大的危害，攻击者可以利用它读取、修改或者删除数据库内的数据，获取数据库中的用户名和密码等敏感信息，甚至可以获得数据库管理员的权限，而且，SQL Injection 也很难防范。网站管理员无法通过安装系统补丁或者进行简单的安全配置进行自我保护，一般的防火墙也无法拦截 SQL Injection 攻击。
  这里对SQL注入不再做过多的讲解，感兴趣的可以自行搜索研读。