又一个mysql面试必问的东西mysql锁

295 阅读7分钟

mysql锁

MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句 (数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。全局锁的典型使用场景是,做全库逻辑备份。

为什么要在备份时加全局锁?

假设我们在备份数据的时候还允许修改数据,假设一个操作两张表a加值表b减值,假设我们在改数据时候b加值的操作已经完成进行备份,然后b还没有减值,此时已经完成a,b两张表的备份操作,后面回复备份时就会出现数据错误。官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务, 来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。既然要全库只读,为什么不使用set global readonly=true的方式呢?

  • 一是,在有些系统中,readonly的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改global变量的 方式影响面更大,我不建议你使用。
  • 二是,在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开,那么MySQL会自动释放这个全局 锁,整个库回到可以正常更新的状态。而将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持 readonly状态,这样会导致整个库⻓时间处于不可写状态,⻛险较高。

表级锁

MySQL里面表级别的锁有两种:

  1. 一种是表锁,表锁的语法是 lock tables ... read/write。与FTWRL类似,可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句,则其他线程写t1、读写t2的语句都会被阻塞。同时,线程A在执行unlock tables之前,也只能执行读t1、读写t2的操作。连写t1都不允许,自然也不能访问其他表。
  2. 一种是元数据锁(meta data lock,MDL)。另一类表级的锁是MDL(metadata lock)。MDL不需要显式使用,在访问一个表的时候会被自动加上。

假设表t是一 个小表。

备注:这里的实验环境是MySQL 5.6。

Session ASession BSession CSession D
begin;
select * from t limit 1;
Select * from t limit 1;
alter table add f int;
(blocked)
select * from t limit 1;
(blocked)
  1. session A先启动,这时候会对表t加一个MDL读锁。
  2. session B需要的也是MDL读锁,因此可以正常执行。
  3. C会被blocked,是因为session A的MDL读锁还没有释放,而session C需要MDL写锁,因此只能被阻塞。
  4. 如果只有session C自己被阻塞还没什么关系,但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了,所有对表的增删改查操作都需要先申请MDL读锁,就都被锁住,等于这个表现在完全不可读写了.
  5. 如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新session再请求的话,这个库的线程很 快就会爆满。

如何安全地给小表加字段?

解决⻓事务,事务不提交,就会一直占着MDL锁。MySQL的information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有⻓事务在执行,要考虑先暂停DDL,或者kill掉这个⻓事务。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?这时候kill可能未必管用,因为新的请求⻢上就来了。比较理想的机制是,在alter table语句里面设定等待时间,如果在这个指 定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。MariaDB已经合并了AliSQL的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n这个语法。

ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ...

mysql行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。MyISAM之所以被InnoDB取代其中一个重要原因就是InnoDB支持行锁。

两阶段锁协议

InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。**如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影 响并发度的锁尽量往后放。**这句话什么意思?举个例子,假设我们有一个事务a一次更新操作,一次新增数据的操作,我们应该先执行插入操作,再执行更新操作。如果我们现在执行更新操作再执行插入操作,此时有另外一个事务b对这条被更新的数据操作要从事务a更新数据时(事务开始等待)到事务结束才能进行;如果先执行插入操作,再执行更新操作,事务b需要从事务a更新数据开始等待到事务a结束操作。也就是先执行插入操作要少等待一段时间。

死锁和死锁检测

事务A事务B
begin;
update t set k=k+1 where id=1
begin;
update t set k=k+1 where id=2
update t set k=k+1 where id=2
update t set k=k+1 where id=1

事务A在等待事务B释放id=2的行锁,而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源 释放,就是进入了死锁状态。当出现死锁以后,有两种策略:

  • 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
  • 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect设置为on,表示开启这个逻辑。

在InnoDB中,innodb_lock_wait_timeout的默认值是50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线 程要过50s才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的。我们又不可能直接把这个时间设置成一个很小的值,比如1s。这样当出现死锁的时候,确实很快就可以解开,但如果不 是死锁,而是简单的锁等待呢?正常情况下我们还是要采用第二种策略,即:主动死锁检测,而且innodb_deadlock_detect的默认值本身就是on。主动 死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是O(n)的操作。假设有1000个 并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁,但是这期间要消耗 大量的CPU资源。如何解决这个问题呢?有个笨方法,就是如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。 另一个思路是控制并发度,如果你有中间件,可以考虑在中间件实现。