Damion's Blog
所有文章 标签 分类 作品集
Damion's Blog

目录

【MySQL】锁机制

 收录于 MySQL
  发布于  

锁的概念

锁被用于解决因资源共享，造成的并发问题。

或者说，数据库的锁是为了解决事务的隔离性问题，让事务之间互不影响。

锁的分类

锁的具体分类为：

  • 全局锁

  • 表级锁

  • 行锁

全局锁

全局锁用于对整个数据库实例加锁。命令如下：

1

flush tables with read lock;

加锁之后，数据库处于只读状态。使用unlock tables释放锁。

其他线程的以下语句会被阻塞：数据增删改、删表建表、修改表结构、删库建库和更新类事务的提交语句。

全库逻辑备份

全库逻辑备份，是全局锁的典型使用场景。此时使用全局锁会存在如下缺陷：

  • 如果在主库执行备份，会让更新无法执行。

  • 如果在从库执行备份，备库将不能执行主库同步过来的bin log，导致主从延迟。

  • 如果不加锁，会导致得到的备份不在一个逻辑时间点。

--single-transaction

如果mysqldump使用了参数--single-transaction，导出数据前将会启动一个事务，确保拿到一致性视图。通过该方式，可以避免加全局锁。

前提是引擎要支持可重复读隔离级别和事务，并且所有表都使用了该引擎，才能使用该参数。MyISAM不支持事务，因此无法使用该参数。

readonly

语法为：

1

set global readonly=true

readonly也可以让全库进入只读状态，但是全局锁场景下不推荐。原因如下：

  • 有些系统中，readonly会被用来做其他逻辑，如判断主库和备库。

  • 如果客户端发生异常，会自动释放使用FTWRL加的全局锁，使用readonly后数据库会一直保持该状态。

表级锁

表级锁会一次性对一张表整体加锁，具体分为表锁和元数据锁两种。

表锁

加锁语法如下：

1

lock tables 表名... read/write

read锁会阻塞本线程和其他线程的写操作，不会阻塞读操作。本线程不能对其他表进行读写操作，其他线程可以对其他表进行读写操作。

write锁会阻塞其他线程的读写操作，不会阻塞本线程的读写操作。本线程不能对其他表进行读写操作，其他线程可以对其他表进行读写操作。

锁的释放时机如下：

  • 客户端断开时会自动释放。

  • 执行unlock tables命令也会主动释放。

  • 再次执行lock tables获取表锁时，会释放之前持有的锁。

  • 执行start trasaction或者begin开启事务时，会释放锁。

MDL(meta data lock)

MySQL5.5版本引入了MDL。

锁的规则如下：

  • 对一个表做增删改查操作时，会自动对该表加MDL读锁，对表结构做变更操作时，会自动对该表加MDL写锁。

  • 读锁之间不互斥，多个线程可以对一张表增删改查。

  • 读写锁、写写锁之间互斥。

锁的释放时机如下：

  • 事务执行时会申请MDL锁，等到事务提交或回滚后锁才会释放。

  • 会话断开时，会自动释放。

如果程序中有lock tables和unlock tables语法，可以尽量改成begin和commit。

修改表结构

给表加字段、或者加索引都需要小心，否则很有可能会出现如下问题：

  1. 事务A正在读表数据，会加MDL读锁。

  2. 有事务B想修改表结构，会请求MDL写锁，被阻塞。

  3. 其他事务想读取表数据，会被MDL写锁阻塞。

  4. 线程会爆满。

如果想给表加字段。可以设置语句的等待时间，等待时间内拿不到写锁就放弃，之后再重试。MariaDB和AliSQL已经支持NOWAIT/WAIT n语法。

分析表锁状态

  1. 查看表锁定的状态
1

show open tables; # In_use是1代表加了锁
  1. 分析表锁的竞争状态
1

show status like 'table%';

  • table_lock_immediate：立即获取到表锁的次数。

  • tale_locks_waited：等待表锁的次数，该值越大，锁竞争越大。

当table_locks_immediate / table_locks_waited > 5000，建议采用InnoDB引擎。

行锁

行锁是各个引擎自己实现的，MyISAM不支持行锁，而InnoDB支持行锁。

行锁按类型分为：读意向锁、写意向锁、读锁、写锁、自增锁。

行锁按粒度分为：记录锁、间隙锁、next-key lock、插入意向锁。

意向锁

意向锁是一种不与行级锁冲突的表级锁。

  • 读意向锁：也叫意向共享锁IS，事务有意向对表中的某些行加共享锁（S锁）。

  • 写意向锁：也叫意向排他锁IX，事务有意向对表中的某些行加排他锁（X锁）。

意向锁不需要手动添加，在给数据行加共享锁/排他锁之前，会自动给数据所在表加意向锁。

意向锁存在的意义：

当给整张表加锁时，需要判断表是否持有行锁。有了意向锁，就不需要扫描整张表查找行锁，只需要判断表是否持有意向锁即可。

意向锁的冲突规则为：

  • 意向锁和行锁不冲突。

  • 意向锁和AUTO_INC表锁不冲突。

  • 意向共享锁和表级共享锁兼容。

  • 意向共享锁和表级排他锁冲突。

  • 意向排他锁和表级共享锁冲突。

  • 意向排他锁和表级排他锁冲突。

读写锁

  • 读锁：也叫共享锁（S锁）。加了读锁的记录，所有事务都可以读取，但是不能修改。可以有多个事务对记录加读锁。

  • 写锁：也叫排他锁（X锁），或独占锁。加了写锁的记录，只有持有锁的事务才能读写。同一时间只能有一个事务加写锁。

对于增、删、改操作，InnoDB会自动给记录添加排他锁。对于查询操作，InnoDB不会加任何锁。

如果想显式加锁，语法为：

  • 加共享锁：

    1

    SQL查询语句 LOCK IN SHARE MODE;

  • 加排他锁：

    1

    SQL查询语句 FOR UPDATE;

自增锁

自增锁也叫AUTO_INC，是一种表锁。当插入的表中有自增列，且数据库需要自动生成自增值时，会为该表加AUTO_INC表锁。

下文会详细叙述自增锁的机制原理。

记录锁

记录锁就是给每行记录加上的行锁，又称record锁。

间隙锁

间隙锁是一种加在索引之间的锁，又称gap锁，只有在InnoDB的可重复度读隔离级别下才使用。

使用间隙锁，可以防止其他事务在这个范围内插入或者修改记录，从而避免出现幻读现象。

间隙锁和间隙锁之间互不冲突。

next-key lock

next-key lock是行锁和间隙锁的组合。

对于存储引擎Innodb，如果事务的隔离级别为可重复读，则此时使用的锁就是next-key lock。

插入意向锁

插入意向锁，是一种间隙锁，不是意向锁。

只有insert的时候，才会加插入意向锁。

插入意向锁之间互不冲突，不同事务对同一个间隙插入值时，只要索引无冲突，事务之间就不会有冲突。

如果某个间隙已经存在该锁，在该间隙加其他锁不会引起冲突。

如果某个间隙存在间隙锁，则该间隙无法再加插入意向锁。

两阶段协议

InnoDB事务中，行锁是在需要的时候加上去的，等到事务结束时释放。这就是两阶段锁协议。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后移。

查看行锁状态

1

show status like '%innodb_row_lock%';

  • Innodb_row_locl_current_waits：当前正在等待锁的数量。

  • Innodb_row_lock_time：等待总时长，从系统启动到现在，一共等待的时间。

  • Innodb_row_lock_time_avg：平均等待时长，从系统启动到现在的平均等待间。

  • Innodb_row_lock_time_max：最大等待时长，从系统启动到现在的最大等待间。

  • Innodb_row_lock_waits：从系统启动到现在，等待的次数。

死锁

并发系统中，不同线程都在等待对方释放资源时，会导致这些线程进入无限等待的状态，称为死锁。

解决死锁的方式有：

  • 等待超时

  • 死锁检测

  • 控制请求的并发度

等待超时

所谓的等待超时，就是一直等待锁释放直到超时。

超时时间由innodb_lock_wait_timeout参数控制，默认为50s。

注意不能将innodb_lock_wait_timeout设置的很小，否则会造成简单的锁等待超时。

死锁检测

所谓的死锁检测，是当一个事务出现锁等待时，判断它依赖的资源有没有被别的线程锁住，最后判断是否出现循环等待，即死锁。死锁检测会消耗大量cpu资源。

发现死锁后，数据库会回滚死锁链条中的某一个事务，从而解决死锁。

将参数innodb_deadlock_detect设置为ON可开启死锁检测。

控制并发度

通过控制并发度，也可以减少锁等待个数，降低死锁检测成本。如设计中间件进行请求排队，或者将一行数据改成逻辑上的多行。

自增锁

自增锁是一种特殊的表级锁，当事务将数据插入到具有自增列的表中，且申请了自增值时，会产生自增锁。

自增锁的出现，是为了解决高并发场景下自增值的竞争问题。

insert种类

自增锁和insert有关，所以需要先讨论insert的种类。

  • insert like

    任何会产生新记录的插入操作，都叫insert like。

    如insert，insert ... select，replace，replace ... select和load data。

  • simple insert

    插入的记录的行数是确定的。

    如insert into和replace，但是不包括insert ... on duplicate key update...。

  • bulk inserts

    插入的记录数不能马上确定。

    如insert ... select，replace ... select，load data。

  • mixed-mode inserts

    有一部分自增值值是给定的，有一部分自增值是未指定的。

    如insert ... on duplicate key update...。

innodb_autoinc_lock_mode

MySQL5.1.22版本之后，引入了innodb_autoinc_lock_mode参数，用来控制自增锁，默认值1。

  • 参数为0时，自增锁是语句级别，等到语句执行结束，自增锁才会释放。

  • 参数为1时，分两种情况讨论。对于simple insert，自增锁在申请之后就马上释放；对于bulk inserts，自增锁需要等待语句执行结束。

  • 参数为2时，所有的自增锁，都是在申请自增值之后马上释放，并发性能最好。

对于bulk inserts，使用语句级别的自增锁，是为了保证主从复制时的数据一致性。

建议配置innodb_autoinc_lock_mode为2，并且配置binlog_format为row，这样，既能提升并发性，又不会出现数据一致性问题。

bulk inserts申请自增id

  • 第一次申请时，分配1个。

  • 同一语句去申请自增id，每次申请到的个数，都是上一次的两倍。

mixed-mode inserts申请自增id

每次申请的自增id个数，等于插入的记录个数。

特别需要注意的是insert ... on duplicate key update...语句，申请的自增id个数，等于插入记录数和更新记录数之和。

锁的优化建议

  • 尽可能使用索引来完成检索，避免无索引时导致行锁升级为表锁。

  • 合理设计索引，尽量缩小锁的范围。

  • 尽量减少索引条件，缩小索引范围，从而避免间隙锁。

  • 控制事务大小，减少锁定的资源量。

  • 尽可能使用较低的事务隔离级别。

更新于 2020-08-02
 MySQL
返回 | 主页