全局锁、表锁、行锁_数据库

全局锁、表锁、行锁

MySQL 锁

全局锁

全局锁就是对整个数据库实例加锁。全局锁的典型使用场景是，做全库逻辑备份。

-- 加全局锁 命令：Flush tables with read lock (FTWRL) -- 释放全局锁 命令：unlock tables -- 设置全局只读 set global readonly=true -- 设置全局读写 set global readonly=false

设置全库只读和加全局锁的区别

1. 全局锁在执行过程中由于客户端异常断开，会自动释放锁，而设置全库只读不会。

表锁

表锁的典型使用场景是，做读写分离，使读写分离失效。

-- 加表锁 命令：lock tables read/write table_name; -- 释放表锁 命令：unlock tables;

元数据锁（MDL锁）

当对一个表进行增删改查操作时，加MDL读锁；当要对表结构进行变更操作时，加MDL写锁。

给一个小表加字段导致整个库挂掉的典型场景：

线程A正在对表进行一个长时间查询，持有MDL读锁
线程B要给该表加一个字段，需要获取MDL写锁，因此被阻塞
后续所有要访问该表的线程都要先获取MDL锁，全部被阻塞
如果该表是一个热点表，大量线程被阻塞，导致数据库连接池被占满
最终整个数据库不可用

解决方案：

在修改表结构前，先确认是否有长事务在执行
设置DDL操作的超时时间，超时后放弃：

ALTER TABLE table_name ADD COLUMN column_name VARCHAR(50)， ALGoRITHM=INPLACE， LOCK=NONE;

采用pt-osC++或gh-ost这类工具来做在线表结构变更

使用 single-transaction 替代全局锁

对于支持事务的存储引擎（如InnoDB），可以使用 single-transaction 参数来做备份，避免使用全局锁：

mysqldump --single-transaction -uroot -p database_name > backup.sql

single-transaction备份在主从架构中的问题

在主从架构中使用single-transaction做备份可能会遇到的问题：

备份过程中如果主库执行了DDL：
- 如果从库还没有复制到这个DDL时，备份是正常的
- 如果从库已经开始执行这个DDL：
  - 备份会报错中断
  - 因为DDL会导致MDL写锁，与备份事务的MDL读锁冲突
解决方案：
- 在备份时，最好确保主库不会执行DDL操作
- 或者使用其他备份方案，如：
  - 使用MySQLdump的--lock-all-tables参数
  - 使用逻辑备份工具Percona XtraBackup
  - 使用全局锁FTWRL

全局锁主要用在逻辑备份过程中。对于全部是InnoDB引擎的库，我建议你选择使用–single-transaction参数，对应用会更友好。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

行锁

是通过锁索引记录实现的。

在innodb中，行锁是在需要的时候加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

两阶段锁协议：
1. 加锁阶段：事务可以获得除InnoDB外其他引擎支持的锁，InnoDB只支持行锁。
2. 解锁阶段：事务结束后，所有加的锁才会被释放。

死锁处理策略：

1. 等待，直到超时。
2. 发起死锁检测，主动回滚死锁链条中的某一个事务，让其他事务继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。