MySQL锁

MySQL锁

分类

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

根据锁粒度不同，MySQL锁大致可分为全局锁、表锁、行锁三类。

1.全局锁

全局锁就是对整个数据库实例加锁。命令：Flush tables with read lock.当需要让整个库处于只读状态时，可以使用此命令，之后其他线程的增删改及定义语句会被阻塞。全局锁的使用场景时全库逻辑备份。全局锁保证了数据的一致性，但是影响业务。innodb可重复读隔离级别开启事务也可以获得一致性试图，官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。但是myisam这种不支持事务的引擎就不能使用了。

set global readonly=true也可以使全库处于只读状态。区别在于 readonly一般被用于判断是主库还是备库。并且在异常机制处理上FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

2.表锁

命令：lock tables t1 read, t2 write;可以用 unlock tables 主动释放锁

显示添加表锁，不仅会限制别的线程读写，当前线程读写也会受到限制。如线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许。

3.MDL元数据锁

MDL（metadata lock)也是表级锁。mdl锁不需要显示使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。

当对一个表做增删改查操作的时候，加 MDL 读锁；

当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。事务不提交，就会一直占着 MDL 锁。

4.online DDL

为了解决ddl阻塞问题，mysql5.6后支持了online ddl.

online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，ddl执行过程中包括三个阶段。

• Prepare阶段：

1. 创建新的临时frm文件

2. 持有EXCLUSIVE-MDL写锁，禁止读写

3. 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)

4. 更新数据字典的内存对象

5. 分配row_log对象记录增量

6. 生成新的临时ibd文件

• ddl执行阶段：

1. 降级EXCLUSIVE-MDL写锁，允许读写

2. 扫描old_table的聚集索引每一条记录rec

3. 遍历新表的聚集索引和二级索引，逐一处理

4. 根据rec构造对应的索引项

5. 将构造索引项插入sort_buffer块

6. 将sort_buffer块插入新的索引

7. 处理ddl执行过程中产生的增量(仅rebuild类型需要)

• commit阶段

1. 升级到EXCLUSIVE-MDL锁，禁止读写

2. 重做最后row_log中最后一部分增量

3. 更新innodb的数据字典表

4. 提交事务(刷事务的redo日志)

5. 修改统计信息

6. rename临时idb文件，frm文件

7. 变更完成

5.行锁

MySQL的行锁是在存储引擎层实现的。并不是所有的存储引擎都支持行锁，MyISAM就不支持行锁。不支持行锁就不得不使用并发度不高的表锁，意味着同一张表同一时刻有且只有1个更新在执行。innodb支持行锁，这也是InnoDB替换MyISAM的原因.

• 两阶段锁协议 ： 在事务中， 行锁是在需要的时候才添加，但并不是不需要了就立刻释放，而是等到事务结束时才释放。所以需要将最可能造成锁冲突的锁尽量放在事务的后边。

• 死锁 ：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就可能会出现无限等待的状态，这种现象称为死锁。解决死锁有两种策略：

  • 直接死等，直至超时。超时时间可以通过innodb_lock_wait_timeout来设置。默认值是50S。

  • 死锁检测。发现死锁后，主动回滚持有行锁较少的事务，让其他事务继续执行。开启方法：innodb_deadlock_detect设置on