AlbertYang Blog

主题

Albert Yang2025-08-20
1944
6.9m
Get...

锁 ​

MySQL中加锁是为了处理并发问题，根据锁的粒度可以分为全局锁、表级锁和行锁。

全局锁 ​

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

加完之后整个数据库处于只读状态。

应用场景（不推荐） ​

全局锁的经典应用场景 数据库备份。

由于加全局锁，会导致整个数据库只读，所以一般不推荐使用。

可重复读进行备份 ​

备份数据库一般可以利用可重复读的事务隔离级别来实现，因为可重复读情况开始事务，会生成当前数据库的视图，保证整个事务期间以视图数据为准。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。

由于 MySQL 支持 MVCC （多版本控制协议），在开启事务的情况下，不影响其它线程对数据库进行更新等操作。

由于利用事务的隔离级别，要求 MySQL 使用的引擎要支持事务，像 MyISAM 便不支持事务，不能使用事务进行全局备份，只能利用全局锁 FTWRL 来实现。

表级锁 ​

MySQL 中表级锁有两种，一种是表锁，另一种是元数据锁（meta data lock，MDL）。

表锁（不推荐） ​

**表锁的语法是 lock tables … read/write。**与 FTWRL 类似，可以用 unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

表锁的粒度比较大，一般不推荐使用。

但是在没有元数据锁之前，如果要修改表结构，都需要加表锁。

元数据锁（MDL） ​

MySQL 5.5 版本中引入了 MDL，不需要手动加，在访问表的时候自动加上。

  • 对表数据进行增删改查的时候，加 读锁。
  • 对表结构进行修改的时候，加 写锁。

MySQL 支持多个线程同时对一张表进行增删改查，所以增删改查加读锁。

但是读-写互斥、写-写互斥。表示修改表结构时，要等待读锁释放或者其它写锁释放。

在事务情况下，执行SQL时申请MDL锁，但是执行完不会释放锁，而是直到事务结束才会释放锁。

使用MDL时，一定注意不要长时间阻塞增删改查，影响业务。

表结构修改问题 ​

MDL 能保证修改表结构的时候的原子性，不能多个线程同时修改表结构。

而修改表结构，类似加索引、加字段、删除字段等操作，都需要扫描全表，操作每一条记录。假如表的数据量很大的情况下，会出现性能问题。

大表的情况修改结构会长时间占用MDL写锁，影响对该表的查询。

假如在查询频率高的情况，由于写锁的存在，查询的线程会一直阻塞等待，导致线程爆满。

长事务阻塞问题 ​

大表由于数据量的原因，去改表结构，执行时间长是必然的。只能在特定时间去修改。

而对于小表，急需修改表结构的情况。

要解决两个问题：

  1. 其它线程的长事务问题。

    在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。

    如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

  2. 自身等待时间长，阻塞其它线程。

    1. 为执行语句增加超时时间，超时放弃获取。

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

      重复执行语句，指到获取MDL进而执行。

行锁 ​

MySQL 的行锁，是引擎自己实现的。MyIASM 就不支持行锁，若想要控制并发，只能使用表锁。InnoDB是支持行锁的，这也是 MyIASM 被 InnoDB 取代的原因之一。

行锁：针对某一行记录加锁，保证记录的原子性。

两阶段锁协议 ​

在 InnoDB 事务中，行锁在需要的时候加上，但是要等事务 commit 之后才会释放。

事务B 在 事务A commit 之前都处于阻塞状态。事务 A 的两条 SQL执行完，事务B依旧阻塞。

事务中合理安排锁顺序 ​

由于事务中的行锁只有在 commit 时才会释放。

所以尽量将会引起锁竞争的行锁，放在后面，减少锁的持有时间。

影院增加余额比较容易影响其它顾客的交易，产生锁竞争。所以可以按照3、1、2顺序调整，减少其锁的持有时间。

死锁 ​

在并发情况下，线程之间存在互相等待资源的情况，称为死锁。

比如行锁的情况下，事务A和事务B相互等待对方持有的锁，导致一直循环等待。出现死锁。

死锁解决策略 ​

  1. 过期策略。

    设置超时时间，超时放弃持有资源。可以设置innodb_lock_wait_timeout参数。

    innodb_lock_wait_timeout 默认时间是50s，意味着发生死锁时，最少要50s才能破坏死锁。

  2. 主动破坏死锁 - 死锁检测。

    主动发起死锁检测，发现死锁后，主动回滚其中的一个事务，保证其它事务继续执行，规避死锁。

    将参数 innodb_deadlock_detect 设置为 on，表示开启这个死锁检测。

    死锁检测是很耗费CPU资源的，因为事务开启后，便要不停的检测需要的锁资源有没有被其它事务持有。最后判断是否出现死锁。

间隙锁 ​

间隙锁只有在可重复读的隔离级别下才有，是为了解决可重复读的问题。

可重复读的幻读问题 ​

  1. 查询涉及范围查询：使用 BETWEEN、IN、LIMIT 和 OFFSET 等条件。
  2. 在同一个事务中，两次执行相同的范围查询。
  3. 在两次查询之间，有其他事务插入了新的行，这些新行满足原来的查询条件。
  4. 当前读，强制读取最新数据也会导致幻读。

间隙锁只要锁住的是范围内的数据，保证该范围数据不能被其它事务修改。

当执行一个范围查询并请求锁时，InnoDB会锁定这个范围内的所有现有记录，同时还会锁定那些不存在于当前查询结果中但可能在范围内的记录。这样做可以防止其他事务在这个范围内插入新的记录，从而避免幻读的发生。

临键锁 ​

间隙锁是对范围内数据进行加锁，防止间隙有数据新增或删除。

而还需要对行记录加行锁，保证行记录不被其它事务篡改。

间隙锁+行锁 = 临键锁 （Next-Key Locks），通过临键锁可以有效解决幻读的问题。

问题记录 ​

  1. 直接删除 10000条，是一个长事务。
  2. 每次删除500条，分20次删除。最为推荐，减少锁竞争和事务大小。
  3. 20个线程同时删除500条数据，容易产生行锁竞争。