Appearance
MySQL 事务和字符集
为什么说一定要开启事务后才能对数据进行操作？
在银行业务中，有一条记账原则，即有借有贷，借贷相等。为了保证这种原则，每发生一笔银行业务，就必须确保会计账目上借方科目和贷方科目至少各记一笔，并且这两笔账要么同时成功，要么同时失败。如果出现只记录了借方科目，或者只记录了贷方科目的情况，就违反了记账原则。会出现记错账的情况。
在银行的日常业务中，只要是同一银行（如都是中国农业银行，简称农行），一般都支持账户间的直接转账。因此，银行转账操作往往会涉及两个或两个以上的账户。在转出账户的存款减少一定金额的同时，转入账户的存款就要增加相应的金额。
下面，在 MySQL 数据库中模拟一下上述提及的转账问题。
假如要从张三的账户直接转账 500 元到李四的账户。首先需要创建账户表，存放用户张三和李四的账户信息。创建账户表和插入数据的 SQL 语句和运行结果如下所示：
sql
mysql> CREATE DATABASE mybank;
Query OK, 1 row affected (0.02 sec)
mysql> USE mybank;
Database changed
mysql> CREATE TABLE bank(
-> customerName VARCHAR(20), #用户名
-> currentMoney DECIMAL(10,2) #当前余额
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.26 sec)
mysql> INSERT INTO bank (customerName,currentMoney) VALUES('张三',1000);;
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO bank (customerName,currentMoney) VALUES('李四',1);
Query OK, 1 row affected (0.08 sec)查询 bank 数据表的 SQL 语句和运行结果如下：
sql
mysql> SELECT * FROM bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三 | 1000.00 |
| 李四 | 1.00 |
+--------------+--------------+
2 rows in set (0.02 sec)结果显示，张三和李四两个账户的余额总和为 1000+1=1001 元。
下面开始模拟实现转账功能。从张三的账户直接转账 500 元到李四的账户，可以使用 UPDATE 语句分别修改张三的账户和李四的账户。张三的账户减少 500 元，李四的账户增加 500 元， SQL 语句如下所示：
sql
/*转账测试：张三转账给李四 500 元*/
#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三';
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';正常情况下，执行以上的转账操作后，余额总和应保持不变，仍为 1001 元。但是，如果在这个过程的其中一个环节出现差错，如在张三的账户减少 500 元之后，这时发生了服务器故障，李四的账户没有立即增加 500 元，此时，第三方读取到两个账户的余额总和变为 500+1=501 元，即账户总额间少了 500 元。
MySQL 为了解决此类问题，提供了事务。事务可以将一系列的数据操作捆绑成一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务执行时遇到错误，则就必须取消或回滚。取消或回滚后，数据将全部恢复到操作前的状态，所有数据的更改均被清除。
MySQL 通过事务保证了数据的一致性。上述提到的转账过程就是一个事务，它需要两条 UPDATE 语句来完成。这两条语句是一个整体，如果其中任何一个环节出现问题，则整个转账业务也应取消，两个账户中的余额应恢复为原来的数据，从而确保转账前和转账后的余额总和不变，即都是 1001 元。
MySQL 设置事务自动提交（开启和关闭）
MySQL 默认开启事务自动提交模式，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。下面主要介绍如何设置事务自动提交模式。
在 MySQL 中，可以通过 SHOW VARIABLES 语句查看当前事务自动提交模式，如下所示：
sql
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set, 1 warning (0.04 sec)结果显示，autocommit 的值是 ON，表示系统开启自动提交模式。
在 MySQL 中，可以使用 SET autocommit 语句设置事务的自动提交模式，语法格式如下：
sql
SET autocommit = 0|1|ON|OFF;对取值的说明：
- 值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有提交或回滚后才会结束当前事务，重新开始一个新事务。
- 值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句，事务都会提交一次。
示例
下面我们关闭事务自动提交，模拟银行转账。
使用 SET autocommit 语句关闭事务自动提交，且张三转给李四 500 元，SQL 语句和运行结果如下：
sql
mysql> SET autocommit = 0; ;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三 | 1000.00 |
| 李四 | 1.00 |
+--------------+--------------+
2 rows in set (0.00 sec)
mysql> UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName='张三' ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName='李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0这时重新打开一个 cmd 窗口，查看 bank 数据表中张三和李四的余额，SQL 语句和运行结果如下所示：
sql
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三 | 1000.00 |
| 李四 | 1.00 |
+--------------+--------------+
2 rows in set (0.00 sec)结果显示，张三和李四的余额是事务执行前的数据。
下面在之前的窗口中使用 COMMIT 语句提交事务，并查询 bank 数据表的数据，如下所示：
sql
mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三 | 500.00 |
| 李四 | 501.00 |
+--------------+--------------+
2 rows in set (0.00 sec)结果显示，bank 数据表的数据更新成功。
在本例中，关闭自动提交后，该位置会作为一个事务起点，直到执行 COMMIT 语句和 ROLLBACK 语句后，该事务才结束。结束之后，这就是下一个事务的起点。
关闭自动提交功能后，只用当执行 COMMIT 命令后，MySQL 才将数据表中的资料提交到数据库中。如果执行 ROLLBACK 命令，数据将会被回滚。如果不提交事务，而终止 MySQL 会话，数据库将会自动执行回滚操作。
使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。之后，自动提交模式会恢复到之前的状态，即如果 BEGIN 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 BEGIN 前 autocommit = 0，则完成本次事务后 autocommit 还是 0。
从实例出发，搞懂高并发下的数据库事务隔离级别
在《数据库事务》一节中介绍了 MySQL 事务的四大特性，其中事务的隔离性就是指当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。事务并发时就容易出现脏读、不可重复读和幻读等情况。
为了保证并发时操作数据的正确性，数据库都会有事务隔离级别的概念。
1) 脏读
脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
2) 不可重复读
不可重复读是指在一个事务内，多次读取同一个数据。
在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
3) 幻读
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
为了解决以上这些问题，标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。
MySQL 包括的事务隔离级别如下：
- 读未提交（READ UNCOMITTED）
- 读提交（READ COMMITTED）
- 可重复读（REPEATABLE READ）
- 串行化（SERIALIZABLE）
MySQL 事务隔离级别可能产生的问题如下表所示：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMITTED | √ | √ | √ |
| READ COMMITTED | × | √ | √ |
| REPEATABLE READ | × | × | √ |
| SERIALIZABLE | × | × | × |
MySQL 的事务的隔离级别由低到高分别为 READ UNCOMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少。
下面根据实例来一一阐述它们的概念和联系。
1. 读未提交（READ UNCOMITTED，RU）
顾名思义，读未提交就是可以读到未提交的内容。
如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。
在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。
例 1 主要演示了在读未提交隔离级别中产生的脏读现象。
示例 1
- 先在 test 数据库中创建 testnum 数据表，并插入数据。SQL 语句和执行结果如下：
sql
mysql> CREATE TABLE testnum(
-> num INT(4));
Query OK, 0 rows affected (0.57 sec)
mysql> INSERT INTO test.testnum (num) VALUES(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.09 sec)2) 下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。
在 A 窗口中修改事务隔离级别，因为 A 窗口和 B 窗口的事务隔离级别需要保持一致，所以我们使用 SET GLOBAL TRANSACTION 修改全局变量。SQL 语句如下：
sql
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.04 sec)
flush privileges;
Query OK, 0 rows affected (0.04 sec)查询事务隔离级别，SQL 语句和运行结果如下：
sql
mysql> show variables like '%tx_isolation%'\G
*************************** 1. row ***************************
Variable_name: tx_isolation
Value: READ-UNCOMMITTED
1 row in set, 1 warning (0.00 sec)结果显示，现在 MySQL 的事务隔离级别为 READ-UNCOMMITTED。
3) 在 A 窗口中开启一个事务，并查询 testnum 数据表，SQL 语句和运行结果如下：
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM testnum;
+------+
| num |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)- 打开 B 窗口，查看当前 MySQL 的事务隔离级别，SQL 语句如下：
sql
mysql> show variables like '%tx_isolation%'\G
*************************** 1. row ***************************
Variable_name: tx_isolation
Value: READ-UNCOMMITTED
1 row in set, 1 warning (0.00 sec)确定事务隔离级别是 READ-UNCOMMITTED 后，开启一个事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test.testnum SET num=num*2 WHERE num=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0- 现在返回 A 窗口，再次查询 testnum 数据表，SQL 语句和运行结果如下：
sql
mysql> SELECT * FROM testnum;
+------+
| num |
+------+
| 1 |
| 4 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.02 sec)由结果可以看出，A 窗口中的事务读取到了更新后的数据。
6) 下面在 B 窗口中回滚事务，SQL 语句和运行结果如下：
sql
mysql> ROLLBACK;
Query OK, 0 rows affected (0.09 sec)- 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：
sql
mysql> SELECT * FROM testnum;
+------+
| num |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)当 MySQL 的事务隔离级别为 READ UNCOMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新但未提交之前， A 窗口中的事务就已经读取到了更新后的数据。但由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。
使用读提交隔离级别可以解决实例中产生的脏读问题。
2. 读提交（READ COMMITTED，RC）
顾名思义，读提交就是只能读到已经提交了的内容。
如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。
该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。
这是大多数数据库系统的默认事务隔离级别（例如 Oracle、SQL Server），但不是 MySQL 默认的。
例 2 演示了在读提交隔离级别中产生的不可重复读问题。
示例 2
- 使用 SET 语句将 MySQL 事务隔离级别修改为 READ COMMITTED，并查看。SQL 语句和运行结果如下：
sql
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%tx_isolation%'\G
*************************** 1. row ***************************
Variable_name: tx_isolation
Value: READ-COMMITTED
1 row in set, 1 warning (0.00 sec)确定当前事务隔离级别为 READ COMMITTED 后，开启一个事务，SQL 语句和运行结果如下：
sqlmysql> BEGIN; Query OK, 0 rows affected (0.00 sec)在 B 窗口中开启事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：
sqlmysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test.testnum SET num=num*2 WHERE num=2; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：
sqlmysql> SELECT * from test.testnum; +------+ | num | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)提交 B 窗口中的事务，SQL 语句和运行结果如下：
sqlmysql> COMMIT; Query OK, 0 rows affected (0.07 sec)在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：
sqlmysql> SELECT * from test.testnum; +------+ | num | +------+ | 1 | | 4 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)
当 MySQL 的事务隔离级别为 READ COMMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新并提交后，A 窗口中的事务读取到了更新后的数据。在该过程中，A 窗口中的事务必须要等待 B 窗口中的事务提交后才能读取到更新后的数据，这样就解决了脏读问题。而处于 A 窗口中的事务出现了不同的查询结果，即不可重复读现象。
使用可重复读隔离级别可以解决实例中产生的不可重复读问题。
3. 可重复读（REPEATABLE READ，RR）
顾名思义，可重复读是专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。
在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。
可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。
例 3 演示了在可重复读隔离级别中产生的幻读问题。
示例 3
在 test 数据库中创建 testuser 数据表，SQL 语句和执行结果如下：
sqlmysql> CREATE TABLE testuser( -> id INT (4) PRIMARY KEY, -> name VARCHAR(20)); Query OK, 0 rows affected (0.29 sec)使用 SET 语句修改事务隔离级别，SQL 语句如下：
sqlmysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec)在 A 窗口中开启事务，并查询 testuser 数据表，SQL 语句和运行结果如下：
sqlmysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM test.testuser where id=1; Empty set (0.04 sec)在 B 窗口中开启一个事务，并向 testuser 表中插入一条数据，SQL 语句和运行结果如下：
sqlmysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO test.testuser VALUES(1,'zhangsan'); Query OK, 1 row affected (0.04 sec) mysql> COMMIT; Query OK, 0 rows affected (0.06 sec)现在返回 A 窗口，向 testnum 数据表中插入数据，SQL 语句和运行结果如下：
sqlmysql> INSERT INTO test.testuser VALUES(1,'lisi'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> SELECT * FROM test.testuser where id=1; Empty set (0.00 sec)
使用串行化隔离级别可以解决实例中产生的幻读问题。
4. 串行化（SERIALIZABLE）
如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。
SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。
MySQL 查看和修改事务隔离级别
在《MySQL事务隔离级别》一节中我们了解了 MySQL 的事务隔离级别，本节主要介绍查看和修改事务隔离级别的几种方法。
查看事务隔离级别
在 MySQL 中，可以通过show variables like '%tx_isolation%'或select @@tx_isolation;语句来查看当前事务隔离级别。
查看当前事务隔离级别的 SQL 语句和运行结果如下：
sql
mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.17 sec）
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)结果显示，目前 MySQL 的事务隔离级别是 REPEATABLE-READ。
另外，还可以使用下列语句分别查询全局和会话的事务隔离级别：
sql
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;提示：在MySQL 8.0.3 中，tx_isolation 变量被 transaction_isolation 变量替换了。在 MySQL 8.0.3 版本中查询事务隔离级别，只要把上述查询语句中的 tx_isolation 变量替换成 transaction_isolation 变量即可。
修改事务隔离级别
MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：
sql
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
- SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
- GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
- 如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。
任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。
如果使用普通用户修改全局事务隔离级别，就会提示需要超级权限才能执行此操作的错误信息，SQL 语句和运行结果如下：
sql
C:\Users\leovo>mysql -utestuser -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)示例 1
使用 SET TRANSACTION 语句分别修改 session 和全局的事务隔离级别SQL 语句和运行结果如下：
sql
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE |
+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)还可以使用 set tx_isolation 命令直接修改当前 session 的事务隔离级别，SQL 语句和运行结果如下：
sql
mysql> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED |
+------------------------+
1 row in set, 1 warning (0.00 sec)MySQL 锁机制：数据库核心技术之一
为了保证数据并发访问时的一致性和有效性，任何一个数据库都存在锁机制。锁机制的优劣直接影响到数据库的并发处理能力和系统性能，所以锁机制也就成为了各种数据库的核心技术之一。
锁机制是为了解决数据库的并发控制问题而产生的。如在同一时刻，客户端对同一个表做更新或查询操作，为了保证数据的一致性，必须对并发操作进行控制。同时，锁机制也为实现 MySQL 的各个隔离级别提供了保证。
可以将锁机制理解为使各种资源在被并发访问时变得有序所设计的一种规则。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库显得尤其重要，也更加复杂。本节我们先简单介绍一下锁机制及常见的锁类型。
按锁级别分类，可分为共享锁、排他锁和意向锁。也可以按锁粒度分类，可分为行级锁、表级锁和页级锁。下面我们先介绍共享锁、排他锁和意向锁。
1. 共享锁
共享锁的代号是 S，是 Share 的缩写，也可称为读锁。是一种可以查看但无法修改和删除的数据锁。
共享锁的锁粒度是行或者元组（多个行）。一个事务获取了共享锁之后，可以对锁定范围内的数据执行读操作。会阻止其它事务获得相同数据集的排他锁。
2. 排他锁
排他锁的代号是 X，是 eXclusive 的缩写，也可称为写锁，是基本的锁类型。
排他锁的粒度与共享锁相同，也是行或者元组。一个事务获取了排他锁之后，可以对锁定范围内的数据执行写操作。允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享锁和排他锁。
如有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能立即获取这个元组的排他锁，必须等到事务 A 释放共享锁之后才可以。
如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后才可以。
3. 意向锁
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁。
意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁（IS）和意向排他锁（IX）两类。
意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意”表示事务想执行操作但还没有真正执行。
锁和锁之间的关系，要么是相容的，要么是互斥的。
- 锁 a 和锁 b 相容是指：操作同样一组数据时，如果事务 t1 获取了锁 a，另一个事务 t2 还可以获取锁 b；
- 锁 a 和锁 b 互斥是指：操作同样一组数据时，如果事务 t1 获取了锁 a，另一个事务 t2 在 t1 释放锁 a 之前无法释放锁 b。
锁模式的兼容情况
其中共享锁、排他锁、意向共享锁、意向排他锁相互之间的兼容/互斥关系如下表所示，其中 Y 表示相容，N 表示互斥。
| 参数 | X | S | IX | IS |
|---|---|---|---|---|
| X（排他锁） | N | N | N | N |
| S（共享锁） | N | Y | N | Y |
| IX（意向排他锁） | N | N | Y | Y |
| IS（意向共享锁） | N | Y | Y | Y |
如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
为了尽可能提高数据库的并发量，需每次锁定的数据范围越小越好，越小的锁其耗费的系统资源越多，系统性能下降。为在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度”的概念。
MySQL 表锁、行锁和页锁
MySQL 按锁的粒度可以细分为行级锁、页级锁和表级锁。
我们可以将锁粒度理解成锁范围。
1）表级锁（table lock）
表级锁为表级别的锁定，会锁定整张表，可以很好的避免死锁，是 MySQL 中最大颗粒度的锁定机制。
一个用户在对表进行写操作（插入、删除、更新等）时，需要先获得写锁，这会阻塞其它用户对该表的所有读写操作。没有写锁时，其它读取的用户才能获得读锁，读锁之间是不相互阻塞的。
表级锁最大的特点就是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率会很高，致使并发度大打折扣。
不过在某些特定的场景中，表级锁也可以有良好的性能。例如，READ LOCAL 表级锁支持某些类型的并发写操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面）。
使用表级锁的主要是 MyISAM，MEMORY，CSV 等一些非事务性存储引擎。
尽管存储引擎可以管理自己的锁，MySQL 本身还是会使用各种有效的表级锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE 之类的语句使用表级锁，而忽略存储引擎的锁机制。
2）页级锁（page lock）
页级锁是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。
页级锁的颗粒度介于行级锁与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也是介于上面二者之间。另外，页级锁和行级锁一样，会发生死锁。
页级锁主要应用于 BDB 存储引擎。
3）行级锁（row lock）
行级锁的锁定颗粒度在 MySQL 中是最小的，只针对操作的当前行进行加锁，所以行级锁发生锁定资源争用的概率也最小。
行级锁能够给予应用程序尽可能大的并发处理能力，从而提高需要高并发应用系统的整体性能。虽然行级锁在并发处理能力上面有较大的优势，但也因此带来了不少弊端。
由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也就更多，带来的消耗自然也就更大。此外，行级锁也最容易发生死锁。所以说行级锁最大程度地支持并发处理的同时，也带来了最大的锁开销。
行级锁主要应用于 InnoDB 存储引擎。
随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量也越来越多，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也会随之提升。
MySQL 这 3 种锁的特性可大致归纳如下：
| 表级锁 | 行级锁 | 页级锁 | |
|---|---|---|---|
| 开销 | 小 | 大 | 介于表级锁和行级锁之间 |
| 加锁 | 快 | 慢 | 介于表级锁和行级锁之间 |
| 死锁 | 不会出现死锁 | 会出现死锁 | 会出现死锁 |
| 锁粒度 | 大 | 小 | 介于表级锁和行级锁之间 |
| 并发度 | 低 | 高 | 一般 |
从上述特点可见，很难笼统的说哪种锁更好，只能具体应用具体分析。
从锁的角度来说，表级锁适合以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用。而行级锁更适合于有大量按索引条件，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
MySQL InnoDB 的3种行锁定方式
在 MySQL 中，InnoDB 行锁通过给索引上的索引项加锁来实现，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁。
InnoDB 支持 3 种行锁定方式：
- 行锁（Record Lock）：直接对索引项加锁。
- 间隙锁（Gap Lock）：锁加在索引项之间的间隙，也可以是第一条记录前的“间隙”或最后一条记录后的“间隙”。
- Next-Key Lock：行锁与间隙锁组合起来用就叫做 Next-Key Lock。 前两种的组合，对记录及其前面的间隙加锁。
默认情况下，InnoDB 工作在可重复读（默认隔离级别）下，并且以 Next-Key Lock 的方式对数据行进行加锁，这样可以有效防止幻读的发生。
Next-Key Lock 是行锁与间隙锁的组合，这样，当 InnoDB 扫描索引项的时候，会首先对选中的索引项加上行锁（Record Lock），再对索引项两边的间隙（向左扫描扫到第一个比给定参数小的值， 向右扫描扫到第一个比给定参数大的值， 然后以此为界，构建一个区间）加上间隙锁（Gap Lock）。如果一个间隙被事务 T1 加了锁，其它事务不能在这个间隙插入记录。
要禁止间隙锁的话，可以把隔离级别降为读已提交（READ COMMITTED），或者开启参数 innodb_locks_unsafe_for_binlog。
注意：以上语句描述的情况，与 MySQL 所设置的事务隔离级别有较大的关系。
开启一个事务时，InnoDB 存储引擎会在更新的记录上加行级锁，此时其它事务不可以更新被锁定的记录。下面我们以示例1演示此过程。
例 1
下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。
分别在 A 窗口和 B 窗口中查看事务隔离级别，A 窗口和 B 窗口的事务隔离级别需要保持一致。
A 窗口查看隔离级别的 SQL 语句和运行结果如下所示：
sql
mysql> SHOW VARIABLES LIKE 'tx_isolation' \G
*************************** 1. row ***************************
Variable_name: tx_isolation
Value: REPEATABLE-READ
1 row in set, 1 warning (0.03 sec)B 窗口查看隔离级别 SQL 语句和运行结果如下所示：
sql
mysql> SHOW VARIABLES LIKE 'tx_isolation' \G
*************************** 1. row ***************************
Variable_name: tx_isolation
Value: REPEATABLE-READ
1 row in set, 1 warning (0.03 sec)结果显示，A窗口和 B窗口的事务隔离级别都为 REPEATABLE-READ。
在 A窗口中开启一个事务，并修改 tb_student 表，SQL 语句和运行结果如下：
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0在 B窗口中也开启一个事务，并修改 tb_student 表，SQL 语句和运行结果如下：
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;会发现 UPDATE 语句一直在执行。这时我们在 A 窗口中提交事务。
sql
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)这时我们发现 B 窗口中的 UPDATE 语句执行成功。
sql
mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;
Query OK, 0 rows affected (1 min 2.78 sec)
Rows matched: 1 Changed: 0 Warnings: 0查询 tb_student 表中的数据，SQL 语句和运行结果如下：
mysql> SELECT * FROM test.tb_student;
+----+------+------+------+------+
| id | name | age | sex | num |
+----+------+------+------+------+
| 1 | 张三 | 30 | 男 | 4 |
| 2 | 李四 | 12 | 男 | 4 |
| 3 | 王五 | 13 | 女 | 4 |
| 4 | 张四 | 13 | 女 | 4 |
| 5 | 王四 | 15 | 男 | 4 |
| 6 | 赵六 | 12 | 女 | 4 |
+----+------+------+------+------+
6 rows in set (0.00 sec)如以上实例所示，当有不同的事务同时更新同一条记录时，另外一个事务需要等待另一个事务把锁释放，此时查看 MySQL 中 InnoDB 存储引擎的状态如下：
sql
mysql> SHOW ENGINE innodb status \G
......
------------
TRANSACTIONS
------------
Trx id counter 19556
Purge done for trx's n:o < 19554 undo n:o < 0 state: running but idle
History list length 12
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283572223909376, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 19555, ACTIVE 54 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating
UPDATE test.tb_student SET age ='30' WHERE id = 1
------- TRX HAS BEEN WAITING 54 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table `test`.`tb_student` trx id 19555 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000004c62; asc Lb;;从上面运行结果可以看出，SQL 语句 UPDATE test.tb_student SET age ='30' WHERE id = 1 在等待，RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table test.tb_student trx id 19555 lock_mode X locks rec but not gap 表示锁住的资源，locks rec but not gap 代表锁住的是一个索引，不是一个范围。
“MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating”表示第 2 个事务连接的 ID 为 14，当前状态为正在更新，同时正在更新的记录需要等待其它事务将锁释放。当超过事务等待锁允许的最大时间，此时会提示“ERROR 1205(HY000):Lock wait timeout exceeded; try restarting transaction" 及当前事务执行失败，则自动执行回滚操作。
MySQL 数据库采用 InnoDB 模式，默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50s，一旦数据库锁超过这个时间就会报错。可通过以下命令查看当前数据库锁等待的时间。
sql
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120 |
+--------------------------+-------+
1 row in set, 1 warning (0.02 sec)下面演示了 InnoDB 间隙锁的实现机制。
例 2
下面在保证 A 窗口和 B 窗口的前提下，将 tb_student 表中的 id 字段设为外键，并开启一个事务，修改 tb_student 表中 id 为 1 的 age。SQL 语句和运行结果如下：
sql
mysql> ALTER TABLE test.tb_student ADD unique key idx_id(id);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test.tb_student SET age ='31' WHERE id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0在 B 窗口中开启一个事务，修改 tb_student 表中 id 为 2 的 age，SQL 语句和运行结果如下：
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test.tb_student SET age ='28'WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0这时分别提交 A窗口和 B窗口的事务。
sql
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)查询 tb_student 表的数据，SQL 语句和运行结果如下：
sql
mysql> SELECT * FROM test.tb_student;
+----+------+------+------+------+
| id | name | age | sex | num |
+----+------+------+------+------+
| 1 | 张三 | 31 | 男 | 4 |
| 2 | 李四 | 28 | 男 | 4 |
| 3 | 王五 | 13 | 女 | 4 |
| 4 | 张四 | 13 | 女 | 4 |
| 5 | 王四 | 15 | 男 | 4 |
| 6 | 赵六 | 12 | 女 | 4 |
+----+------+------+------+------+
6 rows in set (0.00 sec)在上述示例中，由于 InnoDB 行级锁为间隙锁，只锁定需要的记录，因此 B窗口中的事务可以更新其它记录，两个事务之间互不影响。
MySQL 并发时常见的死锁及解决方法
使用数据库时，有时会出现死锁。对于实际应用来说，就是出现系统卡顿。
死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。就是所谓的锁资源请求产生了回路现象，即死循环，此时称系统处于死锁状态或系统产生了死锁。常见的报错信息为“Deadlock found when trying to get lock...”。
死锁展示
上图中，很明显是右侧的四辆汽车造成了死锁。
死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁。多数情况下只需要重新执行因死锁回滚的事务即可。下面我们通过一个实例来了解死锁是如何产生的。
例 1
为了方便读者阅读，操作之前我们先查询 tb_student 表的数据和表结构。
sql
mysql> SELECT * FROM tb_student;
+----+------+------+------+------+
| id | name | age | sex | num |
+----+------+------+------+------+
| 1 | 张三 | 31 | 男 | 4 |
| 2 | 李四 | 28 | 男 | 4 |
| 3 | 王五 | 13 | 女 | 4 |
| 4 | 张四 | 13 | 女 | 4 |
| 5 | 王四 | 15 | 男 | 4 |
| 6 | 赵六 | 12 | 女 | 4 |
+----+------+------+------+------+
6 rows in set (0.01 sec)
mysql> DESC tb_student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| age | int(11) | YES | MUL | NULL | |
| sex | char(1) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)以下操作需要打开两个会话窗口，即下面所提到的 A窗口和 B窗口。
在 A窗口中执行以下命令：
sql
mysql> BEGIN;
mysql> UPDATE tb_student SET num=5 WHERE age=13;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0紧接着在 B窗口中执行以下命令。由于 age 是索引字段，与 A窗口中更新的是不同行的数据，所以这时不会出现锁等待现象。
sql
mysql> BEGIN;
mysql> UPDATE tb_student SET num=8 WHERE age=15;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0然后在 A窗口中，执行以下命令，这时就会出现锁等待现象了。
sql
mysql> UPDATE tb_student SET num=10 WHERE age=15;最后在 B窗口中，执行以下命令，这时会出现相互等待资源的现象，也就是死锁现象。
sql
mysql> UPDATE tb_student SET num=12 WHERE age=13;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction我们可以通过 SHOW ENGINE INNODB STATUS 命令查看死锁的信息，运行结果如下（这里只展示了部分信息）：
LATEST DETECTED DEADLOCK
2020-08-24 16:22:23 0x3944
*** (1) TRANSACTION:
TRANSACTION 22656, ACTIVE 108 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 33, OS thread handle 8808, query id 1689 localhost ::1 root updating
UPDATE tb_student SET num=10 WHERE age=15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 197 page no 8 n bits 80 index index_age of table `test`.`tb_student` trx id 22656 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 80000005; asc ;;
......通过以上日志，我们就能确定造成死锁的事务和 SQL 语句。
死锁检测
InnoDB 的并发写操作会触发死锁，同时 InnoDB 也提供了死锁检测机制。通过设置 innodb_deadlock_detect 参数的值来控制是否打开死锁检测。
- innodb_deadlock_detect = ON ：默认值，打开死锁检测。数据库发生死锁时，系统会自动回滚其中的某一个事务，让其它事务可以继续执行。
- innodb_deadlock_detect = OFF：关闭死锁检测。发生死锁时，系统会用锁等待来处理。
锁等待是指在事务过程中产生的锁，其它事务需要等待上一个事务释放锁，才能占用该资源。如果该事务一直不释放，就需要持续等待下去，直到超过了锁等待时间。当超过锁等待允许的最大时间，就会出现死锁，然后当前事务执行失败，自动执行回滚操作。
MySQL 通过 innodb_lock_wait_timeout 参数控制锁等待的时间，单位是秒。
sql
mysql> SHOW VARIABLES LIKE '%innodb_lock_wait%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120 |
+--------------------------+-------+
1 row in set, 1 warning (0.02 sec)在实际应用中，我们要尽量防止锁等待现象的发生，下面介绍几种避免死锁的方法：
- 如果不同程序会并发存取多个表，或者涉及多行记录时，尽量约定以相同的顺序访问表，这样可以大大降低死锁的发生。
- 业务中要及时提交或者回滚事务，可减少死锁产生的概率。
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
- 对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁定来减少死锁产生的概率（表级锁不会产生死锁）。
MySQL 锁监控
通常情况下，当出现锁问题时，我们习惯性通过 SHOW FULL PROCESSLIST 和 SHOW ENGINE INNODB STATUS 命令来判断事务中锁问题的情况。其实还有特别重要的三张表，即在 information_schema 数据库下的 innodb_trx、innodb_locks 和 innodb_lock_waits 表。 这三张表可以更方便地来帮助我们监控当前的事务并分析可能存在的锁问题。
下面通过实例来逐一了解一下这三张表。
例 1
在 A窗口中，开启一个事务，在查询 tb_student 表字段 age<15 的语句上加一个写锁，SQL 命令如下：
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test.tb_student WHERE age<15 FOE UPDATE;
+----+------+------+------+------+
| id | name | age | sex | num |
+----+------+------+------+------+
| 3 | 王五 | 13 | 女 | 12 |
| 4 | 张四 | 13 | 女 | 12 |
| 6 | 赵六 | 12 | 女 | 4 |
+----+------+------+------+------+
3 rows in set (0.02 sec)在 B窗口中开启一个事务，在 tb_student 表中插入 age=14 的记录，出现锁等待超时。
sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tb_student(name,age) VALUES ('dd',14);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction我们通过开始提到的三张表来分析出现的锁等待问题。
查询 innodb_trx 表，SQL 语句和运行结果如下：
sql
mysql> SELECT * FROM information_schema.innodb_trx \G
*************************** 1. row ***************************
trx_id: 22694
trx_state: LOCK WAIT
trx_started: 2019-08-25 09:17:26
trx_requested_lock_id: 22694:197:3:1
trx_wait_started: 2019-08-25 09:17:26
trx_weight: 2
trx_mysql_thread_id: 42
trx_query: INSERT INTO tb_student(name,age) VALUES ('dd',14)
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 22693
trx_state: RUNNING
trx_started: 2019-08-25 09:17:17
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 41
trx_query: select * FROM information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 7
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| trx_id | 唯一的事务 id 号。本例为 22694 和 22693 |
| trx_state | 当前事务的状态。本例中 22694 事务号是 lock_wait 锁等待状态 |
| trx_wait_started | 事务开始等待的时间。本例为 2019-08-25 09:17:26 |
| trx_mysql_thread_id | 线程 id，与 SHOW FULL PROCESSLIST 相对应。本例为 42 |
| trx_query | 事务运行的 SQL 语句，本例为 INSERT INTO tb_student(name,age) VALUES ('dd',14)。 |
| trx_operation_state | 事务运行的状态。本例为 inserting。 |
使用 SHOW FULL PROCESSLIST 语句查看当前线程处理情况，通常用来处理突发事件，返回的结果是实时变化的。
sql
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+---------------------------------------------------+
| id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+---------------------------------------------------+
| 35 | root | localhost:64579 | test | Sleep | 1772 | | NULL |
| 36 | root | localhost:64582 | NULL | Sleep | 1775 | | NULL |
| 45 | root | localhost:64933 | test | Query | 0 | starting | SHOW FULL PROCESSLIST |
| 46 | root | localhost:64934 | test | Query | 8 | update | INSERT INTO tb_student(name,age) VALUES ('dd',14) |
+----+------+-----------------+------+---------+------+----------+---------------------------------------------------+
4 rows in set (0.00 sec)以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| id | 一个标识，kill 有问题的线程时使用 |
| user | 显示当前用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句 |
| host | 显示这个语句是从哪个 ip 的哪个端口上发出的，可以用来追踪出问题语句的用户 |
| db | 显示这个进程目前连接的是哪个数据库 |
| command | 显示当前连接的执行命令，一般就是休眠（sleep），查询（query），连接（connect） |
| time | 这个状态持续的时间，单位是秒 |
| state | 显示使用当前连接的 SQL 语句的状态 |
| info | 显示这个 SQL 语句，因为长度有限，所以长的 SQL 语句就会显示不全，是判断问题语句的重要依据 |
下面通过 innodb_lock_waits 和 innodb_locks 两张表来判断持有锁和锁等待的对象。本例中 22696 是锁等待的对象，22695 是持有锁的对象。
innodb_lock_waits 表包含每个被阻止 InnoDB 事务的一个或多个行，指示它已请求的锁以及阻止该请求的任何锁。
sql
mysql> SELECT * FROM information_schema.innodb_lock_waits \G
*************************** 1. row ***************************
requesting_trx_id: 22696
requested_lock_id: 22696:197:3:1
blocking_trx_id: 22695
blocking_lock_id: 22695:197:3:1
1 row in set, 1 warning (0.00 sec)以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| requesting_trx_id | 请求（阻止）事务的 id |
| requested_lock_id | 事务正在等待的锁的id |
| blocking_trx_id | 阻止事务的 id |
| blocking_lock_id | 阻止另一个事务继续进行的事务所持有的锁的 id |
innodb_locks 表提供有关 InnoDB 事务已请求但尚未获取的每个锁的信息，以及事务持有的阻止另一个事务的锁。
sql
mysql> SELECT * FROM information_schema.innodb_locks \G
*************************** 1. row ***************************
lock_id: 22696:197:3:1
lock_trx_id: 22696
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tb_student`
lock_index: PRIMARY
lock_space: 197
lock_page: 3
lock_pec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 22695:197:3:1
lock_trx_id: 22695
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tb_student`
lock_index: PRIMARY
lock_space: 197
lock_page: 3
lock_pec: 1
lock_data: supremum pseudo-record
2 rows in set, 1 warning (0.00 sec)以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| lock_id | 一个唯一的锁 id 号，内部为 InnoDB |
| lock_trx_id | 持有锁的交易的 id |
| lock_mode | 如何请求锁定。允许锁定模式描述符 S，X， IS，IX， GAP，AUTO_INC 和 UNKNOWN。锁定模式描述符可以组合使用以识别特定的锁定模式。 |
| lock_type | 锁的类型 |
| lock_table | 已锁定或包含锁定记录的表的名称 |
| lock_index | 索引的名称，如果 lock_type 是 RECORD，否则 NULL |
| lock_space | 锁定记录的表空间 id，如果 lock_type 是 RECORD，否则 NULL |
| lock_page | 锁定记录的页码，如果 lock_type 是 RECORD，否则 NULL。 |
| lock_pec | 页面内锁定记录的堆号，如果 lock_type 是 RECORD，否则 NULL。 |
| lock_data | 与锁相关的数据。 如果 lock_type 是 RECORD，是锁定的记录的主键值，否则 NULL。此列包含锁定行中主键列的值，格式为有效的 SQL 字符串。如果没有主键，lock_data 则是唯一的 InnoDB 内部行 id 号。如果对键值或范围高于索引中的最大值的间隙锁定，则 lock_data 报告 supremum pseudo-record。当包含锁定记录的页面不在缓冲池中时（如果在保持锁定时将其分页到磁盘），InnoDB不从磁盘获取页面，以避免不必要的磁盘操作。相反， lock_data 设置为 NULL。 |
MySQL 设置默认字符集和校对规则
MySQL 服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段中，都可以使用不同的字符集。Oracle 等其它数据库管理系统都只能使用相同的字符集，相比之下，MySQL 明显存在更大的灵活性。
MySQL 的字符集和校对规则有 4 个级别的默认设置，即服务器级、数据库级、表级和字段级。它们分别在不同的地方设置，作用也不相同。
服务器字符集和校对规则
修改服务器默认字符集和校对规则的方法如下。
1）可以在 my.ini 配置文件中设置服务器字符集和校对规则，添加内容如下：
[mysqld]
character-set-server=字符集名称2）连接 MySQL 服务器时指定字符集：
sql
mysql --default-character-set=字符集名称 -h 主机IP地址 -u 用户名 -p 密码如果没有指定服务器字符集，MySQL 会默认使用 latin1 作为服务器字符集。如果只指定了字符集，没有指定校对规则，MySQL 会使用该字符集对应的默认校对规则。如果要使用字符集的非默认校对规则，需要在指定字符集的同时指定校对规则。
可以用 SHOW VARIABLES LIKE 'character_set_server' 和 SHOW VARIABLES LIKE 'collation_server' 命令查询当前服务器的字符集和校对规则。
sql
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| character_set_server | gbk |
+----------------------+--------+
1 row in set, 1 warning (0.01 sec)
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name | Value |
+------------------+-------------------+
| collation_server | gbk_chinese_ci |
+------------------+-------------------+
1 row in set, 1 warning (0.01 sec)数据库字符集和校对规则
数据库的字符集和校对规则在创建数据库时指定，也可以在创建完数据库后通过 ALTER DATABASE 命令进行修改，具体操作可阅读学习《MySQL修改数据库》一节。
需要注意的是，如果数据库里已经存在数据，修改字符集后，已有的数据不会按照新的字符集重新存放，所以不能通过修改数据库的字符集来修改数据的内容。在《MySQL修改字符集步骤详解》一节我们介绍了如何修改已存在数据字符集的方法。
设置数据库字符集的规则如下：
- 如果指定了字符集和校对规则，则使用指定的字符集和校对规则；
- 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则；
- 如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；
- 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。
为了避免受到默认值的影响，推荐在创建数据库时指定字符集和校对规则。
可以使用 SHOW VARIABLES LIKE 'character_set_database' 和 SHOW VARIABLES LIKE 'collation_database' 命令查看当前数据库的字符集和校对规则。
sql
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_database';
+--------------------+-------------------+
| Variable_name | Value |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set, 1 warning (0.00 sec)表字符集和校对规则
表的字符集和校对规则在创建表的时候指定，也可以在创建完表后通过 ALTER TABLE 命令进行修改，具体操作可阅读学习《MySQL修改数据表》一节。
同样，如果表中已有记录，修改字符集后，原有的记录不会按照新的字符集重新存放。表的字段仍然使用原来的字符集。
设置表的字符集规则和设置数据库字符集的规则基本类似：
- 如果指定了字符集和校对规则，使用指定的字符集和校对规则；
- 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则；
- 如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；
- 如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。
为了避免受到默认值的影响，推荐在创建表的时候指定字符集和校对规则。
可以使用 SHOW CREATE TABLE 命令查看当前表的字符集和校对规则，SQL 语句和运行结果如下：
sql
mysql> SHOW CREATE TABLE tb_students_info \G
*************************** 1. row ***************************
Table: tb_students_info
Create Table: CREATE TABLE `tb_students_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`height` float DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)列字符集和校对规则
MySQL 可以定义列级别的字符集和校对规则，主要是针对相同表的不同字段需要使用不同字符集的情况。一般遇到这种情况的几率比较小，这只是 MySQL 提供给我们一个灵活设置的手段。
列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整。语法格式如下：
ALTER TABLE 表名 MODIFY 列名 数据类型 CHARACTER SET 字符集名;
例 1
修改 tb_students_info 表中 name 列的字符集，并查看。SQL 语句和运行结果如下：
sql
mysql> ALTER TABLE tb_students_info MODIFY name VARCHAR(10) CHARACTER SET gbk;
Query OK, 11 rows affected (0.11 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tb_students_info \G
*************************** 1. row ***************************
Table: tb_students_info
Create Table: CREATE TABLE `tb_students_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET gbk DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`height` float DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)结果显示，name 列字符集修改成功。
如果在创建列的时候没有特别指定字符集和校对规则，默认使用表的字符集和校对规则。
连接字符集和校对规则
上面所讲的 4 种设置方式，确定的都是数据保存的字符集和校对规则。实际应用中，还需要设置客户端和服务器之间交互的字符集和校对规则。
对于客户端和服务器的交互操作，MySQL 提供了 3 个不同的参数：character_set_client、character_set_connection 和 character_set_results，分别代表客户端、连接和返回结果的字符集。通常情况下，这 3 个字符集是相同的，这样可以确保正确读出用户写入的数据，尤其是中文字符。字符集不同时，容易导致写入的记录不能正确读出。
设置客户端和服务器连接的字符集和校对规则有以下几种方法：
1）在 my.ini 配置文件中，设置以下语句：
[mysql]
default-character-set=gbk这样服务器启动后，所有连接默认使用 GBK 字符集进行连接。
2）可以通过以下命令来设置连接的字符集和校对规则，这个命令可以同时修改以上 3 个参数（character_set_client、character_set_connection 和 character_set_results）的值。
sql
SET NAMES gbk;使用这个方法可以“临时一次性地”修改客户端和服务器连接时的字符集为 gbk。
3）MySQL 还提供了下列 MySQL 命令“临时地”修改 MySQL“当前会话的”字符集和校对规则。
sql
set character_set_client = gbk;
set character_set_connection = gbk;
set character_set_database = gbk;
set character_set_results = gbk;
set character_set_server = gbk;
set collation_connection = gbk_chinese_ci;
set collation_database = gbk_chinese_ci;
set collation_server = gbk_chinese_ci;再见乱码，MySQL 修改字符集步骤详解
在实际应用中，如果一开始没有正确的设置字符集，在运行一段时间以后，才发现当前字符集不能满足要求，需要进行调整，但又不想丢弃这段时间的数据，这个时候就需要修改字符集。
在《MySQL设置默认字符集和校对规则》一节我们讲到，ALTER DATABASE 或 ALTER TABLE 命令对已经存在的数据没有作用，只对新创建的表或记录生效。如果想修改已存在数据的字符集，需要先将数据导出，经过适当的调整后，再重新导入。
例 1
以下模拟的是将 gb2312 字符集的数据库修改成 gbk 字符集的数据库的过程。
1）创建 testset 数据库，设置其字符集为 gb2312，并添加数据。
sql
mysql> CREATE TABLE test.testset(
-> id INT(11) DEFAULT NULL,
-> name VARCHAR(25) DEFAULT NULL
-> )CHARSET=gb2312;
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO test.testset VALUES (1,'C语言');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test.testset VALUES (2,'Java语言');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test.testset VALUES (3,'Python语言');
Query OK, 1 row affected (0.01 sec)2）导出 testset 表结构，命令如下：
sql
mysqldump -uroot -p --default-character-set=gbk -d test testset> D:\testset.sql其中，--default-character-set=gbk 表示以什么字符集连接；-d 表示只导出表结构，不导出数据。
3）打开 testset.sql 文件，修改表结构定义中的字符集为新的字符集，如下图所示。
4）确保表中的记录不再更新，导出所有记录。
sql
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=gb2312 test testset> D:\testdata.sql- --quick：该选项用于存储记录多的表。它强制 mysqldump 从服务器一次一行地查询表中的行，而不是查询所有行，并在输出前将它缓存到内存中。
- --extended-insert：使用 INSERT 插入多行数据语法。可以使文件更小，导入文件时加速插入。
- --no-create-info：不导出表的 CREATE TABLE 语句。
- --default-character-set=gb2312：按照原有的字符集导出所有数据。这样导出的文件中，所有中文都是可见的，不会保存成乱码。
5）打开 testdata.sql，将 SET NAMES gb2312 修改成 SET NAMES gbk，如下图所示。
6）使用新的字符集创建新的数据库。
sql
CREATE DATABASE test2 DEFAULT CHARSET gbk;7）创建表，执行 testset.sql。
sql
mysql -uroot -p test2 < D:\testset.sql8）导入数据，执行 testdata.sql。
sql
mysql -uroot -p test2 < D:\testdata.sql9）查看 testset 表结构是否修改了字符集，以及表内数据是否丢失或乱码，SQL 语句和运行结果如下：
sql
mysql> SHOW CREATE TABLE test2.testset \G
*************************** 1. row ***************************
Table: testset
Create Table: CREATE TABLE `testset` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> SELECT * FROM test2.testset;
+------+------------+
| id | name |
+------+------------+
| 1 | C语言 |
| 2 | Java语言 |
| 3 | Python语言 |
+------+------------+
3 rows in set (0.00 sec)注意：选择目标字符集的时候，要注意最好的是原字符集的超集，或者确定比原字符集的字库更大，否则如果目标字符集的字库小于原字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。
MySQL 如何选择正确的字符集？
由于数据库中存储的数据大部分都是各种文字，所以字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响。对数据库来说，字符集非常重要。不论是在 MySQL 数据库还是其它数据库，都存在字符集的选择问题。
如果在创建数据库时没有正确选择字符集，在后期就可能需要更换字符集，而更换字符集是代价比较高的操作，也存在一定的风险。所以推荐在应用开始阶段，就按照实际需求，正确的选择合适的字符集，避免后期不必要的调整。
在《MySQL查看字符集和校对规则》一节中，我们了解到目前 MySQL 5.7 支持几十种字符集，包括 UCS-2、UTF-16、UTF-16LE、UTF-32、 UTF-8 和 utf8mb4 等 Unicode 字符集。那么面对众多的字符集，我们该如何选择呢？
在选择数据库字符集时，可以根据应用的需求，结合字符集的特点来权衡，主要考虑以下几方面的因素。
1）满足应用支持语言的需求。如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode 字符集。对 MySQL 来说，目前就是 UTF-8。
2）如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据的字符集是 GBK，如果选择 GB 2312-80 为数据库字符集，就很可能出现某些文字无法正确导入。
3）如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。
因为，相对于 UTF-8 而言，GBK 比较“小”，每个汉字只占 2 个字节，而 UTF-8 汉字编码需要 3 个字节，这样可以减少磁盘 I/O、数据库 Cache 以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8 更好，因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节，会造成很多不必要的开销。
4）如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
5）如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。
6）在多种字符集都能够满足应用的前提下，应尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数，同时由于存储空间的较小间接的提高了系统的性能。
拓展
有很多字符集都可以保存汉字，比如 UTF-8、GB2312、GBK、Latin1 等等。但是常用的是 GB2312 和 GBK。因为 GB2312 字库比 GBK 字库小，有些偏僻字（例如：洺）不能保存，因此在选择字符集的时候一定要权衡这些偏僻字出现的几率，一般情况下，最好选用 GBK。