Mysql 锁的介绍和使用

参考

基本概念

并发

网络编程中,多个请求/查询(进程/线程) 修改同一数据时,就会产并发控制问题,即谁先修改谁后修改.锁机制就是用来解决和控制并发问题的.

Mysql根据加锁的范围(粒度)分为三种锁

全局锁

给整个数据库加锁

FLUSH TABLES WITH READ LOCK
//全局读锁,会使其它线程中的写操作 数据更新语句(删除更新) 数据定义语句(创建修改表) 和更新事物提交类语句被阻塞
//使用场景全库逻辑备份,存在无事物的存储引擎的数据库
mysqldump –single-transaction 适合全库innodb引擎(所有表都支持事物)备份

表级锁

一种是表锁,一种是元数据锁(meta data lock,MDL)

表锁 lock tables tablename… read/write 释放所unlock tables

表锁不仅仅会锁定其它线程对锁定表的操作,也会锁定当前执行lock 语句的线程接下里的操作对象.只能执行当前表的操作

上图示例当前线程执行了所锁定users表,则无法查询或操作其它表,

此时其它线程中可以正常读取users表的数据,因为读锁不互斥.

上三图表示线程A锁定users表示,其它线程对users表的操作,在未释放表锁前,另一线程一直处于阻塞状态,直到释放表锁

另一类表级的锁是MDL(meta data lock)

不需要显示调用,数据库自动操作 Mysql5.5版本中开始引入.作用是保证读写的正确性.例如,一个线程进行遍历查询,另一个线程进行表结构变更.

对一个表进行增删改查操作时,加MDL读锁,对表执行结构变更时,加MDL写锁.

读锁之间不互斥,因此多个线程可以对同一张表做增删改查,

读写锁之间,读锁之间互斥,用户保证表结构变更操作的安全性.例如线程A进行表遍历查询,线程B进行表结构变更操作,线程B需要等线程A的读锁释放之后才能执行

MDL测试案例

此时,sessionB会阻塞,因为sessionA中的MDL读锁还没有被释放

sessionC所有查询被被阻塞,此时整个数据库不能正常查询,这就是生产环境数据库结构变更可能会踩的坑.

解决方案

1.解除长事务,变更前查询一下存在事物正在运行的事物(information_schema 库的 innodb_trx表),手动kill调.

2.alter table语句设定等待时间. MariaDB和AliSQL支持 DDL NOWWAIT?WAIT n 语法

ALTER TABLE tbl_name NOWAIT add column...

ALTER TABLE tbl_name WAIT n add column...

MDL总结

mysql 5.6之前的版本,执行alter语句时间会很长,通过建立新的临时表方案解决,导入数据,最后重命名.

mysql5.6引入online ddl,通常情况下不会锁库,但是存在长事务和慢查询时,也会出现

行锁

行锁是由存储引擎层实现的,是mysql最小锁定粒度,可以锁定一行数据.常用引擎Innodb支持行锁MyISAM不支持行锁

Innodb行锁介绍

两阶段加锁,,