MySQL第5讲锁机制全局锁、表锁、行锁详解MySQL锁详解

63 阅读 0 评论 42 点赞

我是靠谱客的博主彪壮宝贝，最近开发中收集的这篇文章主要介绍MySQL第5讲锁机制全局锁、表锁、行锁详解MySQL锁详解，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

MySQL锁详解

1. 全局锁

2. 表级锁

1）表锁

2）元数据锁（meta data lock， MDL）

3. 行锁

1）在InnoDB事务中什么时候加行锁，什么时候释放锁？——两阶段锁

2）怎么减少行锁对性能的影响

1. 全局锁

全局锁是对整个数据库实例加锁，目的是为了保证读取时的数据的一致性。加全局锁命令：Flush tables with read lock (FTWRL)。加全局锁后，整个库处于只读状态，其它线程的任何更新操作包括更改表结构的操作都会被阻塞。典型使用场景是全库的逻辑备份，备份某一个时刻的数据库，需要保证在备份完成之前数据库的任何表没有新的变化，so加了全局锁。但是在备份过程中整个数据库处于只读状态，无论是在主库还是从库上备份都会有很大影响。

所以有没有其它的办法来保证备份一致性呢？有，事务的隔离级别有一个叫可重复读，可重复读指的就是在一个事务里面先后读取到同一条数据是相同的。这不就是数据的一致性吗，我们需要一个这样的备份在一段时间内保持不变（备份完成这段时间），那就不需要给整个库加全局锁了，这样不就高效很多吗。所以如果使用的是支持事务的引擎，那都是可以用可重复读隔离级别下的这个功能。在备份过程中，读和写之间依然不冲突，数据可以正常更新。

还有一种不建议的方式，就是设置库只读set global readonly=true，但是如果设置了readonly，在连接期间

2. 表级锁

表级别的锁粒度还是比较大，两种表级锁：表锁和元数据锁

1）表锁

命令：lock tables … read/write，释放锁命令：unlock tables，客户端断开连接时也会自动释放。

lock read 锁定表只读，当前线程只读不能写，其它线程也是。

lock write 锁定表写并且读，当前线程可以读写，其它线程不能读写。

2）元数据锁（meta data lock， MDL）

MDL是指的对表结构进行变更，MDL是为了在一个表在做增删改查的时候，防止因为表结构的改变导致查询结果异常。在MySQL5.5中引入了MDL，当对一个表做增删改查操作时，加MDL读锁；当改变表结构时，加MDL写锁。使用时不需要显式指定，在对应的访问时会自动加上。读读锁之间不互斥，因为支持多个线程对一个表进行增删改查操作。读写锁之间以及写写锁之间互斥，有线程在做增删改查时，变更表结构的操作应该阻塞等待。

在这种机制下，会不会存在一些问题？虽然MDL系统会默认加锁，但是不免还是会在使用的时候出现一些问题。例：线程A启动后，开启了一个事务，在事务中访问表T，给表T加了MDL读锁，此时线程B启动后需要更改表结构，给表T加写锁，但是由于线程A的读锁还未释放（事务提交后才会释放MDL锁），所以线程B会阻塞，而申请MDL锁的操作形成的等待队列中，写锁的优先级高于读锁，所以只要有写锁等待，那么后续所有的MDL加锁操作都会被阻塞（详情参考mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象_花落的速度的博客-CSDN博客_mdl读写），也就是说现在这个表已经不能读写了。

怎么解决这个问题呢？第一，可以考虑在申请MDL写锁之前关闭长事务，因为事务不提交就会一直占着锁，显然长事务的影响挺大的。第二，也就是惯用的超时机制，在更改表结构的语句例设定等待时间，如果超过这个时间还未拿到锁，那就先放弃更改。

如果在支持InnoDB的引擎的库中做全局备份，可以使用利用事务的可重复读来做数据一致性视图。目前MySQL的默认存储引擎是InnoDB，在InnoDB这类支持行锁的引擎中，表锁是很少用到的。

3. 行锁

无论是全局锁还是表锁，锁的粒度都太大，如果给整个表加锁，意味着在同一张上表上的任意时刻都只有一个更新在执行，这无疑会影响业务并发度。所以在InnoDB引擎中，引入了行锁，而MyISM是不支持行锁，也难怪MyISM被InnoDB替代了。

行锁也就是对表中的记录行加锁，当然，行锁也意味着同一行记录在任意时刻只能有一个线程更新。

1）在InnoDB事务中什么时候加行锁，什么时候释放锁？——两阶段锁

我们知道InnoDB默认的隔离级别是可重复读，在可重复读的隔离级别下，行锁是在需要更新数据的时候加上，提交事务之后释放行锁。

为什么不更新完立即释放呢？这是为了保证可重复读，简单来说就是在一个事务中需要保证两次读取到的数据是一致的，如果更新完了数据后马上释放行锁，这会导致其它事务读到未提交的数据，因为更新语句都是先读后写，这个读指的是当前读，读到最新的（已提交）数据。

2）怎么减少行锁对性能的影响

减少事务之间锁等待的时间

在前面我们已经知道行锁在需要的时候添加，在事务提交的时候释放，设想一下，如果事务一开始就是一条更新语句，那这个行锁会一直持续到这个事务提交。所以第一个原则就是当一个业务中存在两个事务修改同一行数据的情景时，将这个修改操作尽量安排在最后，那么这个行锁加锁的时间也就最少，这在最大程度上减少了事务之间锁的等待。

所以事务不要太长也是这个原因，事务里面加的行锁会一直持续到事务提交

减少死锁检测的CPU消耗

使用上述策略可以尽量避免事务在一行数据上停留太久，但是这也有一个问题，如果发生了死锁，直接在行上死等，那就需要采用其它的策略了。比如以下两种：
- 进入死锁后自动一直等待，直到超时释放（设置参数innodb_lock_wait_timeout）
- 检测到死锁后，让其中一个事务回滚，其它事务可以继续执行（将参数innodb_deadlock_detect设置为on，开启死锁检测）
如果采用锁超时的方法往往是不太现实的，如果超时时间设置太长，那锁太久直接挂了，如果超时时间太短，那事务有可能因为其它原因处于正常等锁的时间，系统并未出现死锁，由于超时时间太短而错误回滚。

所以采用第二种死锁检测的方法，主动检测死锁，发现死锁的时候进行处理，但是这又会造成额外的开销。每个事务出现等锁的时候都需要判断是不是导致死锁了，这个判断的时间复杂度是O(n)的，这会消耗大量的CPU资源来检测死锁，虽然最后可能并没有发生死锁。比如有1000个线程同时并发修改同一行，都在等锁，那么锁检测操作就是1000*1000=100万，这浪费了太多CPU资源。

怎么解决这个问题呢？其实这涉及到数据库并发度的问题，只要一个时间没有那么多请求行的更新落到数据库上，自然不会同一时间有这么多死锁检测。所以可以采用控制数据库服务端的并发度，采用的策略基本原则就是，让这些请求在进入InnoDB引擎之前先排队，那么在引擎内部就不会有大量的死锁检测了。比如采用中间件缓存，限流等。

其实还可以使用类似于CuncurrentHashMap的锁方法，将一个记录改为多个记录。比如需要增加某一个商品的库存，可以将这个库存的值放在10条记录上，这样每次需要增加库存的时候可以任选一条记录来更新，这样库存的值相当于10条记录的总和，提高了并发度，也减少了死锁发生的概率。

但是这又会引起其它的一些数据一致性问题，以及多条记录之间维护起来更加复杂，还得考虑库存减到0时的特殊处理。