全局锁、表锁、行锁

全局锁、表锁和行锁

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讲

    原文作者:xbhog
    原文地址: https://www.cnblogs.com/xbhog/p/16368318.html
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞