数据库极客时间-mysql锁与事务
Source
Edit
History

锁

Mysql 锁分为 全局锁、表级锁、行锁

一、全局锁

对整个数据库实例加锁，使处于只读状态。阻塞语句包括：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构）和更新类型的事务的提交语句。

1
2
3
4
# 加锁(FTWRL)
flush tables with read lock
# 解锁
unlock tables;

场景：做全库逻辑备份，对于全部是 InnoDB引擎的库，可以选择使用 –single-transaction 参数（mysqldump时导数据前会启动一个事务，来确保拿到一致性视图。只适用于所有的表使用事务引擎的库）

风险点：

  1. 如果在主库上备份，那么在备份期间都不能执行更新，业务上基本上就得停摆
  2. 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

其他：set global readonly = true 也可以让全库进入只读状态，但不建议使用，因为：

  1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global 变量的方式影响面更大，不建议
  2. 在异常处理机制上有差异。如果执行FTWRL 命令之后由于客户端发生异常断开，那么mysql 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly 之后，如果客户端发生异常，则数据库就会一直保持readonly 状态，这将导致整个库长时间处于不可写状态，风险较高

二、表级锁

表级锁：1. 表锁 2. 元数据锁（meta data lock，MDL）

1. 表锁

会锁住整张表，而且客户端断开的时候自动释放。

1
2
3
4
# 加锁
lock table 表名 read/write
# 解锁
unlock tables

2. 元数据锁-MDL（metadata lock）

在Mysql 5.5 版本中引入 MDL，不需要显式使用，在访问一个表时会自动加上。主要是规避在访问一张表时，另一个线程对这个表结构做变更。当对一张表做增删改查操作时，加 MDL 读锁；当对表结构做变更时，加 MDL 写锁。

读锁之间不互斥；读写锁之间、写锁之间是互斥的。注意：事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会整个事务提交后才会释放。

1
2
3
4
5
6
7
修改表结构时的问题：
1. 比如修改表结构，需要加写锁，而如果这个表有读锁，则写就被阻塞。后面来的读要加读锁也会阻塞。等于这个表无法读写了。客户端如果查询语句频繁或有重试机制的话，这个库的线程很快就会爆满。

如何解决（安全的给小表加字段）：
1. 首先要解决长事务，事务部提交，就会一直占着 MDL 锁。在 mysql 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做字段变更的表刚好有长事务在执行，要考虑暂停字段变更，或者先停止（kill）这个长事务。
2. 如果对这个表的请求很频繁，并且必须做字段变更，此时 kill 事务的方式未必管用，因为新的请求马上就来了。比较理想的方式是在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，可以尝试重复执行。
因此，MDL 就直到事务提交才释放，在做表结构变更时，一定要小心，不要锁住线上的查询和更新。

三、行锁

行锁时引擎自己实现，MyISAM 引擎不支持行锁，InnoDB支持行锁。会自动加上行锁。

两阶段锁协议定义：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束（事务提交了或者事务回滚了）时才释放。

行锁主要是为了保证事务的隔离性，即多个事务在并发的情况下等同于串行的执行。

因此，如果某个事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

四、死锁和死锁检测

当并发系统中不同线程出现资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁后，有两种策略：

  1. 直接进入等待，直到超时，超时时间的参数 innodb_lock_wait_timeout ，默认为50s。意味着第一个被锁住的线程要过 50s 才会超时退出，其他线程才有可能继续执行。对于在线服务来说，这个等待时间无法接受。同时，这个超时时间如果设置的太短，比如 1s，那么当死锁的时候，确实可以很快解开，但如果不是死锁，而是简单的锁等待，就会出现误伤。所以，正常情况下我们还是要采用第二种策略。

  2. 主动死锁检测。即发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on，表示开启这个逻辑。注意：是有额外负担的，每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

    每个新来的被阻塞的线程，都要判断会不会由于自己的加入导致了死锁，时间复杂度为 O(n)。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级。这期间要消耗大量的CPU资源。因此，有时候会看到CPU利用率很高，但每秒却执行不了几个事务。

如何优化死锁检测耗费的大量 CPU 资源：

  1. 如果可以确保业务一定不会出现死锁，可以临时关闭死锁检测。但是这种操作本身带有一定风险，关掉死锁检测意味着可能会出现大量的超时。
  2. 控制并发度，比如同一行同时最多只有10个线程在更新，死锁检测的成本就很低。一种直接的思路是，在客户端做并发控制。但是这种方式受限于客户端的数量，如果客户端数量很多，即使每个客户端只有几个并发线程，汇总到数据库服务端后，峰值并发数也很大。
  3. 将一行改成逻辑上的多行来减少锁冲突，也就是降低锁的粒度。