mysql--锁

1. 全局锁

对整个数据库加锁

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

数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句都会被阻塞。
全局锁的典型使用场景是，做全库逻辑备份
使用msqldump工具并加上–single-transaction可以开启一个事务,进行逻辑备份,数据也是可以正常更新的

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

2. 表级锁

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

表锁:

lock tables ... read/write
unlock tables

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

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

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

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过长,获取到一开始的视图比较慢