mysql 锁

1. 全局锁

1.1 FTWRL

对整个数据库加锁

Flush tables with read lock (FTWRL) //整库只读

数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句都会被阻塞。
全局锁的典型使用场景是，做全库逻辑备份

1.2 single-transaction 一致性读

使用 msqldump 工具并加上–single-transaction 可以开启一个事务,进行逻辑备份,数据也是可以正常更新的

set global readonly=true //全库只读,readonly一般用来判断主库还是备库,遇到异常时FTWRL会释放全局锁,readonly遇到异常会一直导致库不可写,风险较高

2. 表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

2.1 表锁:

lock tables ... read/write
unlock tables

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

2.2 MDL 锁

执行增删改查语句(DML 语句)时自动会加上 MDL 读锁;
执行表结构更改语句(DDL)时自动会加上 MDL 写锁;

session C 在获取 MDL 写锁时会被阻塞,session D 在获取 MDL 读锁时受 session C 的影响也会被阻塞

online DDL

1. 建立一个临时文件, 扫描表 A 主键的所有数据页
2. 用数据页中表 A 的记录生成 B+ 树, 存储到临时文件中
3. 生成临时文件的过程中, 对 A 的操作记录到日志文件中,
4. 临时文件生成后, 将日志文件中的操作应用到临时文件文件, 得到一个逻辑数据上与表 A 相同的数据文件
5. 用临时文件替换表 A 的数据文件

alter 语句在启动的时候事先获取 mdl 写锁, 在拷贝数据的时候就退化成读锁, 读锁不阻塞增删改数据, 但是会阻塞其它线程的 ddl 语句(例如 alter 等), 由于 mdl 写锁的占用时间比较短就被认为是 online

3. 行锁

innoDB 事务中,行锁是在需要的时候才加上的,事务结束时才释放;

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

1. 从顾客 A 账户余额中扣除电影票价；
2. 给影院 B 的账户余额增加这张电影票价；
3. 记录一条交易日志。

锁竞争冲突的最大部分在于 2 步骤, 如果把 2 步骤放到最后一行,则最大限度可以减少事务之间的锁竞争

死锁

事务 AB 互相等待对方释放资源,即进入死锁状态,当出现死锁以后有两种策略

当进入死锁状态时,一般有两种策略:

1. 直接进入等待,直到超时,超时时间可以通过参数 innodb_lock_wait_timeout 来设置,默认 50s
2. 发起死锁检测,发现死锁后主动回滚其中一个事务,innodb_deadlock_detect 设置为 on 表示开启

死锁检测时间复杂度为 O(n²),即 1000 个并发线程更新同一行,死锁检测操作量级为百万级

优化方案为:
1.拆行,一行数据拆多行,控制并发度
2.限流,控制同一时间的线程数
3.关闭死锁检测,会出现大量业务超时

问题

加入要删除 10000 行数据,有三种方式,哪种更好?
1.delete from T limit 10000;
2.在一个连接中循环执行 20 次 delete from T limit 500;
3.在 20 个连接中同时执行 delete from T limit 500;

方法 1: 事务太长,长事务会造成 redolog 过长,响应延时会变大;并且长事务会引起锁竞争,事务阻塞
方法 2: 相对较好,长事务切分为多个短事务,但会引起数据不一致,即在两次 delete 中插入新数据,会被误删,一般如果是自增主键的话,可以加上 order by
方法 3:会产生并发问题,循环等待形成死锁

查看单行数据慢

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

第一类:查询长时间不返回

原因一:等表锁(表锁//MDL 锁)
解决方案:使用 show processlist 查看当前连接状态,不过一般情况下 session A 都处于 sleep 状态,无法判定,这种情况下可以使用 performance_schema 和 sys 库(启动时设置 performance_schema=on,会损失 10% 的性能)

select blocking_pid from sys.schema_table_lock_waits --找到pid ,kill即可

原因二: 等 flush(全局锁)

--mysql中常见的flush,一般情况下很快
flush tables t with read lock;
flush tables with read lock;

当循环调用 sessionA 时,sessionC 会被长时间阻塞

原因三:等行锁

select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'

第二类:查询慢

原因一: 扫描行数多,未使用索引
原因二: 其他线程在循环更新记录,导致 undo log 过长,获取到一开始的视图比较慢

原因三: 后台在刷脏页 如脏页比例过高, redolog 写满,连坐刷脏页太多