蓁祎家进步中

# 常见处理

back

# 8 种最坑的 SQL 错误用法，你有没有踩过坑

back

# LIMIT语句

可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？

在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：

# 隐式转换

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。

explain extended select * from my_table mt where mt.bnp=111111 and mt.a is null;
1

其中字段 bpn 的定义为 varchar(20)，MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。

# 关联更新、删除

# 混合排序

# EXISTS语句

去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

# 条件下推

外部查询条件不能够下推到复杂的视图或子查询的情况有：

  • 聚合子查询；
  • 含有 LIMIT 的子查询；
  • UNION 或 UNION ALL 子查询；
  • 输出字段中的子查询；

# 提前缩小范围

# 中间结果集下推

# 关系型数据库设计注意点

back

  • 1、关系型数据库，由多个数据表构成。每一个数据表的结构是相同的，不同表之间可能存在关联关系。表之间的关联关系，正是关系型数据库得名的原因。 每一张数据库的表，都使用自增长的id字段作为物理主键。
    多表之间的外键关联，都关联其他表的物理主键，也就是关联其他表的id字段。
    逻辑主键，就是除了id字段外的不重复的字段。我们设计数据库的外键关联时，不使用逻辑主键，而是使用物理主键。 这是因为，物理主键肯定是主键，因为它是自增的。
    而逻辑主键，则可能随着业务的发展，成为可重复的字段。一旦这种情况发生，关系数据库的外键关系就被破坏了。如，可能你觉得姓名是逻辑主键，但可能后面发现有人重名，那么这个字段就不再是主键了。而如果之前使用这个字段作为外键，那么查询返回的数据就不是一一对应的了。
    这就是为什么要使用物理主键，以及用物理主键作为关联外键的原因

  • 2、考虑这样一种数据库的应用场景：
    任务单的接管：多个操作人员从一个任务池中接管一个任务。只有第一个获取任务的操作人员才能成功接管该任务，后续操作人员的接管操作都必须失败。这时，就需要一种数据库记录的锁定机制。只有第一个事务才能更新记录
    数据库可以使用悲观锁和乐观锁来锁定数据库记录。
    悲观锁是如下sql语句实现的：

    SELECT - FROM t FOR UPDATE
1

这条语句会在其他修改内容的事务提交后返回最新的数据。 一旦执行这条语句，这些记录就被锁住了，不能被其他sql事务修改。直到本事务提交。
乐观锁，是应用程序实现的，不是数据库实现的机制。乐观锁，对于数据库来说，就是没有上锁。事务可以select其他事务已经提交的数据。更新数据时，数据库保证多个事务的更新是原子的。 悲观锁，会导致事务等待其他事务完成。乐观锁，只会等待其他事务的更新语句的完成，不会等待整个事务完成，因此效率较高

实现乐观锁的方法：
给数据库表添加一个version字段。version是一个数字类型的字段，每次更新都加1。每次更新时都要检测version字段是否和当前事务的值相同。如果version字段不同，那么就表明在查询数据之后，有其他事务已经更新了该记录，就会导致此次更新失败。应用必须重新载入最新的数据，然后重新更新数据。
如果使用乐观锁，那么如果数据库中version和应用中version相同，则用version+1的版本值更新version字段。

SQL语句如下：

update studentVersion set ver=?, name=? where id=? and ver=?
1
  • 3、不使用锁更新独立状态的记录
    考虑这样一种数据库的应用场景：
    需要更新虚拟机的状态。多个事务可能会同时更新虚拟机的状态为start或者stop。这种状态的更新和前一个阶段的状态是无关的，因此不需要锁定记录。直接更新即可。此时不需要使用悲观锁或者乐观锁。
    如果这个表添加了version字段，直接忽略对version字段的比较和更新即可。
    SQL语句如下：
update studentVersion set   name=? where id=?
1

# 总结

设计关系型数据库的表时，需要给表添加一个ID字段（自增字段，或者uuid字段）和一个version字段（数值类型）。ID字段作为物理主键，用于保证记录的不可重复性和用作外键关联。
version字段用于实现乐观锁，提供比悲观锁更好的性能。特别是对于UI显示并可能出现并发更新的数据，更需要使用乐观锁来提升数据库访问性能。
对于后台自动更新的任务，可以使用乐观锁实现。但需要在冲突发生时实现自动退让。也可以使用悲观锁在数据库上对事务进行排队来解决更新冲突问题。
对于不关心记录的状态之间关系的场景，可以直接更新记录，忽略掉version字段的检测和更新

# SQL中的case用法

back

Case具有两种格式。简单Case函数和Case搜索函数。

  • 简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END
1
2
3
4
5
6
7
8

这两种方式，可以实现相同的功能。
简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。

还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

比如说，下面这段SQL，你永远无法得到“第二类”这个结果

CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
         WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' END
1
2
3

下面我们来看一下，使用Case函数都能做些什么事情。

  • 一，已知数据按照另外一种方式进行分组，分析。 有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key)
国家（country） 人口（population）
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。

洲 人口
亚洲 1100
北美洲 250
其他 700

想要解决这个问题，你会怎么做？生成一个带有洲Code的View，是一个解决方法，但是这样很难动态的改变统计的方式。 如果使用Case函数，SQL代码如下:

SELECT  SUM(population),
        CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下:

SELECT
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600  THEN '2'
             WHEN salary > 600 AND salary <= 800  THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END salary_class,
        COUNT(*)
FROM    Table_A
GROUP BY
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600  THEN '2'
             WHEN salary > 600 AND salary <= 800  THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 二，用一个SQL语句完成不同条件的分组

有如下数据

国家（country） 性别（sex 人口（population）
中国 1 340
中国 2 260
美国 1 45
美国 2 55
加拿大 1 51
加拿大 2 49
英国 1 40
英国 2 60

按照国家和性别进行分组，得出结果如下

国家 男 女
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60

普通情况下，用UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个Select部分)，而且SQL语句会比较长。 下面是一个是用Case函数来完成这个功能的例子

SELECT country,
       SUM( CASE WHEN sex = '1' THEN
                      population ELSE 0 END),  --男性人口
       SUM( CASE WHEN sex = '2' THEN
                      population ELSE 0 END)   --女性人口
FROM  Table_A
GROUP BY country;
1
2
3
4
5
6
7

这样我们使用Select，完成对二维表的输出形式，充分显示了Case函数的强大。

# 在Check中使用Case函数

在Check中使用Case函数在很多情况下都是非常不错的解决方法。可能有很多人根本就不用Check，那么我建议你在看过下面的例子之后也尝试一下在SQL中使用Check。 下面我们来举个例子 公司A，这个公司有个规定，女职员的工资必须高于1000块。如果用Check和Case来表现的话，如下所示

CONSTRAINT check_salary CHECK
           ( CASE WHEN sex = '2'
                  THEN CASE WHEN salary > 1000
                        THEN 1 ELSE 0 END
                  ELSE 1 END = 1 )
如果单纯使用Check，如下所示
CONSTRAINT check_salary CHECK
           ( sex = '2' AND salary > 1000 )
1
2
3
4
5
6
7
8

女职员的条件倒是符合了，男职员就无法输入了。

# 根据条件有选择的UPDATE

例，有如下更新条件 工资5000以上的职员，工资减少10% 工资在2000到4600之间的职员，工资增加15% 很容易考虑的是选择执行两次UPDATE语句，如下所示
--条件1

UPDATE Personnel
SET salary = salary - 0.9
WHERE salary >= 5000;
1
2
3

--条件2

UPDATE Personnel
SET salary = salary - 1.15
WHERE salary >= 2000 AND salary < 4600;
1
2
3

但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。接下来运行第二个SQL时候，因为 这个人 的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。如果要是反过来执行，那么工资4600的人相反会变成减少工资。暂且不管这个 规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下:

UPDATE Personnel
SET salary = CASE WHEN salary >= 5000
　            THEN salary - 0.9
WHEN salary >= 2000 AND salary < 4600
THEN salary - 1.15
ELSE salary END;
1
2
3
4
5
6

这里要注意一点，最后一行的ELSE salary是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。
这种方法还可以在很多地方使用，比如说变更主键这种累活。
一般情况下，要想把两条数据的Primary key,a和b交换，需要经过临时存储，拷贝，读回数据的三个过程，要是使用Case函数的话，一切都变得简单多了。 p_keycol_1col_2a1张三b2李四c3王五 假设有如上数据，需要把主键a和b相互交换。用Case函数来实现的话，代码如下

UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
1
2
3
4
5
6
7

同样的也可以交换两个Unique key。需要注意的是，如果有需要交换主键的情况发生，多半是当初对这个表的设计进行得不够到位，建议检查表的设计是否妥当。

# 两个表数据是否一致的检查

Case函数不同于DECODE函数。在Case函数中，可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS，可以进行子查询，从而 实现更多的功能。
下 面具个例子来说明，有两个表，tbl_A,tbl_B，两个表中都有keyCol列。现在我们对两个表进行比较，tbl_A中的keyCol列的数据如果 在tbl_B的keyCol列的数据中可以找到， 返回结果'Matched',如果没有找到，返回结果'Unmatched'。
要实现下面这个功能，可以使用下面两条语句

--使用IN的时候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )
THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT - FROM tbl_B
WHERE tbl_A.keyCol = tbl_B.keyCol )
THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
1
2
3
4
5
6
7
8
9
10
11
12
13

使用IN和EXISTS的结果是相同的。也可以使用NOT IN和NOT EXISTS，但是这个时候要注意NULL的情况。

# 在Case函数中使用合计函数

假设有下面一个表

学号(std_id) 课程ID(class_id) 课程名(class_name) 主修flag（main_class_flg)
100 1 经济学 Y
100 2 历史学 N
200 2 历史学 N
200 3 考古学 Y
200 4 计算机 N
300 4 计算机 N
400 5 化学 N
500 6 数学 N

有的学生选择了同时修几门课程 (100,200)也有的学生只选择了一门课程(300,400,500)。选修多门课程的学生，要选择一门课程作为主修，主修flag里面写入 Y。只选择一门课程的学生，主修flag为N(实际上要是写入Y的话，就没有下面的麻烦事了，为了举例子，还请多多包含)。 现在我们要按照下面两个条件对这个表进行查询

只选修一门课程的人，返回那门课程的ID 选修多门课程的人，返回所选的主课程ID

简单的想法就是，执行两条不同的SQL语句进行查询。
条件1

--条件1：只选择了一门课程的学生
SELECT std_id, MAX(class_id) AS main_class
FROM Studentclass
GROUP BY std_id
HAVING COUNT(*) = 1;
STD_ID   MAIN_class
------   ----------
300      4
400      5
500      6
1
2
3
4
5
6
7
8
9
10

条件2

--条件2：选择多门课程的学生
SELECT std_id, class_id AS main_class
FROM Studentclass
WHERE main_class_flg = 'Y' ;
如果使用Case函数，我们只要一条SQL语句就可以解决问题，具体如下所示
SELECT  std_id,
CASE WHEN COUNT(*) = 1  --只选择一门课程的学生的情况
THEN MAX(class_id)
ELSE MAX(CASE WHEN main_class_flg = 'Y'
THEN class_id
ELSE NULL END
)
END AS main_class
FROM Studentclass
GROUP BY std_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
STD_ID MAIN_class
100 1
200 3
300 4
400 5
500 6

通过在Case函数中嵌套Case函数，在合计函数中使用Case函数等方法，我们可以轻松的解决这个问题。使用Case函数给我们带来了更大的自由度。
最后提醒一下使用Case函数的新手注意不要犯下面的错误

CASE col_1
WHEN 1    　   THEN 'Right'
WHEN NULL  THEN 'Wrong'
END
1
2
3
4

在这个语句中When Null这一行总是返回unknown，所以永远不会出现Wrong的情况。因为这句可以替换成WHEN col_1 = NULL，这是一个错误的用法，这个时候我们应该选择用WHEN col_1 IS NULL。

# 报字符集不匹配问题

case t.CUR_UNIT when '万元' then 10000 when '亿元' then 100000000 end
1

后查找原因发现涉及数据库中字段CUR_UNIT为nvarchar2类型，导致进行对比的字段类型不符。网上的解决方式一般是类型转换to_char 或者cast 等等。

但是也可以改成：

case when t.CUR_UNIT ='万元' then 10000 when t.CUR_UNIT ='亿元' then 100000000 end
1

这样。

# 字符集nvarchar

Oracle中在做字符匹配时 遇到 NVARCHAR2 类型时报错，提示 字符集不匹配。 对使用 NVARCHAR2 的地方，需要对字段进行字符转换，加上to_char(nvarchar2字段)即可。

昨天在重建一个视图的时候，突然间就报了个ORA-12704字符集不匹配的错误。开始的时候我很是愕然，以为ORACLE是不是傻了，为什么会报这样的一个错误。 后来上网查了下，发现是由于NVARCHAR2与VARCHAR2的问题。以下是官方文档中关于这两种数据类型的介绍： 水平有限，翻译的大概意思我是这样理解的：

1.VARCHAR2(size [BYTE | CHAR])是可变长度的字符，最大长度为size个字节或者字符。最大长度为4000，最小长度为1。必须要指定VARCHAR2的长度。

BYTES表明表将会有字节长度的语义。CHAR表明列将有字符语句。

2.NVARCHAR2(size)，可变长度UNICODE字符串的最大长度为size个字符。字节的长度当编码为AL16UTF16时可以是2倍，当编码是UTF8时为3倍。 最大长度决定于国家字符集（national character set）的定义，最大为4000个字节。必须要为NVARCHAR2指定长度。 也就是说，
相同点：

  • a.两者都可用以定义可变长度的字符串
  • b.两者的最大长度都是4000
  • c.如果存储英文或者数字等两者基本可以等同

不同点：
NVARCHAR2的长度是根据国家字符集来决定的。而VARCHAR2的单位有两种，分别是字节和字符。
解决方法：

1.TO_CHAR(NVARCHAR2_TYPE) 将NVARCHAR2转成VARCHAR
2.N'VARCHAR2_TYPE' 将字符转成UNICODE字符

# oracle日期

back

  • 1、to_char的用法
select to_char(sysdate,'yyyy.mm.dd') from dual
select to_char(sysdate,'yyyy-mm-dd') from dual
select to_char(sysdate,'yyyy mm dd') from dual
select to_char(sysdate,'yyyy*mm*dd') from dual
select to_char(sysdate,'yyyymmdd') from dual
1
2
3
4
5

只要被转换的是一个日期，yyyy,mm,dd中间加不加连接符，加什么连接符都可以。我们可以把空格理解为一个空的连接符。

  • 2、to_date

①带连接符

select to_date('2012.07.26','yyyy-mm-dd') from dual
select to_date('2012.07.26','yyyy.mm.dd') from dual
select to_date('2012.07.26','yyyy mm dd') from dual
select to_date('2012-07-26','yyyy-mm-dd') from dual
select to_date('2012-07-26','yyyy.mm.dd') from dual
select to_date('2012-07-26','yyyy mm dd') from dual
select to_date('2012.7.1','yyyy-mm-dd') from dual
select to_date('2012.7.1','yyyy.mm.dd') from dual
select to_date('2012.7.1','yyyy mm dd') from dual
1
2
3
4
5
6
7
8
9

所谓带连接符，就是我们to_date的第一个参数的yyyy,mm,dd之间有没有连接符。如果有，那么第二个参数必须有连接符，随便是什么连接符。而且mm,dd不一定要对应位数。07可以对应mm,7同样可以对应mm。我们可以把空格理解为一个空的连接符。

②不带连接符

select to_date('20120726','yyyymmdd') from dual
select to_date('20120726','yyyy.mm.dd') from dual
select to_date('20120726','yyyy-mm-dd') from dual
1
2
3

不带连接符的必须一一位数对应，第二个字符串带不带连接符均可。07可以匹配mm，但7就不可以匹配mm
③①②中情况查出的结果都是带'-'的，无论有无连接符，连接符是什么。看截图:

# mysql与oracle日期转换对比

back

# mysql日期和字符相互转换方法

date_format(date,'%Y-%m-%d') -------------->oracle中的to_char(); str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date();

%Y：代表4位的年份
%y：代表2为的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)
%H：代表小时,格式为(00……23)
%k：代表 小时,格式为(0……23)
%h： 代表小时,格式为(01……12)
%I： 代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)

# Oracle中 to_char()函数

使用TO_CHAR函数处理数字
TO_CHAR(number, '格式')
TO_CHAR(salary,’$99,999.99’);

使用TO_CHAR函数处理日期
TO_CHAR(date,’格式’);

数字格式格式

9 代表一个数字
0 强制显示0
$ 放置一个$符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符

日期格式

格式控制 描述
YYYY、YYY、YY 分别代表4位、3位、2位的数字年
YEAR 年的拼写
MM 数字月
MONTH 月的全拼
MON 月的缩写
DD 数字日
DAY 星期的全拼
DY 星期的缩写搜索
AM 表示上午或者下午
HH24、HH12 12小时制或24小时制
MI 分钟
SS 秒钟
SP 数字的拼写
TH 数字的序数词

日期例子：

SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL
1
2
3
4
5
6

# 关于日期相等的传说

# 闰年

# 输出1
select 1 from dual where to_date('2024-02-29', 'yyyy-mm-dd') - interval '1' year = to_date('2023-02-28' , 'yyyy-mm-dd'); 
# 输出1
select 1 from dual where to_date('2024-02-28', 'yyyy-mm-dd') - interval '1' year = to_date('2023-02-28' , 'yyyy-mm-dd'); 
# 输出NULL
select 1 from dual where to_date('2024-02-27', 'yyyy-mm-dd') - interval '1' year = to_date('2023-02-28' , 'yyyy-mm-dd');
1
2
3
4
5
6

# 查询最近7天时间

SELECT to_char(SYSDATE - LEVEL + 1, 'yyyy-mm-dd') as createTime FROM DUAL connect BY LEVEL <= 7;
1

# 死锁

两个事务互相等待对方持有的锁，导致互相阻塞，从而导致死锁。

com.baomidou.mybatisplus.extension.service.IService#saveOrUpdate(T, com.baomidou.mybatisplus.core.conditions.Wrapper) 这个方法可能导致死锁【间隙锁死锁】

# 什么是间隙锁

间隙锁是MySQL行锁的一种，与Record lock不同的是间隙锁锁定的是一个间隙。

锁定规则如下： MySQL会向左找第一个比当前索引值小的值，向右找第一个比当前索引值大 的值（没有则为正无穷），将此区间锁住，从而阻止其他事务在此区间插入数据。

# MySQL为什么要引入间隙锁

与Record lock组合成Next-key lock，在可重复读这种隔离级别下一起工作避免幻读。

# 间隙锁死锁分析

理论上一款开源的框架，经过了多年打磨，提供的方法不应该造成如此严重的错误，但理论仅仅是理论上，事实就是发生了死锁，于是我们开始了一轮深度排查。首先我们从这个方法的源码入手，源码如下：

    default boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper) {
        return this.update(entity, updateWrapper) || this.saveOrUpdate(entity);
    }
1
2
3

从源码上看此方法就没有按套路出牌，正常逻辑应该是首先执行查询，存在则修改，不存在则新增，但此方法上来就执行了修改。我们就猜想是不是MySQL在修改时增加了什么锁导致了死锁，于是我们找到了DBA获取了最新的死锁日志，即执行show engine innodb status，我们发现了两项关键信息如下：

*** (1) TRANSACTION:
...省略日志
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 347 n bits 80 index `PRIMARY` of table `database_name`.`table_name` trx id 71C lock_mode X locks gap before rec insert intention waiting
  
*** (2) TRANSACTION:
...省略日志
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 347 n bits 80 index `PRIMARY` of table `database_name`.`table_name` trx id 71D lock_mode X locks gap before rec insert intention waiting
1
2
3
4
5
6
7
8
9

简单翻译一下，就是事务一在获取插入意向锁时，需要等待间隙锁（事务二添加）释放，同时事务二在获取插入意向锁时，也在等待间隙锁释放（事务一添加）， （本文不讨论MySQL在修改与插入时添加的锁，我们把修改时添加间隙锁，插入时获取插入意向锁为已知条件） 那我们回到B方法，并发场景下，是不是就很大几率会满足事务一和事务二相互等待对方持有的间隙锁，从而导致死锁。

现在我们理论有了，我们现在用真实数据来验证此场景。

# 验证间隙锁死锁

准备如下表结构（以下简称验证一）

create table t_gap_lock(
id int auto_increment primary key comment '主键ID',
name varchar(64) not null comment '名称',
age int not null comment '年龄'
) comment '间隙锁测试表';
1
2
3
4
5

准备如下表数据

mysql> select * from t_gap_lock;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  18 |
|  5 | 李四 |  19 |
|  6 | 王五 |  20 |
|  9 | 赵六 |  21 |
| 12 | 孙七 |  22 |
+----+------+-----+
1
2
3
4
5
6
7
8
9
10

我们开启事务一，并执行如下语句，注意这个时候我们还没有提交事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_gap_lock t set t.age = 25 where t.id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
1
2
3
4
5
6

同时我们开启事务二，并执行如下语句，事务二我们同样不提交事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_gap_lock t set t.age = 25 where t.id = 7;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
1
2
3
4
5
6

接下来我们在事务一中执行如下语句

mysql> insert into t_gap_lock(id, name, age) value (7,'间隙锁7',27);
1

我们会发现事务一被阻塞了，然后我们执行以下语句看下当前正在锁的事务。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS \G;
*************************** 1. row ***************************
    lock_id: 749:0:360:3
lock_trx_id: 749
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`t_gap_lock`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 360
   lock_rec: 3
  lock_data: 5
*************************** 2. row ***************************
    lock_id: 74A:0:360:3
lock_trx_id: 74A
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`t_gap_lock`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 360
   lock_rec: 3
  lock_data: 5
2 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

根据lock_type和lock_mode我们可以很清晰的看到锁类型是行锁，锁模式是间隙锁。

与此同时我们在事务二中执行如下语句

insert into t_gap_lock(id, name, age) value (4,'间隙锁4',24);
``

一执行以上语句，数据库就立马报了死锁，并且回滚了事务二（可以在死锁日志中看到`*** WE ROLL BACK TRANSACTION (2)`）

```js
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
1
2
3
4
5
6
7

到这里，细心的同学就会发现，诶，你这上面故意造了一个间隙，并且让两个事务分别在对方的间隙中插入数据，太刻意了，生产环境基本上不会有这种场景，是的，生产环境怎么会有这种场景呢，上面的数据只是为了让大家直观的看到间隙锁的死锁过程，接下来那我们再来一组数据，我们简称验证二。

我们还是以验证一的表结构与数据，我们来执行这样一个操作。首先我们开始开启事务一并且执行如下操作，依然不提交事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_gap_lock t set t.age = 25 where t.id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
1
2
3
4
5
6

同时我们开启事务二，执行与事务一一样的操作，我们会惊奇的发现，竟然也成功了。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_gap_lock t set t.age = 25 where t.id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
1
2
3
4
5
6

于是乎我们在事务一执行如下操作，我们又惊奇的发现事务一被阻塞了。

insert into t_gap_lock(id, name, age) value (4,'间隙锁4',24);
1

在事务一被阻塞的同时，我们在事务二执行同样的语句，我们发现数据库立马就报了死锁。

insert into t_gap_lock(id, name, age) value (4,'间隙锁4',24);    
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
1
2

验证二完整的复现了线上死锁的过程，也就是事务一先执行了更新语句，事务二在同一时刻也执行了更新语句，然后事务一发现没有更新到就去执行主键查询语句，发现确实没有，所以执行了插入语句，但是插入要先获取插入意向锁，在获取插入意向锁的时候发现这个间隙已经被事务二加锁了，所以事务一开始等待事务二释放间隙锁，同理，事务二也执行上述操作，最终导致事务一与事务二互相等待对方释放间隙锁，最终导致死锁。

验证二还说明了一个问题，就是间隙锁加锁是非互斥的，也就是事务一对间隙A加锁后，事务二依然可以给间隙A加锁。

# 如何解决

# 关闭间隙锁（不推荐）

降低隔离级别，例如降为提交读。

直接修改my.cnf，将开关，innodb_locks_unsafe_for_binlog改为1，默认为0即开启

  • PS：以上方法仅适用于当前业务场景确实不关心幻读的问题。

# 自定义saveOrUpdate方法（推荐）

建议自己编写一个saveOrUpdate方法，当然也可以直接采用Mybatis-Plus提供的saveOrUpdate方法，但是根据源码发现，会有很多额外的反射操作，并且还添加了事务，大家都知道，MySQL单表操作完全不需要开事务，会增加额外的开销。

  @Transactional(
        rollbackFor = {Exception.class}
    )
    public boolean saveOrUpdate(T entity) {
        if (null == entity) {
            return false;
        } else {
            Class<?> cls = entity.getClass();
            TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
            Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!", new Object[0]);
            String keyProperty = tableInfo.getKeyProperty();
            Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!", new Object[0]);
            Object idVal = ReflectionKit.getFieldValue(entity, tableInfo.getKeyProperty());
            return !StringUtils.checkValNull(idVal) && !Objects.isNull(this.getById((Serializable)idVal)) ? this.updateById(entity) : this.save(entity);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 拓展

# 如果两个事务修改是存在的行会发生什么

在验证二中两个事务修改的都是不存在的行，都能加间隙锁成功，那如果两个事务修改的是存在的行，MySQL还会加间隙锁吗？或者说把间隙锁从锁间隙降为锁一行？带着疑问，我们执行以下数据验证，我们还是使用验证一的表和数据。

首先我们开启事务一执行以下语句

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_gap_lock t set t.age = 25 where t.id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6

我们再开启事务二，执行同样的语句，发现事务二已经被阻塞

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_gap_lock t set t.age = 25 where t.id = 1;
1
2
3
4

这个时候我们执行以下语句看下当前正在锁的事务。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS \G;
*************************** 1. row ***************************
    lock_id: 75C:0:360:2
lock_trx_id: 75C
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t_gap_lock`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 360
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 75B:0:360:2
lock_trx_id: 75B
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t_gap_lock`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 360
   lock_rec: 2
  lock_data: 1
2 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

根据lock_type和lock_mode我们看到事务一和二加的锁变成了Record Lock，并没有再添加间隙锁，根据以上数据验证MySQL在修改存在的数据时会给行加上Record Lock，与间隙锁不同的是该锁是互斥的，即不同的事务不能同时对同一行记录添加Record Lock。

# 死锁结语

虽然Mybatis-Plus提供的这个方法可能会造成死锁，但是依然不可否认它是一款非常优秀的增强框架，其提供的lambda写法在日常工作中极大的提高了我们的开发效率，所以凡事都用两面性，我们应该秉承辩证的态度，熟悉的方法尝试用，陌生的方法谨慎用。

# 数据库中的递归查询

# oracle中

在Oracle数据库中，递归查询通常使用的是“连接查询”或者“递归子查询分级”的方法来实现。主要用到的SQL语法是CONNECT BY或者使用WITH子句中的递归CTE（公用表表达式）。

# 使用 CONNECT BY 语法

CONNECT BY 语法通常用于层次化数据查询，如公司组织架构、产品分类等。基本的语法结构如下：

SELECT column1, column2, ...
FROM table_name
START WITH condition
CONNECT BY PRIOR parent_column = child_column;
1
2
3
4
  • START WITH：指定递归查询的起点（根节点）。
  • CONNECT BY PRIOR：指定如何从父级节点递归到子级节点。

例如，查询员工的上下级关系：

SELECT employee_id, employee_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
1
2
3
4

# 使用递归CTE (Common Table Expressions)

Oracle 11g Release 2及以后版本支持递归CTE。递归CTE使用WITH语句定义一个递归查询，通常分为两部分：基础部分和递归部分。

基本语法如下：

WITH RECURSIVE cte_name AS (
  -- 基础部分
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  
  UNION ALL
  
  -- 递归部分
  SELECT column1, column2, ...
  FROM table_name
  JOIN cte_name ON condition
)
SELECT * FROM cte_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

例如，查询一个组织结构的层次：

WITH RECURSIVE OrgChart AS (
  -- 基础部分：选择根节点
  SELECT employee_id, employee_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归部分：选择子节点
  SELECT e.employee_id, e.employee_name, e.manager_id, o.level + 1
  FROM employees e
  JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 注意事项

  • 循环引用：递归查询可能导致无限循环，尤其在存在循环数据时，需要加以防范。
  • 性能：递归查询可能对性能有影响，特别是在大数据集上，因此建议在查询前做好数据的索引和优化。