MySQL实战 - 行锁

MySQL实战 - 行锁

目录

• MySQL实战 - 行锁
  • 1 行锁
    • 1.1 行锁的意义
    • 1.2 InnoDB的行锁
      • 1.2.1 两阶段锁
        • 1.2.1.1 两阶段锁协议
        • 1.2.1.2 实际用处
    • 1.3 死锁和死锁检测
      • 1.3.1 死锁
        • 1.3.1.1 解决死锁策略
        • 1.3.1.2 死锁解决策略的问题
        • 1.3.1.3 如何解决热点更新行死锁检测的性能问题
  • 2 问题
    • 2.1 总结问题
    • 2.2 行锁问题

1 行锁

1.1 行锁的意义

• MySQL行锁在引擎层由各个引擎实现
• MyISAM等引擎不支持行锁
• 不支持行锁的引擎只能采用表锁控制并发
• InnoDB支持行锁，取代MyISAM的原因之一

1.2 InnoDB的行锁

基本概念：

• 表行记录的锁
• 事务 A 更新一行，事务 B 也要更新该行，B等待A更新完成

1.2.1 两阶段锁

1.2.1.1 两阶段锁协议

InnoDB事务：

• 行锁在需要时加上
• 事务结束时释放（不是立即释放）

1.2.1.2 实际用处

• 事务需要锁多行，把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

• 减少事务间的锁等待，冲突锁放最后距离提交并结束事务释放锁的时间间隔更短

1.3 死锁和死锁检测

1.3.1 死锁

概念：

• 并发系统中不同线程出现循环资源依赖
• 涉及线程都在等待别的线程释放资源时
• 会导致这几个线程都进入无限等待的状态

示例：

事务A	事务B
begin;update t set ... where id = 1;	begin;
	update t set ... where id = 2;
update t set ... where id = 2;
	update t set ... where id =1;

• 此时事务A等待事务B释放id=2的行锁，事务B等待事务A释放id=1的行锁

1.3.1.1 解决死锁策略

等待超时：

• 设置参数innodb_lock_wait_timeout
• MySQL默认等待时间为50s
• show variables like 'innodb_lock_wait_timeout%' 查看设置

死锁检测：

• 发现死锁主动回滚死锁链条中的某一个事务让其他事务继续执行
• 设置参数innodb_deadlock_detect 为 on
• 默认值为on

1.3.1.2 死锁解决策略的问题

等待超时：

• 默认等待时间为50s，第一个被锁住的线程50s才超时退出，在线服务无法接受这个等待时间
• 如果设置等待时间很短比如1s，可能会误伤普通的锁等待

死锁检测：

• 需考虑该锁等待队列中的线程，单个线程判断时间复杂度为O（n）
• 并发更新同一行的n个线程死锁检测的时间复杂度为O（n^2）
• 耗费大量的cpu资源，高cpu利用率事务执行效率低

1.3.1.3 如何解决热点更新行死锁检测的性能问题

• 如果可以保证业务不会出现死锁，关闭死锁检测，风险是可能出现大量超时导致业务有损

• 控制并发：

  • 客户端控制：因客户端多而不可行，比如600个客户端每个5线程汇总到服务端后并发数也高达3000
  • 数据库服务端并发控制
  • 中间件并发控制
  • MySQL源码修改
  • 相同行更新进入引擎前排队 - proxy可以实现请求排队功能

• 设计上优化：

  • 一行转变为逻辑多行
  • 比如一条账户余额转换为10条账户余额的总和
  • 更新时随机选取
  • 无损但逻辑设计需要设计，比如某条账户余额记录为0时特殊处理

2 问题

2.1 总结问题

1. 两阶段锁的概念是什么? 对事务使用有什么帮助?
2. 死锁的概念是什么? 举例说明出现死锁的情况.
3. 死锁的处理策略有哪两种?
4. 等待超时处理死锁的机制什么?有什么局限?
5. 死锁检测处理死锁的机制是什么? 有什么局限?
6. 有哪些思路可以解决热点更新导致的并发问题?

2.2 行锁问题

update t set t.name='abc' where t.name='cde'name字段无索引

• innodb行级锁是通过锁索引记录实现
• 如update列没索引，只update一条记录也会锁定整张表
• innodb内部是全表根据主键索引逐行扫描
• 逐行加锁
• 事务提交统一释放

当加上limit1之后 更新语句的执行流程是先去查询在去更新,也就是查询sql为 select * from t where name = "abc" limit 1 for update,相当于扫描主键索引找到第一个满足name="abc"的条件为止,此时锁的区间为(负无穷,当前行的id],如果在这个id之后的更新和插入时都不会锁住的,在这个id之前的更新和插入会阻塞,之后则不会阻塞

当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

• 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构
• 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止
• 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成
• 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构