? 优质资源分享 ?

全局锁、表锁和行锁

MySQL45讲基础篇：根据加锁的范围，MySQL里面的锁大致分为全局锁、表锁、行锁三类

全局锁

实现：对整个数据库实例进行加锁，使用FTWRL.

Flush table with read lock

效果：整个库处于只读状态，DML和DDL以及更新事务的提交语句都会被阻塞。

全局锁使用场景：做全库逻辑备份(binlog)--也就是把整库每个表都 select 出来存成文本

做全库逻辑备份：

1. 加全局锁：整个库处于阻塞状态，无法更新，这对线上是不可能采用的
2. 不加全局锁：当我在备份的时候，由数据更新，造成，备份库和本地库不匹配，没有意义

准确点说：

不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的

由此，可以引出前面所说的事务隔离中的可重复读（视图中的数据前后一致）：

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是 一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

MySQL多版本并发控制(MVCC):同一条记录在系统中可以存在多个版本，不同时刻启动的事务会有不同的read-view(值)。

具体的实现是：

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction(所有的表使用事务引擎的库(InnoDB )) 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

FTWRL与set global readonly=true对比：

1. FTWRL：适用于不支持事务的引擎；并且使用后如果客户端发生异常连接断开，那么MySQL会自动释放全局锁。

为了使全库已读，也不推荐：set global readonly=true

• 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
• 二是，将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁(表锁)

表锁语法：lock tables ...read/write

释放锁：unlock tables 或者在客户端断开的时候自动释放

缺点：除了限制别的线程的读写外，也限定了本线程接下来的操作对象

线程A：

lock tables t1 read, t2 write; 

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

尽量不要使用全局表锁，对于有innodb引擎的数据库来说，推荐使用：single-transaction

MDL(metadata lock): 不显示使用，在访问一个表的时候会被自动加上。

server层的锁；

规则：读读共享，读写互斥，写写互斥；

问题：表加字段，导致整个库挂掉

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据

对表进行增删改查(隐式提交)的时候都会自动加上MDL；

显示使用事务：begin---commit;

1. sessionA 加读锁--未释放
2. sessionB 加读锁，读读不互斥，可以使用
3. sessionC 修改表（加字段--写锁），前面读锁未释放，所以等待
4. 后续对于t表的操作都会阻塞

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满；

实践：

事务提交以后：

由上引出如何安全给表加字段：

明确产生的原因：解决长事务，事务不提交，就会一直占着 MDL 锁；

如果表的实时性不是很重要，可以考虑暂停DDL的变更或者kill长事务；

表(热点表--请求频繁)的实时性很高的话(数据都是热点数据)：

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程.

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

行锁(innodb)

行锁每次锁定的是一行数据，行级锁定不是MySQL自己实现锁定的方式，是由存储引擎实现的(InnoDB)自己实现的。

两段锁：

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

通过给索引上的索引项加锁来实现的，也就意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。这一点在实际应用中特别需要注意，不然的话可能导致大量的锁冲突，从而影响引发并发性能

--共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有
select ... lock in share mode;

--排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。
select ... for update

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

减少冲突造成的阻塞时间过长。

死锁和死锁检测：

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

解决策略：

1. 直接进入等待，直到超时，超时时间参数：innodb_lock_wait_timeout(默认值50s)
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

对于innodb_lock_wait_timeout的默认值来说，时间太长，如果设置一个很小的值，会造成误伤。

推荐使用：主动死锁检测

检测对系统来说还是有额外的负担；这里有一个边界情况：所有事务都要更新同一行的场景

假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务

怎么解决由这种热点行更新导致的性能问题呢？

问题的症结在于，死锁检测要耗费大量的 CPU 资源。

1. 临时关闭死锁检测，但并不可靠
2. 控制并发度：控制同一行最大线程操作数

问题：如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

• 第一种，直接执行 delete from T limit 10000;(X)
• 第二种，在一个连接中循环执行 20 次 delete from T limit 500;(√)
• 第三种，在 20 个连接中同时执行 delete from T limit 500。(X)

你会选择哪一种方法呢？为什么呢？

1. 长事务，占用的时间比较长，造成等待时间较长，应该避免;
2. 将一个长事务，分为20个短事务，每次事务占用锁的时间相对较短；
3. 造成锁冲突，当第一个连接中的事务没有提交，那么会阻塞剩余线程。

部分图片引入来源：MySQL实战45讲

• 全局锁、表锁和行锁

  • 全局锁
  • 表级锁(表锁)
  • 行锁(innodb)
  • 两段锁：
  • 死锁和死锁检测：
  • 怎么解决由这种热点行更新导致的性能问题呢？

  __EOF__

  xbhog - 本文链接： https://blog.csdn.net/xbhog/p/16368318.html

  • 关于博主： 评论和私信会在第一时间回复。或者直接私信我。
  • 版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
  • 声援博主： 如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。

转载请注明：xuhss » 全局锁、表锁、行锁