热点数据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时，为了防止死锁现象，应尽量避免事务相互依赖。当并发量巨大时，应尽量使用数据库中间件来管理请求，减轻数据库死锁检测的压力。