MySQL里面的锁大致可以分成全局锁、 表级锁和行锁三类
1.全局锁
一个库被全局锁上以后， 你要对里面任何一个表做加字段操作， 都是会被锁住的。
对整个数据库实例加锁。 MySQL提供了一个加全局读锁的方法， 命令是Flush tables with read lock (FTWRL)。 当你需要让整个库处于只读状态的时候， 可以使用这个命令， 之后其他线程的以下语句会被阻塞： 数据更新语句（数据的增删改） 、 数据定义语句（包括建表、 修改表结构等） 和更新类事务的提交语句
全局锁的典型使用场景是， 做全库逻辑备份。
2.全局锁 FTWRL 会出现什么问题？
FTWRL确保不会有其他线程对数据库做更新， 然后对整个库做备份，但是让整库都只读， 有风险：
- 如果你在主库上备份， 那么在备份期间都不能执行更新， 业务基本上就得停摆
- 如果你在从库上备份， 那么备份期间从库不能执行主库同步过来的binlog， 会导致主从延迟
全局锁不太好。 但是细想一下， 备份为什么要加锁呢？ 不加锁会有什么问题。
3.不加锁会出现什么问题？
假设备份期间， 有一个用户， 他购买了一门课程， 业务逻辑里就要扣掉他的余额， 然后往已购课程里面加上一门课
- 时间顺序上是先备份账户余额表， 然后用户购买， 然后备份用户课程表会怎么样呢？
结果就是：用户数据A的数据状态是“账户余额没扣，但是课程表里多了一门课，如果用这个备份来恢复数据的话，用户A就赚了。
- 如果备份表的顺序反过来， 先备份用户课程表再备份账户余额表， 又可能会出现什么结果？
结果就是：用户数据A的数据状态是“账户余额扣了，但是课程表里还是空的。
从上面实验结果来看，不加锁备份的话，备份得到的库结果不是一个逻辑时间点，这视图的逻辑不一致的
4怎么解决不加锁备份会出现库数据不一致的问题？
可重复读隔离级别下开启一个事务是可以拿到一致性视图的
官方自带的逻辑备份工具是mysqldump。 当mysqldump使用参数–single-transaction的时候， 导数据之前就会启动一个事务， 来确保拿到一致性视图。 而由于MVCC的支持， 这个过程中数据是可以正常更新的。
single-transaction方法只适用于所有的表使用事务引擎的库
5.既然备份工具是mysqldump可以解决一致性视图问题，为什么还需要 FTWRL ？
使用 逻辑备份工具是mysqldump前提是 引擎要支持这个隔离级别 ， 比如， 对于MyISAM这种不支持事务的引擎， 如果备份过程中有更新， 总是只能取到最新的数据， 那么就破坏了备份的一致性。 这时， 我们就需要使用FTWRL命令了。 single-transaction方法只适用于所有的表使用事务引擎的库
6.还有一种方式支持全库只读 readonly
使用set global readonly=true的方式可以实现全库只读， eadonly方式也可以让全库进入只读状态， 但是会建议你用FTWRL方式， 主要有两个原因 ：
- 在有些系统中， readonly的值会被用来做其他逻辑， 比如用来判断一个库是主库还是备库。 因此， 修改global变量的方式影响面更大， 我不建议你使用
- 在异常处理机制上有差异。 如果执行FTWRL命令之后由于客户端发生异常断开， 那么MySQL会自动释放这个全局锁， 整个库回到可以正常更新的状态。 而将整个库设置为readonly之后， 如果客户端发生异常， 则数据库就会一直保持readonly状态， 这样会导致整个库长时间处于不可写状态， 风险较高
7. 表级锁
MySQL里面表级别的锁有两种： 一种是表锁， 一种是元数据锁（meta data lock， MDL)
表锁的语法是 lock tables …read/write，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放
lock tables语法除了会限制别的线程的读写外， 也限定了本线程接下来的操作对象， 举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句， 则其他线程写t1、 读写t2的语句都会被阻塞。 同时， 线程A在执行unlock tables之前， 也只能执行读t1、 读写t2的操作。 连写t1都不允许， 自然也不能访问其他表。
8. 表级的锁是MDL
MDL不需要显式使用， 在访问一个表的时候会被自动加上。 MDL的作用是， 保证读写的正确性。
事务中的MDL锁， 在语句执行开始时申请， 但是语句结束后并不会马上释放， 而会等到整个事务提交后再释放
MySQL 5.5版本中引入了MDL， 当对一个表做增删改查操作的时候， 加MDL读锁； 当要对表做结构变更操作的时候， 加MDL写锁。
- 读锁之间不互斥， 因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、 写锁之间是互斥的， 用来保证变更表结构操作的安全性。 因此， 如果有两个线程要同时给一个表加字段， 其中一个要等另一个执行完才能开始执行。
9. MDL的坑: 给一个小表加个字段， 导致整个库挂了 ?
给一个表加字段， 或者修改字段， 或者加索引， 需要扫描全表的数据。 在对大表操作的时候， 你肯定会特别小心， 以免对线上服务造成影响。 而实际上， 即使是小表， 操作不慎也会出问题。 我们来看一下下面的操作序列， 假设表t是一个小表。
我们可以看到session A先启动， 这时候会对表t加一个MDL读锁。 由于session B需要的也是MDL读锁， 因此可以正常执行。之后session C会被blocked， 是因为session A的MDL读锁还没有释放， 而session C需要MDL写
锁， 因此只能被阻塞。如果只有session C自己被阻塞还没什么关系， 但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。 所有对表的增删改查操作都需要先申请MDL读锁， 就都被锁住， 等于这个表现在完全不可读写了 . 如果某个表上的查询语句频繁， 而且客户端有重试机制， 也就是说超时后会再起一个新session
再请求的话， 这个库的线程很快就会爆满。
10 如何安全地给小表加字段？
-
解决长事务， 事务不提交， 就会一直占着MDL锁。 在MySQL的information_schema
库的 innodb_trx表中， 你可以查到当前执行中的事务。 如果你要做DDL变更的表刚好有长事务
在执行， 要考虑先暂停DDL， 或者kill掉这个长事务。 -
如果变更的表是一个热点表， 虽然数据量不大，上面的请求很频繁， 而你不得不加个字段， 你该怎么做呢？
kill可能未必管用， 因为新的请求马上就来了。 比较理想的机制是， 在alter table语句里面设定等待时间， 如果在这个指定的等待时间里面能够拿到MDL写锁最好， 拿不到也不要阻塞后面的业务语句， 先放弃。 之后开发人员或者DBA再通过重试命令重复这个过程
11 MySQL的行锁
行锁就是针对数据表中行记录的锁。 这很好理解， 比如事务A更新了一行， 而这时候事务B也要更新同一行， 则必须等事务A的操作完成后才能进行更新。
加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：
12 MySQL的行锁是两阶段锁
在InnoDB事务中， 行锁是在需要的时候才加上的， 但并不是不需要了就立刻释放， 而是要等到事务结束时才释放。 这个就是两阶段锁协议
两阶段锁协议容易出现死锁。例子 ，
- 线程A开启事务,执行UPDATE t set k=k+1 where id=1
- 线程B开启事务,执行UPDATE t set k=k+1 where id=2
- 线程A开启事务,执行UPDATE t set k=k+1 where id=2
- 线程B开启事务,执行UPDATE t set k=k+1 where id=1
- 进入了死锁状态
如果你的事务中需要锁多个行， 要把最可能造成锁冲突、 最可能影响并发度的锁的申请时机尽量往后放
13 死锁和死锁检测
当并发系统中不同线程出现循环资源依赖， 涉及的线程都在等待别的线程释放资源时， 就会导致这几个线程都进入无限等待的状态， 称为死锁 , 调整语句顺序并不能完全避免死锁
14 出现死锁以后， 有两种策略 是怎么解决地？
- 一种策略是， 直接进入等待， 直到超时。 这个超时时间可以通过参数innodb_lock_wait_timeout来设置
- 另一种策略是， 发起死锁检测， 发现死锁后， 主动回滚死锁链条中的某一个事务， 让其他事
务得以继续执行。 将参数innodb_deadlock_detect设置为on， 表示开启这个逻辑。主动死锁检测在发生死锁的时候， 是能够快速发现并进行处理的
innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出， 然后其他线程才有可能继续执行，对于在线服务来说，这个等待时间往往是无法接受的。 直接把这个时间设置成一个很小的值， 比如1s。 这样当出现死锁的时候， 确
实很快就可以解开， 但如果不是死锁， 而是简单的锁等待呢？ 所以， 超时时间设置太短的话， 会
出现很多误伤
正常情况下我们还是要采用第二种策略， 即： 主动死锁检测， 而且innodb_deadlock_detect的默认值本身就是on。 主动死锁检测在发生死锁的时候， 是能够快速发现并进行处理的， 但是它也是有额外负担的
15 热点行更新导致的会导致什么问题？
每个新来的被堵住的线程， 都要判断会不会由于自己的加入导致了死锁， 这是一个时间复杂度是O(n)的操作。 假设有1000个并发线程要同时更新同一行， 那么死锁检测操作就是100万这个量级的。 虽然最终检测的结果是没有死锁， 但是这期间要消耗大量的CPU资源。 因此， 你就会看到CPU利用率很高， 但是每秒却执行不了几个事务。
16 怎么解决由这种热点行更新导致的性能问题呢？
-
就是如果你能确保这个业务一定不会出现死锁， 可以临时把死锁检测关掉
-
控制并发度
-
从设计上优化这个问题
考虑通过将一行改成逻辑上的多行来减少锁冲突。 还是以影院账户为例， 可以考虑放在多条记录上， 比如10个记录， 影院的账户总额等于这10个记录的值的总和。 这样每次要给影院账户加金额的时候， 随机选其中一条记录来加。 这样每次冲突概率变成原来的1/10， 可以减少锁等待个数， 也就减少了死锁检测的CPU消耗
17.MySQL语句加锁分析
假设表数据有ID,NUMBE为(1,1),(5,5),(10,10),(15,15)(20,20),
RC隔离级别下锁行
1.REPEATABLE READ隔离级别下
REPEATABLE READ隔离级别与READ UNCOMMITTED和READ COMMITTED这两个隔离级别相比，最主要的就是要解决幻读问题，幻读问题的解决还得靠我们之前讲过的gap锁
-
对于使用主键进行等值查询的情况
SELECT * FROM t WHERE id = 10 for update;--结果id=10这行数据被锁 --如果我们要查询主键值不存在的记录 SELECT * FROM t WHERE id = 8 for update;--结果(5,10)这个区间数据被锁SELECT * FROM t WHERE id >= 10 for update; --为id值大于10的所有聚簇索引记录都加一个S型next-key锁（包括Supremum伪记录）[10,+正无穷)。 SELECT * FROM t WHERE id <= 10 for update; --为id值小于于10的所有聚簇索引记录都加一个S型next-key锁（包括Supremum伪记录）(-负无穷,15)。 --REPEATABLE READ隔离级别下，在判断id值为15的记录不满足边界条件 id <= 10 后，并不会去释放加在该记录上的锁！！！唯一二级索引具有唯一性
---对唯一二级索引列进行等值查询的记录存在 select * from t where c=10 for update;---先对二级索引记录加X锁，再对聚簇索引加X锁 ---对唯一二级索引列进行等值查询的记录并不存在 select * from t where c=8 for update; -- 这条二级索引记录上加一个gap锁--对普通二级索引等值查询的值存在 select * from t where c=10 for update;---(5,15)被next-lock锁 --对普通二级索引等值查询的值并不存在 select * from t where c=8 for update;---(5,10)被next-lock锁--对普通二级索引进行范围查询 select * from t where c>=10 for update;---(5,,+正无穷)被next-lock锁全部记录都被加了next-key锁
18 间隙锁
产生幻读的原因是， 行锁只能锁住行， 但是新插入记录这个动作， 要更新的是记录之间的“间隙”。 因此， 为了解决幻读问题， InnoDB只好引入新的锁， 也就是间隙锁(GapLock)
即使给所有的行都加上行锁， 仍然无法解决幻读问题， 因此引入了间隙锁的概念。 把隔离级别设置为读提交的话，就没有间隙锁了 ，只有行锁， 间隙锁的引入， 可能会导致同样的语句锁住更大的范围， 这其实是影响了并
发度的。
19 for update锁表还是锁行
如果where条件是有索引的就是行锁+间隙锁， 间隙锁和行锁合称next-keylock
不是索引就是表锁
扫一扫
236
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
