主页 服务器运维
快捷导航
您的位置：主页 > 数据库 > Mysql

全局锁、表锁、行锁

https://www.518cn.com   发布时间：2025-03-18 22:39   作者：网络
摘要：mysql 锁 全局锁 全局锁就是对整个数据库实例加锁。全局锁的典型使用场景是，做全库逻辑备份。 -- 加全局锁 命令：Flush tables with read lock (FTWRL) -- 释放全局锁 命令：unlock tables -- 设置全

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写锁。

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

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

解决方案：

  1. 在修改表结构前，先确认是否有长事务在执行
  2. 设置DDL操作的超时时间，超时后放弃：
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;
  1. 采用pt-osc或gh-ost这类工具来做在线表结构变更
使用 single-transaction 替代全局锁

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

mysqldump --single-transaction -uroot -p database_name > backup.sql
single-transaction备份在主从架构中的问题

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

  1. 备份过程中如果主库执行了DDL：

    • 如果从库还没有复制到这个DDL时，备份是正常的
    • 如果从库已经开始执行这个DDL：
      • 备份会报错中断
      • 因为DDL会导致MDL写锁，与备份事务的MDL读锁冲突

  2. 解决方案：

    • 在备份时，最好确保主库不会执行DDL操作
    • 或者使用其他备份方案，如：
      • 使用mysqldump的--lock-all-tables参数
      • 使用逻辑备份工具Percona XtraBackup
      • 使用全局锁FTWRL
 

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

行锁

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

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

两阶段锁协议：
1. 加锁阶段：事务可以获得除InnoDB外其他引擎支持的锁，InnoDB只支持行锁。
2. 解锁阶段：事务结束后，所有加的锁才会被释放。
死锁处理策略：
1. 等待，直到超时。
2. 发起死锁检测，主动回滚死锁链条中的某一个事务，让其他事务继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

相关文章

最新评论