四. 数据库的锁

1. 全局锁,表锁,行锁

1.1. 全局锁

全局锁就是对整个数据库实例加锁,MySQL 提供了一个加全局读锁的方法, 命令是 Flush tables with read lock (FTWRL),整个库处于只读状态.

全局锁的典型使用场景是, 做全库逻辑备份。也就是把整库每个表都 select 出来存成文本.

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候, 导数据之前就会启动一个事务, 来确保拿到一致性视图.

**一致性读是好, 但前提是引擎要支持这个隔离级别。**对于 MyISAM 这种不支持事务的引擎, 如果备份过程中有更新, 总是只能取到最新的数据, 那么就破坏了备份的一致性。这时, 我们就需要使用 FTWRL 命令了

--single-transaction 方法只适用于所有的表使用事务引擎的库。

​既然要全库只读, 为什么不使用 set global readonly=true 的方式呢?

• 在有些系统中, readonly 的值会被用来做其他逻辑, 比如用来判断一个库是主库还是备库。因此, 修改 global 变量的方式影响面更大, 我不建议你使用.

• 在异常处理机制上有差异。

  如果执行 FTWRL 命令之后由于客户端发生异常断开, 那么 MySQL 会自动释放这个全局锁, 整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后, 如果客户端发生异常, 则数据库就会一直保持 readonly 状态, 这样会导致整个库长时间处于不可写状态, 风险较高。

1.2. 表级锁

• 表锁: 表锁的语法是 lock tables t read/write。需要注意, lock tables语法除了会限制别的线程的读写外, 也限定了本线程接下来的操作对象.

  在还没有出现更细粒度的锁的时候, 表锁是最常用的处理并发的方式。

  而对于 InnoDB 这种支持行锁的引擎, 一般不使用 lock tables 命令来控制并发, 毕竟锁住整个表的影响面还是太大 如何安全地给小表加字段？

  • MariaDB已支持
  • mysql未知

  ALTER TABLE tbl_name NOWAIT add column ...
  ALTER TABLE tbl_name WAIT N add column ... 
  

• 元数据锁(meta data lock, MDL):

  • MDL是server层的锁, 表级锁, 主要用于隔离DML和DDL操作之间的干扰
  • MDL 的作用是,保证读写的正确性,一个表做增删改查操作的时候,加 MDL 读锁；当要对表做结构变更操作的时候,加 MDL 写锁。
  • 读锁之间不互斥, 因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的, 用来保证变更表结构操作的安全性.
  • MDL 会直到事务提交才释放, 在做表结构变更的时候, 你一定要小心不要导致锁住线上查询和更新。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句, 你需要追查一下, 比较可能的情况是：

• 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎, 那要安排升级换引擎；
• 要么是你的引擎升级了, 但是代码还没升级。我见过这样的情况, 最后业务开发就是把lock tables 和 unlock tables 改成 begin 和 commit, 问题就解决了

1.3. 行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁.比如 MyISAM 引擎就不支持行锁。

• 在 InnoDB 事务中, 行锁是在需要的时候才加上的, 但并不是不需要了就立刻释放, 而是要等到事务结束时才释放。这个就是两阶段锁协议
• 如果你的事务中需要锁多个行, 要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放.

2. 死锁和死锁检测

两种策略：

• 一种策略是, 直接进入等待, 直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout(默认50s) 来设置。
• 另一种策略是, 主动死锁检测, 发现死锁后, 主动回滚死锁链条中的某一个事务, 让其他事务得以继续执行。将参数 innodb_deadlock_detect(默认on) 设置为 on, 表示开启这个逻辑。
  • 业务避免死锁
  • 控制并发度,中间件或修改mysql源码来进行控制并发. 基本思路就是, 对于相同行的更新, 在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了