浅析MySQL的锁

小伙伴们对数据库编程感兴趣吗？是否正在学习相关知识点？如果是，那么本文《浅析MySQL的锁》，就很适合你，本篇文章讲解的知识点主要包括MySQL。在之后的文章中也会多多分享相关知识点，希望对大家的知识积累有所帮助！

MySQL锁种类

MySQL中，当出现并发访问时，数据库需要合理的控制资源的访问规则。而锁作用就是为了实现这些访问规则。

根据加锁的范围，MySQL中的锁可以分为：全局锁，表级锁和行级锁。

MySQL全局锁

全局锁是对整个数据库进行加锁。
MySQL提供有

Flush table with read lock

命令来实现全局加锁，简称

FTWRL

。

FTWRL

FTWRL

命令主要用于全局一致性备份，该命令执行后，数据库完全处于只读状态。增删改操作，事务的提交和表结构修改等操作都会处于阻塞状态。
那么，

FTWRL

是怎么做到锁全局呢？它主要做了3件事：
1、上全局读锁（lock_global_read_lock）阻塞所有的更新操作
2、清理表缓存（close_cached_tables）将缓存数据刷新到磁盘上落库
3、上全局commit锁（make_global_read_lock_block_commit）阻塞事务的提交

这样就做到了全局保障，使得我们备份全库时，就不必担心数据的一致性出现问题。
但是，由于该锁的力度太大，如果在主库操作，则业务的数据操作就会完全停摆，如果在从库操作，则会使从库无法执行主库同步的binlog，导致主从延迟。

当连接断开或者执行

unlock tables

命令时，即释放全局锁。

single-transaction

MySQL自带的mysqldump备份工具中带有一个参数为：

single-transaction

，该参数会在备份时，将数据库的隔离级别调整为可重复读，然后启动事务，得到一致性数据视图，然后再执行

unlock tables

解锁，由于MVCC的支持，备份的过程中不影响正常更新操作。
但是该参数虽然好用，却只适用于支持事务的InnoDB引擎，对于MyISAM，还是得使用

FTWRL

来锁库备份。

MySQL表级锁

MySQL的表级锁分为表锁和元数据锁（meta data lock，MDL）两种。

表锁（lock table ... read/write）

举个简单的例子就能理解表锁的概念：
如：

线程t1

执行了

lock tables t_a read,t_b write

，那么其他线程对

表t_a的写操作

和对

表t_b的读写操作

都将会被

阻塞

，且

t1自己

也只能

读t_a表

以及

读写t_b表

，对其他表的访问也是

禁止

的。
最初表锁是用来处理并发的，但是我们可以发现，锁整张表的影响还是很大，会导致业务停摆。

元数据锁 MDL

MDL是MySQL在5.5版本中引入的，在对表进行增删改查时，系统会自动的为其加读锁，对表结构进行修改时，系统会自动为其加写锁。读锁和读锁可并行，读锁写锁，写锁写锁之间互斥。
需要注意的是，MDL直到事务提交时才会被释放，在做表结构修改时，注意不要锁住线上的查询和更新。
有个经典的例子：

请求A：begin事务，执行select，未commit（加读锁，未释放MDL）
请求B：select（自动提交了）（加读锁，已释放）
请求C：alter表结构（此时加写锁，读写互斥，阻塞）
请求D：select（加读锁，等待C，阻塞）

其中A，B因为都是读锁，可正常访问数据，C因为是更改表结构，此时要加写锁，但是A事务未提交，读锁未释放，导致阻塞。之后的请求想要获取MDL读写锁时，发现请求A的MDL写锁请求正在阻塞，那么全都进入了阻塞，相当于该表被堵死，之后的请求完全无法读写了。
如果该表又读写频繁，且MySQL客户端有重试机制，当超时后会自动新起一个同样的请求，那么数据库会马上被挤爆。
所以我们为在数据表上进行表结构修改时，一定到慎之又慎，确保没有未提交的事务（查看information_schema 库的 innodb_trx 表看是否有事务未提交），且最好咨询专业的DBA人员并在闲时进行操作，防止一个小小的修改导致业务宕机。

MySQL行级锁

行锁是引擎提供的，在MySQL中，InnoDB支持行锁，MyISAM不支持行锁，这也是为什么MySQL推荐我们使用InnoDB引擎，因为行锁对并发访问更友好。
但是和MDL释放锁类似，在InnoDB事务请求中，行锁在需要的时候添加，在事务提交后才释放，也就是说，当begin一个事务，update一个字段，未commit时，其他事务的update操作必须等待前一个事务commit后，释放掉行锁才能执行更新操作。

当我们使用事务操作多张表，加多个行锁时，一定要注意将影响最大的那一行最后加锁，减少锁时间，拿丁奇老师的例子来说明：

业务如下：
1、从顾客A账户扣除影票金额
2、从影院B账户添加该金额
3、记录交易日志

为了保证交易的原子性，这三条操作需要放到一个事务中处理，因为操作2的行记录是其他事务也需要使用的，所以为了保证锁等待时间最短，最优的解决办法是将执行顺序变为3，1，2。

死锁

正常的业务中，我们涉及到的更新操作不会是上面的例子中那么简单，当更新操作涉及到多表多字段的时候，如果不慎，很容易陷入死锁。
即：当并发时，不同线程出现循环资源依赖，涉及的线程在互相等待对方释放锁，就会导致陷入死循环的状态，最终导致死锁。
如：

事务A在等事务B释放id=2的行锁，事务B又在等待事务A释放id=1的行锁，就导致了死锁现象。
MySQL中针对死锁有两种处理方式：
1、

innodb_lock_wait_timeout=50

超时自动退出
2、

innodb_deadlock_detect=on

死锁检测
针对第一种超时退出而言，让第一个被锁住的事务超时50s后自动退出，其他请求再执行，对我们的业务来说是无法接受的。虽然我们可以将超时时间设置的小一些，但是太小如1s的话，可能会波及到正常的事务提交，是不可取的。
所以，最好的方式还是第二种，死锁检测。但是需要注意的是因为每个需要加行锁的事务，都需要顺藤摸瓜的去检测是否会导致死锁，虽然不是扫描所有的事务，但是当请求量很大的时候，死锁检测也是很耗费CPU资源的，你会发现，时间都浪费在了检测上，事务却没执行几个，CPU利用率还很高。

InnoDB是通过索引来实现的行锁，当更新列上没有索引时，其更新会锁整张表。如

update T set name='zhangsan' where age=10

，若age列无索引，这条语句将会锁整表。这是因为InnoDB需要确保当你执行该语句时，必须阻止其他事务插入age=10的行数据。
但是如果说执行的是

update T set name='zhangsan' where age=10 limit 1

的话，就会只锁定一行。

大并发的性能解决

那么对于热点行更新，我们最好怎么解决其性能为题呢？
个人认为，最优的解决方案就是使用数据库中间件控制到库的并发量。
原理就是，请求到来时，中间件将其接住，放入队列，每次释放一定量的请求入库操作，这样能有效的控制并发量，减轻数据库的死锁检测压力。
（在使用连接池的情况下，由于连接会复用，如果一个连接执行了set sql_select_limit=1，当其他业务复用该连接是，该设置也会生效。为了避免这种情况，5.7版本，MySQL提供了一个reset_connection接口，我们调用后连接会被重置，历史数据被清空，避免以上问题。）
网上还看到其他的解决方案：
1、关闭数据库的死锁检测（关闭的风险颇高）
2、将热点行拆分为多行或多表，分流操作以达到减少并发量的效果（此种方式需要注意数据的操作合法性，如行数据不能为0，但当前分行已为0了该怎么办？）

最后

MySQL提供了全局锁（FTWRL），表级锁（表锁和MDL），行锁（引擎实现）。
全局锁在全量备份时使用，但是会锁定全库为只读，可能会引起业务问题。在引擎支持事务时，mysqldump备份时，使用--single-transaction参数执行全量备份更加友好，依据MVCC，可重复读的隔离级别，不影响正常业务更新。（但是无论怎么备份都会影响到主从结构的一致性问题，这是不可避免的）
表锁是MySQL最初用来解决并发而引入的，但由于它的限制面太广，引入InnoDB的行锁之后，一般就不再使用。
MDL是MySQL为了保证DDL操作和DML操作的一致性，防止DML操作时，其他线程进行DDL操作，导致数据一致性出错。MDL锁是系统自动加上的，不需要显式执行，且它在事务提交时才会释放。
行锁是InnoDB引擎支持的锁，行锁在使用时加上，但是和MDL类似，在事务提交时才会释放。所以一定要注意，事务执行完及时commit。且为了较少锁等待，将公用的表操作尽量放在后面。
使用InnoDB时，为了防止死锁现象，应尽量避免事务相互依赖。当并发量巨大时，应尽量使用数据库中间件来管理请求，减轻数据库死锁检测的压力。

好了，本文到此结束，带大家了解了《浅析MySQL的锁》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！