SQL四种语言

先介绍下SQL的四种语言

DDL：数据库定义语言定义数据库结构，用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束。DDL不需要commit
- 相关语句：CREATE，ALTER，DROP，TRUNCATE，COMMENT，RENAME
DML：数据操控语言，由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
- 相关语句：SELECT，INSERT，UPDATE，DELETE，MERGE，CALL，EXPLAIN PLAN，LOCK TABLE
DCL：数据库控制语言，用于授权，角色控制等
- 相关语句：GRANT，REVOKE
TCL：事务控制语言
- 相关语句：SAVEPOINT，ROLLBACK，SET TRANSACTION

锁的粒度

全局锁

顾名思义就是对整个数据库实例加锁，MySQL提供了一个全局读锁（命令：Flush tables with read lock）FTWRL。让整个数据库处于只读状态
效果：阻塞数据更新语句（数据的增删改），数据定义语句（创建/修改表结构），更新类事务的提交语句
使用场景：做全库备份（整个库每个表都select出来存成文本）
全库备份：当表的引擎支持一致性读（例如Innodb）就可以开启一个事务，来一致性读，做全表备份
风险：整个库陷入只读

表级锁

MySQL表级锁有两种

表锁（数据库引擎不支持行锁时使用）
元数据锁（MDL）

表锁：lock tables … read/write 释放锁的方法 unlock tables/客户端断开时自动释放
不仅限制其他线程操作，对当前线程的操作也会有影响（例如read也会影响当前线程对表的update）

begin;
lock tables t read;
select * from t where id = 1;
-- 在释放表锁之前，当前线程的更新操作会报错
-- 报 Table 't' was locked with a READ lock and can't be updated
update t set a = 2 where id = 1;
unlock tables;

-- 而另一个线程的更新语句会被阻塞住（不报错）
update t set a = 1 where id = 1;

MDL锁。不需要显示声明，在访问表时会自动加上。作用是保证读写的正确性，不允许有线程访问表的时候，其他线程对表有结构变更
在MySQL5.5中引入，当对一个表进行增删改查操纵时，上MDL读锁，对表结构进行变更的时候，上MDL写锁
读锁不互斥，读写/写写互斥
关于读写锁引发的一个问题（查询语句频繁导致数据库线程被耗尽）

为什么sessionc的写锁在没有获取到锁的时候，也会阻塞sessiond的读锁参考链接
结论：申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。
既然锁表风险那么大，但是不锁表又不能搞定操作期间dml（数据库操作语句）语句的影响，那么有什么办法呢？
online ddl解决更新表期间导致dml阻塞的问题
online ddl （1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ” 上面的例子在第一步就锁住了 )

拿MDL写锁
降级成MDL读锁
真正做DDL
升级成MDL写锁
释放MDL锁
或者ddl时，设置超时时间，当到了指定时间自动放弃，稍后重试
1
2
3
-- MariaDB整合了AliSQL的整个功能
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
行锁（存储引擎实现）
此处讲 Innodb 的行锁
行锁两阶段协议：Innodb事务中，行锁在需要时才加上，等待事务结束时才释放
间隙锁（GapLock 防止幻读）
在可重复读隔离级别下生效

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系
间隙锁+行锁合称为next-key lock 前开后闭的区间

加锁的规则

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间
原则2：查找过程中访问到的对象才会加锁
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

日常使用的注意点

select … lock in share mode; 要考虑到覆盖索引的情况，不回表，就不会对聚簇索引上间隙锁
select … for update; 系统会认为接下来会更新数据，就会顺便把聚簇索引也给锁上

死锁与死锁检测

事务之间互相等待对方持有的锁，导致发生死锁。出现死锁两个解决的策略

直接进入超时等待，直到超时。可以通过参数 innodb_lock_wait_time 来设置（默认50s）
发起死锁检测，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。通过参数 innodb_deadlock_detect 为 on 来表示开启这个逻辑（将持有最少行级排他锁的事务进行回滚。

第一个策略，太长业务不接受，太短容易把正常等待锁的操作误伤。通常还是要使用第二种策略（默认也是开启的）
但是死锁检测需要耗费大量CPU资源。每个并发线程都要判断一下会不会因为自己的加入而导致死锁（现象：CPU利用率高，但是执行不了几个事务）

一些名词

S锁：S 锁，英文为 Shared Lock，中文译作共享锁，有时候我们也称之为读锁，即 Read Lock。
X锁：X 锁，英文为 Exclusive Lock，中文译作排他锁，有时候我们也称之为写锁，即 Write Lock。如同它的名字，X 锁是具有排他性的，即一个写锁会阻塞其他的 X 锁和 S 锁。

-- 排他锁
select ... for update;
-- 共享锁
select ... lock in share mode;

意向锁 I锁：在申请对应行锁之前，需要在表上申请表级意向锁。如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突
行级别的X和S按照普通的共享、排他规则即可。所以之前的示例中第2步不会冲突，只要写操作不是同一行，就不会发生冲突。

实际上表级的意向锁，做的目的是为了让我们的dml语句执行期间，不让ddl语句来影响表的结构，也不让用户手动声明的表级锁产生影响

一些操作

查看当前session持有锁的情况

1 2	-- 8.0.13版本 performance_schema.data_locks select * from data_locks;

执行效果

关于上锁

插入一条记录（多条实际上也是逐条插入操作）

1
2
3

-- 
begin;
insert into test_insert(id,`name`) VALUES (8,'a'),(9,'a');

此时只上了一个表级的意向排他锁

在上面插入记录的前提下，在插入相同id的记录

-- session1 先执行
begin;
insert into test_insert(id,`name`) VALUES (8,'a'),(9,'a');
-- session2 后执行
insert into test_insert(id,`name`) VALUES (8,'a'),(9,'a');

会先给session1对应id=8的记录上排他锁，行锁。然后session2等待锁

查询语句 for update

1	select * from test_insert where id in (1,2,3) for update;

给表上意向排他锁，给行上排他锁与行锁

1	select * from test_insert where id in (1,2,3) lock in share mode;

给表上意向共享锁，给行上共享锁与行锁

删除一个不存在的行

1 2	begin; delete from test_insert where id = 110;

对表上了意向排他锁，然后对数据库最大的记录后上了间隙锁（supremum pseudo-record 相当于比数据库中所有的记录都大）
此时假如又有记录在插入，比最大的id大。则会阻塞

删除一个存在的行

表意向排他锁，行排他锁，行锁

一些锁上遇到的问题

并发insert导致死锁的问题

问题执行的sql：
先执行session1的begin与insert语句（第一环节），此时session2，session3执行插入语句（第二环节），session1回滚（第三环节）

-- session1
begin;
insert into test_insert(id,`name`) VALUES (1,'a');
rollback;

-- session2
insert into test_insert(id,`name`) VALUES (1,'a');

-- session3
insert into test_insert(id,`name`) VALUES (1,'a');

第一环节：对表上意向排他锁

第二环节：
session1对表上意向排他锁（第一环节就已经完成），因为有锁竞争，还会对id=1的记录上排他锁和行锁
session2/session3对表上意向排他锁，对id=1的记录获取共享锁和行锁（获取不到共享锁和行锁，阻塞）

第三环节：
session1回滚，释放排他锁与行锁
session2/session3一个执行成功，另一个报死锁错误
查看死锁日志

1	show engine innodb status;

两个session都持有S锁，都想拿X锁，第一个session想拿X锁，发现第二个session持有S锁，等待…第二个也同理，于是就发生了死锁（升级成排他时发现有其他线程持有共享，等待。然后相互等待导致死锁)
mysql检测死锁，会回滚其中一个事务

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-07-26 15:55:02 140021836547840
*** (1) TRANSACTION:
TRANSACTION 1064326, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 11, OS thread handle 140022177969920, query id 1069 172.18.0.1 root update
insert into test_insert(id,`name`) VALUES (1,'a')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `test`.`test_insert` trx id 1064326 lock mode S locks gap before rec
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000103b36; asc     ;6;;
 2: len 7; hex 82000000870110; asc        ;;
 3: SQL NULL;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `test`.`test_insert` trx id 1064326 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000103b36; asc     ;6;;
 2: len 7; hex 82000000870110; asc        ;;
 3: SQL NULL;


*** (2) TRANSACTION:
TRANSACTION 1064327, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 13, OS thread handle 140022180951808, query id 1073 172.18.0.1 root update
insert into test_insert(id,`name`) VALUES (1,'a')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `test`.`test_insert` trx id 1064327 lock mode S locks gap before rec
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000103b36; asc     ;6;;
 2: len 7; hex 82000000870110; asc        ;;
 3: SQL NULL;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `test`.`test_insert` trx id 1064327 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000103b36; asc     ;6;;
 2: len 7; hex 82000000870110; asc        ;;
 3: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1064328
Purge done for trx's n:o < 1064325 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421497208045512, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208044704, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208040664, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208043896, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208043088, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208042280, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208041472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208039856, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421497208039048, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
998 OS file reads, 1042 OS file writes, 750 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.83 writes/s, 0.77 fsyncs/s