MySQL里面的锁可以分为：全局锁、表级锁、行级锁

全局锁

  • 对整个数据库实例加锁。
  • 使用场景：做全库逻辑备份时，为了保证备份期间的库在同一个逻辑时间点，即一致性视图（类似于可重复读隔离级别的效果）

全局锁两种方式：

  • Flush tables with read lock(FTWRL) 使数据库处于只读状态，数据的增删改、数据定义语句和更新类事务的提交语句都会被阻塞
  • mysqldump 官方自带的逻辑备份工具，参数 -single-transaction 会在导数据之前启动一个事务，确保拿到一致性视图

以上两种方式不同点：

使用 mysqldump 前提是 引擎支持隔离级别 ，所以single-transaction方法只适用于支持事务引擎的库；MyISAM不支持事务，所以只能使用FTWRL命令

1
set global readonly = true

这条语句也可以做到全库只读，但是不建议使用：

  • 有时 readonly 会用来判断库是主库还是备库，因此修改global变量的方式影响会比较大
  • 异常处理方面：FTWRL后客户端异常断开，MySQL会自动释放全局锁，库恢复正常；设置readonly，客户端异常，则会保持readonly，会导致长时间处于不可写状态，风险较高

以上哪种方式，一个库被全局上锁后，对立面任何一个表做字段操作，都会被锁住的

即使没有全局加锁，有了表级锁，加字段也会遇到问题

表级锁

MySQL有两种表级别的锁：一种是表锁、另一种是元数据锁( metadata lock，MDL )

表级锁的语法：

lock tables xxx read/write

例如 线程A执行了lock table t1 read,t2 write 效果是 包括A线程在内的所有线程对于t1表只可读，写被阻塞；t2表读写都被阻塞

lock tables 操作可以用 unlock tables 主动释放，也可以在客户端断开的时候自动释放。

该操作不仅阻塞其他线程的操作，也阻塞了当前线程的操作

对于innoDB这种支持行锁的引擎，一般不使用lock tables命令控制并发，影响过大

另一种表级锁：MDL(metadata lock)

MDL在访问一个表的时候会自动加上，MDL的作用是，保证读写的正确性。当表做增删改查操作时，加MDL读锁；当对表结构变更的时候，加MDL写锁。

  • 读锁之间不互斥，多线程可对同一张表增删改查
  • 读写锁之间、写锁之间互斥。两个线程同时给一个表增加字段，则第二个需要等待第一个执行完才能继续
  • MDL锁在语句执行开始时申请，事务结束后释放

如何给小锁安全的加字段

  • 解决长事务，当做DDL变更的表中正好在执行长事务，则从information_schema库的innodb_trx找到当前执行长事务，先kill掉长事务或者暂停DDL

  • 比较理想的状态：修改表结构语句alter table可以设置等待时间，如果该时间内拿不到MDL锁，则该时间内拿不到MDL锁，则放弃执行，不会阻塞后面的语句

    MariaDB/ALiSQL已经支持 DDL NOWAIT/WAIT n 这个语法

1
2
alter table tb1_name NOWAIT add column...
alter table tb1_name WAIT N add column...

行锁

两阶段锁

  • 定义：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放， 而是要等到事务结束时才释放

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

死锁

  • 定义：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态
  • 解决方案
    • 通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB 引擎默认值是50s
    • 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。

如何解决热点行更新导致的性能问题？

  • 如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
  • 控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了
  • 将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高

Innodb 行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。