全局锁、表锁和行锁

全局锁

实现:对整个数据库实例进行加锁,使用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;

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

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

server层的锁;

规则:读读共享,读写互斥,写写互斥;

问题:表加字段,导致整个库挂掉

全局锁、表锁、行锁-LMLPHP

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

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

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

实践:

全局锁、表锁、行锁-LMLPHP

全局锁、表锁、行锁-LMLPHP

事务提交以后:

全局锁、表锁、行锁-LMLPHP

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

明确产生的原因:解决长事务,事务不提交,就会一直占着 MDL 锁;

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

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

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

行锁(innodb)

行锁每次锁定的是一行数据,行级锁定不是MySQL自己实现锁定的方式,是由存储引擎实现的(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的默认值来说,时间太长,如果设置一个很小的值,会造成误伤。

推荐使用:主动死锁检测

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

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

问题的症结在于,死锁检测要耗费大量的 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讲

06-12 17:02