@dungan 2022-06-30T08:24:10.000000Z 字数 21607 阅读 168

mysql 深入

Mysql

索引

索引的实现方式

mysql 的索引实现方式主要有两种：B+Tree 和 Hash，InnoDB 中的索引是通过 B+Tree 实现的。

Hash

hash 索引只有 Memory/NDB 引擎支持，适用于 Key-Value 查询，通过Hash索引要比B-tree 索引查询更迅速。

B+Tree

B+Tree 的原理

B+Tree 是由二叉树->平衡二叉树->B-Tree 演化而来的，我们先逐一了解下这三种树状结构，以便更好的了解 B+Tree 结构。

二叉树
二叉树中，左子树的键值永远比右子树的小，并且小于根键值。

平衡二叉树
平衡二叉树是二叉树基础上的提高，它是一颗空树，必须满足左右两个子树的高度差绝对值不能超过1，它和二叉树最大的区别在于随时要保证插入后的整棵二叉树是平衡的，它会通过左旋或右旋来使不平衡的树变成平衡树。

B-Tree

B-Tree 也可以叫平衡多路搜索树，多路的意思就是索引节点的孩子至少有2个，并且所有叶子节点位于同一层。

B-Tree 有如下特点

每个索引节点(图中的方块)可以存储好几个关键字，以及关键字的数据(data)；例如图中的『12,22』就是关键字，它们的数据存储形式为对象 {12:{...}, 22:{...}}。

每个索引节点的关键字是按从小到大的顺序排列的，节点间的关系也满足平衡二叉树的特点，即每个叶子节点的高度一样。

每个索引节点还会有几个指针，用来指向索引节点或叶子节点，例如上图『12,22』所在的节点有三个指针。

基于的B-Tree的特点，可以看到在B-Tree中按key检索数据的算法非常高效的：

首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

B+Tree
B+树其实是B-树变种，与B-树最大的区别是索引结点不存储数据(data)，只存储关键字，所有的数据存储在叶子节点上，并且叶子节点间通过顺序访问指针来提高区间查询的性能，这样设计让 B+Tree 能够支持『全等，范围，前缀』查找，以及『ORDER BY』和『GROUP BY』。

索引分类

先说一下 索引的选择性

索引的选择性指的是不重复的索引值和记录总数的比值，计算公式为 COUNT(DISTINCT field)/COUNT(*)，最大值为1，选择性越高，查询效率也越高，因此索引一般建在选择性高的列上。

普通索引

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

创建语法

alter mytable add index [indexName] on (column);

唯一索引

它与前面的普通索引类似，不同的就是：普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。如果是联合索引，则列的组合必须唯一。

创建语法

alter table_name add unique [indexName] on (cloumn)

前缀索引

对于 BLOB，TEXT，或很长的 VARCHAR 类型的列，为它们前几个字符建立索引，这样的索引称为前缀索引，比如查询name列以 J 开头满足条件的记录。前缀索引不能在 ORDER BY 或 GROUP BY 中使用，也不能把它们用作覆盖索引。

前缀索引创建语法

alter table table_name add key(column(prefix_length))

至于取多少个字符，这个我们可以在同一个查询中计算不同前缀长度的选择性，一般某个字段的前缀选择性如果能接近 0.031，基本上就可以建索引了。

select count(distinct left(column, 3))/count(*) as c1,
       count(distinct left(column, 4))/count(*) as c2,
       count(distinct left(column, 5))/count(*) as c3,
       count ...
       from table;

联合索引

联合索引是指对表上的多个列进行索引。

联合索引的创建语法

 create index idx_name_age on user (name,phone);

联合索引最左前缀原则

一条查询语句可以只使用索引中的一份，但查询条件必须包含联合索引中最左边的列，称之为最左前缀原则，建议将选择性最高的列放在最左侧。

# 可以使用到索引
select * from user where name='xxx';
select * from user where name='xxx' and phone='xxx';
select * from user where name='xxx' and phone like 'xx%';
select * from user where name like 'xx%';
# 无法使用到索引
select * from user where phone='xxx';
select * from user where phone like 'xx%';

聚簇索引

InnoDB 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。

InnoDB聚簇索引的叶子节点存储行记录，因此，nnoDB必须有且只有一个聚集索引：

如果表定义了PK，则PK就是聚集索引；
如果表没有定义PK，则第一个not NULL unique列是聚集索引；
否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB 普通索引的叶子节点存储主键值。

MyISAM 就是典型的普通索引，它的B+Tree叶子节点 data 域记录的是数据存放的地址。

聚簇索引的优点

数据访问很快，因为索引和数据保存在同一个 b-tree 节点，很大程度的减少了 I/O 的次数。

InnoDB 中的聚簇索引严重依赖主键的插入顺序，否则会有页分裂的问题

乱序写入会导致 InnoDB 需要为新行寻找合适的位置，比如已经刷到磁盘上的目标页可能不得不再次被加载到内存中，造成大量的随机 I/O，并且不得不频繁的做页分裂操作，以便将新行插入到合适的位置，而频繁的页分裂，导致页变得稀疏并被不规则的填充，最终导致数据产生碎片。

因此最好将主键设置成自增的，并且时不时的 OPTIMIZE TABLE 来重建索引并优化页的填充。

覆盖索引

指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，就可以称之为实现了索引覆盖。这样避免了查到索引后再回表操作，减少 I/O 提高效率。

如果要查询的字段都是索引字段的话，因为字段作为索引本身已经包含了要查询的数据(或者说本身已经覆盖了要查询的数据)，这种字段会引起覆盖索引。

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
# 可以看到没有使用 where 条件访问， 结果发现命中了索引
mysql> explain select name from user \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: uniq_name
      key_len: 1022
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)
# 查询字段包含非索引列，发现全表扫描
mysql> explain select name,age from user \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)
# 如果想使用覆盖索引，那么绝对不能出现 select * 之类的查询语句
mysql> explain select * from user \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

关于回表
简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据索引上保存的主键 ID 再次到数据块里获取数据，如下图所示。

建议通过覆盖索引(即查询字段都有索引)来避免回表，覆盖索引本身在其非叶子节点已经包含了要查询的数据。

//能够走覆盖索引,无需回表
explain select id,name from `user` where name = '张三';
//能够走覆盖索引,无需回表
explain select name from `user` where name = '张三';
//查询字段中有非索引字段 age, 破坏了覆盖索引,导致回表.
//name这个索引的非叶子节点已经包含了本身的值 叶子节点则存储的是主键id,
//所以需要再次通过主键回到表中查询到对应的记录,然后获取到对应的age属性
explain select name,age from `user` where name = '张三';
//如果 age 和 name 建立联合索引 idx_name_age(name,age),则又可以走覆盖索引,无需回表
drop index idx_name on user;
create index idx_age_name on user(`age`,`name`);

锁

InnoDB锁

锁机制

InnoDB 有两种锁机制：乐观锁和悲观锁。

乐观锁 :

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号是否有冲突，有则处理，无则提交事务。

悲观锁：

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操，悲观锁是由数据库自己实现了的，大多数时候我们直接调用数据库的相关语句就可以了。

后文讲到的共享锁，排它锁，意向锁，间隙锁 都属于悲观锁的范畴。

锁粒度

行级锁：行锁是通过给索引上的索引项加锁来实现的，如果数据不是通过索引(主键索引，唯一索引，普通索引)检索出来上锁的话，这种上锁行为会升级成表级锁，InnoDB行锁有 共享锁，排它锁，间隙锁。
表级锁： InnoDB 表锁有 意向共享锁，意向排它锁。

锁粒度越小并发性能就越高，越合适做并发操作，锁粒度越小，完成某个功能需要的加锁，解锁次数就越多，会出现资源的恶性竞争，甚至发生死锁问题。

锁粒度越大，并发性能就越低，越适合做并发查询操作，当然锁的生命周期如果很长的话，并发性能也会很低，锁冲突的概率也高。

行锁的生命周期一般非常短暂，但是开启事务能延长行锁的生命周期直至事务提交或回滚。

行级锁

共享锁(share lock 也叫读锁/s锁)

如果一个事务对数据A施加了共享锁后，当前事务可以读写数据 A，，其他事务可以读取当前数据A，但是只能对数据 A 施加共享锁，不能加排他锁。

排它锁(exclusive lock 也叫写锁/x锁)

若一个事务对数据A加了排它锁后，当前事务可以读写数据 A，其他事物可以读取当前数据A，但不能对数据 A 施加排它锁和共享锁。

间隙锁

使用区间范围(>,<,in...,between...and..,) 检索数据时，对该区间范围施加排它锁或共享锁后，检索出来的记录会被施加排它锁或共享锁；而没有检索出记录叫做"间隙(GAP)"，这些间隙(空记录)也会被施加排它锁或共享锁，称之为 间隙锁(Next-Key)，间隙锁和索引密切相关，是最严格的行锁。
使用相等条件(=)检索出来的空记录，为其加锁的话，这种锁也是间隙锁。

间隙锁是通过参数 innodb_locks_unsafe_for_binlog 控制的，默认值为OFF，即启用间隙锁。此参数是只读模式，如果想要禁用间隙锁，需要修改配置文件 my.cnf。

间隙锁和死锁

由于符合查询条件但不存在的记录是空的，这就使事务 A 与事务 B 能同时对该空记录加锁(间隙锁)，但是如果事务 A 和事务 B 都试图添加一条『符合查询条件的记录』，此时会导致死锁状态。

间隙锁的作用

间隙锁可以防止幻读，根据间隙锁的定义可以看到，在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是读不到其他事物提交的在区间范围内的数据，这样就避免了幻读问题。

另一个作用是满足复制和恢复的需要，因为 Binlog 记录了各个事务提交的有先后顺序的 SQL 语句，恢复时也是按照顺序执行 SQL 语句的，因此就要确保其他并发事物不能插入满足锁定条件的数据。

共享锁和排他锁

可通过 select ... lock in share mode 为符合查询条件的记录加共享锁。
可通过 select ... for update 为符合查询条件的记录施加排它锁。InnoDB 会为事务中的更新操作 update，insert，delete 自动施加排它锁，对于查询，InnoDB 不会加任何锁。

排它锁和共享锁的共性是其他事务可以读取加锁的数据，但不能修改加锁的数据。

先看一下 user 表中的数据：

mysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | b    |  20 |
|  3 | c    |  30 |
+----+------+-----+
3 rows in set

事务 A 中对 id=1 索引出来的记录加共享锁：

# 事务 A
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set
mysql>

接下来看看对事务 B 中加锁行为的影响：

# 事务 B 可以对 id=1 的记录施加共享锁
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set
mysql>

# 事务 B 不可以对 id=1 的记录施加排它锁，会产生锁冲突直到超时
mysql> select * from user where id=1 for update;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql>

# 事务 B 可以对 age=10 非索引出来的数据加共享锁
mysql> select * from user where age=10 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set
# 事务 B 不可以对 age=10 非索引出来的数据加排它锁，尽管age=10的记录只有一条，但这种锁会升级为表写锁(锁粒度切换),
# 可以看做是意向排它锁，因此会和事务 A 中的共享锁冲突
mysql> select * from user where age=10 for update;
1205 - Lock wait timeout exceeded; try restarting transaction

# 事务 B 可以对除了 id=1 之外的索引出来的记录施加排它锁
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=2 for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | b    |  20 |
+----+------+-----+
1 row in set
mysql>

接着回到事务Ａ：

# 在事务 A 中加共享锁和排它锁
mysql> select * from user where id=1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set
mysql> select * from user where id=1 for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set
# 事务 B 不管加了事务 A 加了什么锁，事务 B 始终可以查询事务 A 中上了锁的记录
mysql> select * from user where id=1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set
mysql>

间隙锁

# 事务 A,通过索引检索id大于 7 这一条件范围的记录,发现不存在   
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id > 7 for update;
Empty set

# 事务 B,插入一条满足事务 A 条件范围的记录,发现被阻塞,说明间隙锁起作用了
mysql> insert into user values(10, 'asdf', 100);
1205 - Lock wait timeout exceeded; try restarting transaction

死锁

两个以上的并发事务在持有各自资源的锁的前提下(即未释放自身资源的锁)，尝试锁定对方加了锁的资源，导致事务之间产生了锁资源的循环等待，这种现象称之为死锁。

InnoDB 目前处理死锁的方法是，通过比较参与死锁问题的事务权重，将权重值最小的事务进行回滚，并释放琐，以便其他事务获得锁继续完成事务。

死锁示例

mysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | b    |  20 |
|  3 | c    |  30 |
+----+------+-----+
3 rows in set

事务 A 中修改 id=1 的记录(施加了排它锁)

mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set age=11 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

事务 B 中修改 id=2 的记录(施加了排它锁)

mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set age=21 where id=2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

回到事务 A 中，对 id=2 的记录修改，此时需要等待事务 B 释放 id=2 的排它锁，发现被卡住了，出现了锁等待的现象

# 因为事务 B 对 id=2 的记录作用了排它锁还未释放,因此事务 A 被卡住了，在等着事务 B 释放琐
mysql> update user set age=21 where id=2;

回到事务 B 中，对 id=1 记录修改，此时需要等待事务 A 释放 id=1 的排它锁，结果形成了一个环路等待导致死锁产生。

mysql> update user set age=11 where id=1;
1213 - Deadlock found when trying to get lock; try restarting transaction

根据上述死锁示例，我们可以设想

假如三个事务插入同样一行记录，其中一个先拿到了排他锁开始插入并提交事务后，那么后面的两个事务会出现主键重复(Duplicate Key)错误，而此时它们会同时成功的获取该行的共享锁用于检测冲突;

如果第一个事务回滚，后面的两个事务一样会同时成功的获取共享锁，接着两个事务会同时申请该行的排他锁，由于这两个事务已经获取该记录的共享锁，因此这两个事务之间又会出现等待对方释放锁的情况，也就产生了死锁。

间隙锁导致死锁示例
事务 A 对 id=2 这条不存在的记录加锁，这就生成了间隙锁

# 事务 A
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=2 for update;
Empty set

事务 B 中同样对 id=2 这条不存在的记录加锁，也生成了间隙锁

# 事务 B
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=2 for update;
Empty set

回到事务 A，由于事务 A 已经知道不存在这条 id=2 的记录，于是插入这条满足查询条件的记录，结果出现了锁等待

# 事务 A
mysql> insert into user values(2, 'b', 20);

这是因为和事务 B 中的间隙锁冲突了，因为事务中的 insert 会自动变为排它锁，这时的事务 A 状态是等着事务 B 释放琐。

回到事务 B，事务 B 也知道这条记录不存在，尝试插入它，结果出现了死锁

# 事务 B
mysql>  insert into user values(2, 'b', 20);
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql>

这是由于事务 B insert 获取的排它锁导致和事务 A 的间隙锁也冲突了,这一刻事务 B 也想等着事务 A 释放琐, 但是由于上面事务 A 也在等着事务 B释放琐,因此形成了循环等待, 导致了死锁, 数据库立马回滚了事务 B。

数据库死锁回滚事务，这是数据库自身的行为，这个我们后面会讲到。

最后我们去看事务 A 发现成功执行了 insert 语句获取了排它锁

# 事务 A
mysql> insert into user values(2, 'b', 20);
Query OK, 1 row affected
mysql>

更多死锁的场景见这里。

如何预防死锁

尽量使用较低的事务隔离级别，隔离级别越高，锁冲突的概率越大，尽管锁冲突不一定导致死锁，但死锁一定伴随着锁冲突，大部分应用使用 Read Commited 隔离级别就足够了。

不同事务并发存取多个表或多行数据时，应尽量以相同的顺序访问数据(避免出现循环等待)，这样可以大大减少死锁的机会。

通过锁等待超时参数 innodb_lock_wait_timeout 设置何时的锁等待超时阈值，如果大量的事务无法获取到锁而被阻塞，会占用大量的服务器资源，降低服务器的性能。

对于涉及多个的事务或大批量的更新数据，可以使用表锁来提高处理速度，以减少锁冲突的可能。使用 LOCK TABLES 对 InnoDB 加表锁时，要设置 autocommit=0，提交事务的 commit 命令要在 UNLOCK TABLES 之前执行，因为 UNLOCK TABLES 会隐式的提交事务。

索引的离散度尽可能的大些，这样加锁就能更精确，从而减少锁冲突的机会。

解除死锁
第一种

mysql> show full processlist \G
...
*************************** 5. row ***************************
     Id: 240
   User: root
   Host: localhost:52395
     db: tmp
Command: Sleep
   Time: 513
  State:
   Info: NULL
*************************** 6. row ***************************
     Id: 242
   User: root
   Host: localhost:52444
     db: tmp
Command: Query
   Time: 9
  State: statistics
   Info: select * from user where id=1 for update
*************************** 7. row ***************************
     Id: 243
   User: root
   Host: localhost:52474
     db: tmp
Command: Query
   Time: 6
  State: updating
   Info: update user set age=11 where id=1
*************************** 8. row ***************************
     Id: 245
   User: root
   Host: localhost:52876
     db: tmp
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
8 rows in set (0.00 sec)

Id 列是线程id，Commond 是命令类型，出现锁冲突的命令类型一般都是 Query ，Time 表示该线程的执行耗时，通过这几个参数我们就可以知道哪些线程的锁有冲突了，那么可以通过 kill id 杀死该线程。

第二种

# 1查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
# 2查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 3查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
# 4杀死进程
kill 进程ID

查询结果中各字段的含义见这里。

表级锁

意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁，意向锁是InnoDB自动加的，不需要用户干预。

意向锁的作用

意向锁是为了减少表锁和行锁的冲突引入的，如果事务 A 对表 T 加锁，就必须遍历表 T 下面每一行数据是否加了共享锁或排它锁，这样做的性能非常低。因此才引入了意向锁，这样一旦有数据行被加锁，会在表层面的意向锁上体现出来，这样就无需遍历数据行加锁了。

意向锁的分类

意向共享锁（IS）：向 InnoDB 表某些记录施加行级共享锁时，InnoDB 会自动的为表施加 IS锁。
意向排它锁（IX）：向 InnoDB 表某些记录施加行级排他锁时，InnoDB 会自动的为表施加 IX锁。

MyISAM 锁

锁机制

MyISAM 使用的是表锁，有两种锁机制：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

MyISAM 一般会自动的给查询加读锁，为更新操作加写锁，至于 MyISAM 提供显示加锁的功能其实是为了模拟事务，实现某一时间点多个表的一致性读取。

MyISAM 存储引擎支持并发插入，以减少给定表的读和写操作之间的争用，MyISAM 总是一次获得所有 SQL 语句所需要的锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

锁行为

lock tables table_name read/write [local] [low_priority]：给表加锁，如果表被其他进程锁定，则当前进程会等待，直到可以获取所有锁定为止。

local ：local 的作用是允许 MyISAM 并发插入，即当你持有表的读锁时，其他用户可以在满足 MyISAM 表并发插入条件(concurrent_insert > 0)的情况下，在表尾并发插入记录。
并发插入的条件是，主键必须是连续的，如果你删除了中间的某些数据导致主键不连续，则并发插入会被禁用。

low_priority：降低语句的优先级，默认情况下写锁的优先级要高于读锁，但是可以通过设置 low_priority 来降低写锁的优先级。

unlock table_name：给表解锁，当前进程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前进程锁定的表会被隐含地解锁。

MyISAM 还有一个系统变量 concurrent_insert 用以控制并发插入的行为

concurrent_insert=0 时不允许并发插入。

concurrent_insert=1 默认，要求主键必须是连续的，如果你删除了中间的某些数据导致主键不连续，则并发插入会被禁用。

concurrent_insert=2，无论主键是否连续，都允许在表尾并发插入记录。

加锁时需要注意：

当前进程对表加了读锁后，其他进程可以读取表的数据，也可以对表加读锁，但不能加写锁。

但如果当前进程对表加了写锁，其他进程是无法读取表数据的，也不能对表加任何锁，这点和 InnoDB 的排它锁不一样，排它锁允许其他进程读数据。

会话在执行 LOCK TABLES 后，只能访问显式加锁了的表，不能访问未加锁的表。

如果需要为一个表同时加读锁和写锁，那么需要为该表起两个别名，以区分读锁与写锁

先准备一张表test
mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  1 |    10 |
|  2 |    20 |
|  3 |    30 |
+----+-------+
3 rows in set
# 可以这样同时加读锁和写锁
local tables test as t_a read,test as t_b write;

锁行为示例

在会话 A 中对表加共享读锁：

# 会话 A
mysql> lock tables test read;
Query OK, 0 rows affected
mysql>

接着来到会话B：

# 会话 B,可以看到能加共享读锁,也能读数据,但是加独占写锁时被卡住了
mysql> lock tables test read;
Query OK, 0 rows affected
mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  1 |    10 |
|  2 |    20 |
|  3 |    30 |
+----+-------+
3 rows in set
# 加独占锁
mysql> lock tables test write;

# 会话 B,无法更新数据,被卡住了
mysql> update test set score=11 where id=1;

在会话 A 中对表加独占写锁：

# 会话 A
mysql> lock tables test write;
Query OK, 0 rows affected
mysql>

在会话 B 中读取数据：

# 会话 B,可以看到卡住了,说明加了独占锁的表不能对其上锁也不能读它的数据
mysql> select * from test;

read local 并发插入(主键连续)：

# 事务 A, 使用 read local 加读锁
mysql> lock tables test read local;
Query OK, 0 rows affected
mysql>

# 事务 B, 可以看到是能够插入数据的
mysql> insert into test values(4,40);
Query OK, 1 row affected
mysql>

read local 并发插入(主键不连续)：

# 我们把 id=2 的记录删除，造成主键不连续
mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  1 |    11 |
|  3 |    30 |
|  4 |    40 |
+----+-------+
3 rows in set

# 会话 A ,read local
mysql> lock tables test read local;
Query OK, 0 rows affected
mysql>

# 会话 B,被卡住了无法插入数据
mysql> insert into test values(4,40);

只能访问当前会话 lock 了的表：

mysql> lock tables test read;
Query OK, 0 rows affected
mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  1 |    11 |
|  5 |    40 |
|  3 |    30 |
|  4 |    40 |
+----+-------+
4 rows in set
# user 表未被lock，所以报错
mysql> select * from user;
1100 - Table 'user' was not locked with LOCK TABLES
mysql>

查看表锁争用情况

# 如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况
mysql> SHOW STATUS LIKE 'Table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 264   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 1     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set

事务

事务控制

Mysql 控制事务的语句如下

start transaction 或 begin ：开启事务，相当于隐式的关闭了自动提交。

commit 和 rollback ：前者用来提交事务，后者用来回滚事务，需要注意事务中的 truncate 命令是无法被回滚的。

set autocommit ：设置是否开启自动提交，默认为 1 开启，设置为 0 相当于显示的关闭了自动提交。

该变量是会话变量，客户机 A 设置了该变量不会影响客户机 B。

事务控制示例

mysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  3 | c    |  30 |
|  5 | e    |  50 |
|  7 | g    |  60 |
+----+------+-----+
4 rows in set

事务 A	事务B
mysql> select * from user where id=10; Empty set	mysql> select * from user where id=10; Empty set
mysql> start transaction; Query OK, 0 rows affected mysql> insert into user values(10, 'ten', 100); Query OK, 1 row affected mysql>
	mysql> select * from user where id=10; Empty set
mysql> commit; Query OK, 0 rows affected
	mysql> select * from user where id=10; id \| name \| age 10 \| ten \| 100 1 row in set

事务的保存点

事务保存点是在事务中埋的一些标志位，用来实现事务的部分提交或部分回滚。

release savepoint 保存点名; 可以删除一个保存点。

在下面这个示例中你会看到，当回滚保存点后，发现最终只插入了保存点之前的事务语句。

mysql> start transaction;
Query OK, 0 rows affected
mysql> 
mysql> insert into user values(11, 'eleven', 111);
Query OK, 1 row affected
mysql> 
mysql> savepoint pt;
Query OK, 0 rows affected
mysql> insert into user values(12, 'twelve', 120);
Query OK, 1 row affected
mysql> 
mysql> select * from user;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | a      |  10 |
|  3 | c      |  30 |
|  5 | e      |  50 |
|  7 | g      |  60 |
| 10 | ten    | 100 |
| 11 | eleven | 111 |
| 12 | twelve | 120 |
+----+--------+-----+
7 rows in set
mysql> rollback to pt;
Query OK, 0 rows affected
mysql> commit;
Query OK, 0 rows affected
mysql> select * from user;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | a      |  10 |
|  3 | c      |  30 |
|  5 | e      |  50 |
|  7 | g      |  60 |
| 10 | ten    | 100 |
| 11 | eleven | 111 |
+----+--------+-----+
6 rows in set

事务的特性(ACID)

事务具有以下 4 个特性，简称事务的 ACID 属性。

原子性(atomicity)：事务是一个原子，其对数据的修改，要么全部执行，要么全都不执行。

一致性(consistency)：在事务开始和完成时，数据都必须保持一致状态，例如银行卡 A 转账到银行卡 B，如果从卡 A 扣了100块，那么卡 B 就应该多100块，这就是一致性。

隔离性(isolation)：同时有多个事务执行时，相互之间不能有影响，隔离性通过隔离级别实现。

持久性(durability)：事务完成后，对数据的修改时永久性的，即使出现系统故障也能够保持。

事务的隔离级别

事务的隔离级别用来解决事务的并发问题，事务并发引起的常见问题如下：

更新丢失：两个或多个事务修改同一行数据时，一个事务了覆盖了另一个事务做出的修改。

脏读(dirty read)：一个事务可以读到另一个事务未提交的数据。

不可重复读(non-repeatable read)：同一个事务内，相同的两条查询语句，第二次的查询结果和第一次的不一致(被删或被修改)。

幻读(phantom read)：同一个事务内，相同的两条查询语句，第二次查询到了之前不存在的记录(被插入了满足查询条件的记录)。

SQL 标准定义了4种隔离级别用来解决上述的并发问题，这四种隔离级别逐渐增强

read uncommitted(RU，未提交读)：这种隔离级别，所有事务都能看到其他事务未提交的数据，无法规避任何一个事务并发问题，因此这种隔离级别很少用于实际应用中。

read committed(RC,读取提交的数据)：这种隔离级别，所有事务只能读取其他事务提交后数据，可以有效避免脏读的问题，但无法避免不可重复读和幻读的问题。

repeatable read(RR，可重复读)：mysql 默认的隔离级别，这种隔离界别，就是用来解决不可重复读的问题，可重复读基于 mvcc 实现，具体细节见这里。

serializable(串行化)：这是最高的隔离级别，它为每一条 sql 施加一个共享锁，使之不可能相互冲突，但带来的问题是可能造成大量的锁等待现象，这种隔离级别主要用于 Innodb 的分布式事务。

隔离级别只是一种规范，而隔离级别的具体实现则是由它下面的各种锁来实现的，隔离级别越高，加锁就越严格，产生锁冲突的概率就越高，对并发事物的性能影响也就越大。

测试事务的隔离级别

查看和修改隔离级别的命令如下

# 查看隔离级别
show variables like '%tx_isolation%' 可以查看当前数据库的隔离级别
# 修改隔离级别
set global|session transaction isolation level 隔离级别

示例1 ：设置事务A和事务B的隔离级别为 read uncommitted

# 事务 A,修改 id=11 数据，未提交事务
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set age=110 where id=11;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

# 事务 B,读取到了事务 A 未提交的数据,也就是出现了脏读
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=11;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 11 | eleven | 110 |
+----+--------+-----+
1 row in set

示例2 ：升级事务A和事务B的隔离级别为 read committed

# 事务 A,修改 id=11 数据，未提交事务
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set age=110 where id=11;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

# 事务 B,没有读取事务 A 中未提交的数据,因此该隔离级别解决了脏读的问题
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=11;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 11 | eleven | 111 |
+----+--------+-----+
1 row in set

那如果事务 A 中删除 id=11 的这条记录呢？看下会不会影响事务 B 中对这条记录的读取

# 事务A，删除 id=11 的这条记录，并提交事务
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set age=110 where id=11;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 
mysql> delete from user where id=11;
Query OK, 1 row affected
mysql> 
mysql> commit;
Query OK, 0 rows affected

# 事务 B, 可以看到对 id=11 这条记录前后两次的查询结果不一致，不可重复读的问题出现了
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=11;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 11 | eleven | 111 |
+----+--------+-----+
1 row in set
mysql> select * from user where id=11;
Empty set

示例3：升级事务A和事务B的隔离级别为 repeatable read

先在事务 B 中查看 id=7 的这条记录

# 事务 B
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=7;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  7 | g    |  60 |
+----+------+-----+
1 row in set

然后在事务 A 中删除这条记录,并提交事务

# 事务 A 
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> delete from user where id=7;
Query OK, 1 row affected
mysql> commit;
Query OK, 0 rows affected

回到事务 B，再次查看下这条记录是否存在

# 事务 B，可以看到事务提交的删除并未影响到事务 B 对 id=7 这条记录重复读取,因此该隔离级别解决了不可重复读的问题
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id=7;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  7 | g    |  60 |
+----+------+-----+
1 row in set
mysql> select * from user where id=7;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  7 | g    |  60 |
+----+------+-----+
1 row in set

示例4：mysql 使用间隙锁解决幻读问题

# 事务A: 通过索引检索id大于 7 这一条件范围的记录,发现不存在   
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where id > 7 for update;
Empty set

# 事务B: 插入一条满足事务 A 条件范围的记录,发现被阻塞,说明间隙锁起作用了
mysql> insert into user values(10, 'asdf', 100);
1205 - Lock wait timeout exceeded; try restarting transaction

事务与锁的关系

我们知道 InnoDB 会为事务中的更新操作 update，insert，delete 自动施加排它锁。

事务间的修改操作成功与否有三种情况：

事务A和B都不是通过索引修改数据，这种会发生锁粒度的切换，两个事务的排他锁会升级为表锁，会阻塞其他事务任何修改操作。
事务A通过索引修改数据，事务B通过非索引修改数据，事务B的排他锁会升级为表锁，因此事务B的操作会被被阻塞(因为事务A加的锁还未释放)。
事务A和B都通过索引修改数据(前提是二者修改的不是同一行数据)，不会发生锁粒度的切换，二者的修改操作都能成功。

查看 test 表的数据：

mysql> select * from test;
+----+-----+------+-------+
| id | num | name | title |
+----+-----+------+-------+
|  1 | 100 | a    | b     |
|  2 | 200 | aa   | bb    |
+----+-----+------+-------+
2 rows in set (0.31 sec)

第1种情况：事务A和B都通过非索引修改数据。

# 事务A
mysql> start transaction;
Query OK, 0 rows affected (0.14 sec)
mysql> update test set num=111 where name="aa";
Query OK, 0 rows affected (0.16 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 事务B
mysql> start transaction;
Query OK, 0 rows affected (0.12 sec)
# 发现该操作被阻塞(两个事务都是表锁)
mysql> update test set num=222 where name="bb";
1205 - Lock wait timeout exceeded; try restarting transaction

第2种情况：事务A通过索引修改，事务B通过非索引修改数据。

# 事务A
mysql> start transaction;
Query OK, 0 rows affected (0.14 sec)
mysql> update test set num=111 where id=1;
Query OK, 0 rows affected (0.16 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 事务B
mysql> start transaction;
Query OK, 0 rows affected (0.12 sec)
# 发现该操作一样被阻塞(事务B是表锁)
mysql> update test set num=222 where name="bb";
1205 - Lock wait timeout exceeded; try restarting transaction

第3种情况：事务A和B都通过索引修改数据。

# 事务A
mysql> start transaction;
Query OK, 0 rows affected (0.14 sec)
mysql> update test set num=111 where id=1;
Query OK, 0 rows affected (0.16 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 事务B
mysql> start transaction;
Query OK, 0 rows affected (0.12 sec)
# 成功执行(两个事务都是行锁，并且互不影响)
mysql> update test set num=222 where id=2;
Query OK, 0 rows affected (0.16 sec)
Rows matched: 1  Changed: 0  Warnings: 0

再看一个事务与间隙锁的示例：

# 事务A 更新 id > 2 的数据(此时表中不存在id>2的数据)
mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)
mysql> update test set num = 300 where id > 2;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 0  Changed: 0  Warnings: 0

# 事务 B 尝试插入 id=3 的数据发现被阻塞，说明间隙锁起作用了
mysql> start transaction;
Query OK, 0 rows affected (19.01 sec)
mysql> insert into test values(3, 300, "aaa", "bbb");
1205 - Lock wait timeout exceeded; try restarting transaction