MySQL| 全局锁和表锁、行锁

MySQL| 全局锁和表锁、行锁

• 根据加锁的范围，MySQL里面的锁大致可以分成全局锁，表级锁，行锁。

全局锁

• 对整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock。当需要整个库处于只读状态时可以使用。
• 数据更新语句、数据定义语句和更新类事务会被阻塞。

---

• 使用场景：做全库逻辑备份。就是把整个表的select 出来存成文本。
  • 如果再主库上做备份，那么再备份期间都不能执行更新，业务基本停摆。
  • 如果再从库上做备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
• 不加锁，备份系统备份得到的库不是一个逻辑时间点。导致逻辑不一致。

表级锁

• MySQL里面表级锁有两种：表锁、元数据锁（MDL）。
  • 表锁的语法时lock tables… read/write。与FTWRL类似。可以用unlock tables主动释放锁。
    • 如果某个线程A中执行lock tables t1 read , t2 write;这个语句，则其他线程写t1,读写t2的语句都会被阻塞，同时，线程A再执行unlock tables之前，也只能执行读t1、读写t2的操作。写t1都不被允许，也不能访问其他表。
  • MDL不需要显示使用，再访问一个表的时候会被自动加上。
    • MDL的作用时，保证读写的正确性。再MySQL5.5版本中引入MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作加MDL写锁。
    • 读锁不互斥，因此可以有多个线程对同一张表增删改查。
    • 读写锁之间、写锁之间时互斥的，用来保证变更表结构操作的安全。
    • 如果有两个线程同时给一个表加字段，其中一个要等另一个执行完毕。

---

• 如何安全给小表加字段
  1. 解决长事务，事务不提交，就会一直占MDL锁，考虑DDL或者kill掉长事务。
  2. alter table语句里面设定等待时间，在指定时间没有拿到则放弃，直到之后重试。

行锁

• MySQL的行锁在引擎层由各个引擎独立实现。
• MyISAM引擎不支持行锁。使用并发控制只能使用表锁。

两阶段锁

事务A	事务B
begin
update t set k = k + 1 where id = 1;
update t set k = k + 1 where id = 2;
	begin
	update t set k = k + 2 where id = 1;
commit

• 上面事务A在update过程中，持有两个记录的行锁，都是在commit的时候释放,而事务B的update会被阻塞，直到事务A在commit后才继续执行。
• InnoDB事务中，行锁是在需要的时候才加上，但不是不需要了就立刻释放，而是到事务结束时才释放，即两阶段锁协议。
• 在事务中需要多个锁，尽量将可能造成所冲突和影响并发度的锁后延。

---

思考：

• 假设：在线一个在线交易业务，顾客A需要到影院B购买电影票。
  1. 扣除A中票价
  2. 添加B中票价
  3. 记录交易日志
• 即需要两个update，一个insert。这时候需要开启事务保持交易原子性。
• 但如果有大量的顾客要在B买票，那么事务将在语句2起冲突。导致阻塞。
• 这时候最好将2安排在最后，即3、1、2的更新顺序，减少锁等待，提高并发。

死锁和死锁检测

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

• 事务A在等待事务B释放ID = 2的行锁，事务B在等待事务A释放ID=1的行锁。造成死锁。

解决死锁策略：

• 直接进入等待，直到超时。超时时间可以通过参数innodb_lock_wait_timeout来设置。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，其他事务继续执行，将innodb_deadlock_detect设置为on。

两种策略的劣势：

• InnoDB默认超时是50s，第一个被锁住的线程超过50S才会退出，其他线程才可继续执行。如果设置超时为1s,那么正常等待的线程就直接退出。
• 死锁检测本身也需要资源消耗，如果检测量太大，那么就要小号大量CPU资源。

---

怎么解决由热点行更新导致的性能问题。

• 如果能确保业务一定不会发生死锁，可以临时关掉死锁检测。
• 控制并发度，运用中间件，对于同行的更新，在进入引擎之前排队。

小结

• 全局锁主要用作逻辑备份。对于全是InnoDB引擎的库，最好使用single-transaction参数。、
• 表锁一般在数据库引擎不支持行锁时使用。如果发现lock tables语句。
  • 检查是否在使用MyISAM这类引擎，进行升级。
  • 引擎升级了，代码没有升级，那就要把lock tables和unlock table改成begin 和 commit。
• 如果事务中需要锁多行，把最可能造成锁冲突、影响并发度的锁往后延。
• 减少死锁对数据库的影响，主要方向就是控制访问相同资源的并发事务量。