MySQL锁的种类

  • 按照粒度分，MySQL锁可以分为全局锁、表级锁、行锁
  • 全局锁会锁住整个表，整个库无法修改
  • 表级锁分为表锁（数据锁）和元数据锁

  • 行锁会锁住数据行，分为共享锁和独占锁

    全局锁

    FTWRL

    FTWRL (Flush tables with read lock)

  • 所有的表加读锁，此命令使整个库处于只读状态

  • 主要用途是保证备份的一致性

  • 不要随意使用，杀伤性极大，要在备库使用

    表级锁

    表锁（数据锁）

  • 命令:lock tables XxX read/write

  • 表锁是非常重的锁，在lnnoDB中使用很少

    元数据锁（metadata lock）

  • 元数据指的是表的结构、字段、数据类型、索引等

  • 事务访问数据时，会自动给表加MDL读锁

  • 事务修改元数据时，会自动给表加MDL写锁

    行锁

  • 行锁也有两种类型，有很多种叫法:

    • 读锁/写锁
    • 共享锁/排他锁
    • 共享锁/独占锁
    • S锁/X锁（share/exclusive）
  • S锁不是不让读，而是自己要读，不让别人写
  • X锁不只是不让写，而是自己要写，不让别人读写
  • 只有S锁和S锁之间可以兼容，其他均不兼容

  • MVCC机制快照读可以绕过X锁进行读取

    死锁

    当前读会对数据行加锁，事务提交前无法释放，其他事务更新相同数据时会等待锁，造成更新性能差。
    解决办法：

  • 调整innodb_lock_wait_timeout 参数

  • 默认为50，即等待50秒还未获取锁，当前语句报错
  • 如果等待时间过长，可以适当缩短此参数。

长事务中，行级锁长时间无法释放，造成其他事务等待，容易产生死锁。死锁指的是两个事务都依赖对方的释放。
解决办法：
主动死锁检测：innodb_deadlock_detect，mysql默认开启
发现死锁时回滚代价较小的事务

在高并发情况下，开启死锁检测性能上可能存在问题，需要对系统仔细分析， 可以尝试关闭innodb_deadlock_detect，并将innodb_lock_wait_timeout调短，比如2s，同时需要结合业务，尽量避免长事务和死锁的发生。

MDL锁（元数据锁）

MDL锁发生的情况：

  • 事务访问数据时，会自动给表加MDL读锁
  • 事务修改元数据时，会自动给表加MDL写锁

遇到锁不兼容时，申请MDL锁的事务形成一个队列
解决办法：

  • alter table之前，查看是否有长事务还未提交

  • 查看长事务: information_schema库innodb_trx表

    如何查看影响性能的锁

  • 查看长事务： information_schema库innodb_trx表

  • 查看锁:：information schema库INNODB_LOCKS表

  • 查看阻塞的事务：information_schema 库INNODB_LOCK_WAITS表

    如何查看影响性能的锁（8.0）

  • 查看锁：performance_schema库data_locks表

  • 查看锁等待：performance_schema库data_lock_waits表

  • 查看MDL锁：performance_schema库metadata_locks表

    业务建议

  • 控制长事务，没有必要的情况下不开启事务

  • 数据修改（当前读)尽量放在事务后部，降低锁时间