MySQL中的锁

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁是对整个数据库实例加锁。加了这个锁之后整个数据库都处于只读状态。通过Flush tables with read lock开启。

之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是：做全库逻辑备份。

危害：

  • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

解决方案（但是仅限于使用事务的引擎，如InnoDB引擎）：

所以官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

使用的语法是lock tables … read/write可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

这个表锁有个特点：除了会限制别的线程读写之外，还会限制本线程也只能进行加了锁的操作；例如加了read锁之后只能进行读。

元数据锁

**MDL（metadata lock)**，元数据锁同样分为读锁和写锁。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。（这个锁是系统默认加上的）

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

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

  • 首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。

  • 给DDL加等待时间：ALTER TABLE tbl_name NOWAIT add column ...或ALTER TABLE tbl_name WAIT N add column ...

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的；InnoDB是支持行锁的。

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

  • 所要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

在解决幻读问题时，会引入间隙锁（Gap Locks）以及临键锁（Next-Key Locks）。

死锁检测

MySQL对于死锁有两种基本策略：

  • 一种策略是：

    直接进入等待，直到超时。这个超时时间可以通过参innodb_lock_wait_timeout来设置。但是这种策略时间很难控制。

  • 另一种策略是：

    发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。但是会带来额外的负担。

其余方法：

  • 进行并发控制限流
  • 也可以把其中一个记录进行拆分，这样减少了冲突的概率。例如电影院账户总额分为10条记录，有人买票了随机挑一条记录加上。