Mysql锁

mysql的两种锁

• 表锁：开销小，加锁快，锁粒度大，不会出现死锁；并发度最低，遇到锁冲突的概率高
• 行锁：开销大，加锁慢，锁粒度小，会出现死锁；并发度高，遇到锁冲突的概率低

mysql不同的存储引擎支持不同的锁机制，myisam支持表锁，innodb支持表锁和行锁；

表锁适合查询，少量按索引条件更新的应用

行锁适合大量更新少量数据的场景，也是和并发查询

innodb和myisam最大的不同就是，innodb支持事务和行锁

Innodb锁

并发事务隐含的问题

• 丢失更新

一个事务更新的数据被另外事务的更新的数据覆盖，丢失掉本事务更新的数据

• 脏读

读到别的事务未提交的数据

• 不可重复读

条件相同，两次读取的数据不一致，不可重复读

• 幻读

查询到一批数据，在下次查询之前，有别的事务插入了数据，导致再次查询时查到的数据不一致，出现“幻觉”

更新了一批数据，别的事务又做了更新，查询时和预期出现偏差，出现“幻觉”

事务隔离级别

对于丢失更新的问题，通常是可以避免的，需要应用程序对要更新的数据加必要的锁来解决。

对于脏读、不可重复读、幻读，实际上是数据库数据一致性状态的问题，需要数据库提供不同的事务隔离级别来实现，mysql提供的方案是1.对数据加锁；2.MVCC（多版本并发控制），本质是提供某时刻的数据库快照。

事务的隔离级别有：

• 读未提交
• 读已提交
• 可重复读
• 串行化
  这是ISO制定的标准，各数据库实现了该标准。

越往下级别越严格，更不容易造成数据并发问题，但是并发度越低，付出的代价越大，mysql默认的事务隔离级别是可重复读，其它数据库（oracle等）的默认隔离级别是读已提交。

Innodb锁模式

为了提供多粒度的锁，innodb表锁和行锁是并存的

行锁有两种：

• 共享锁（X）：允许事务读一行，不允许其它事务获取该数据的排它锁；
• 排它锁（S）：允许获得排它锁的事务更新数据，不允许其它事务获取该数据的共享锁和排它锁；

说白了，共享锁和排它锁有点像读锁和写锁，读读之间是互斥的，读写/写写之间是互斥的（这里跟严格意义的读写锁有点区别）。对于更新操作（insert、update、delete），mysql默认会加排它锁，对于普通的查询操作（select），mysql不会加任何锁，但是我们可以手动加，方式如下：

select ...... lock in share mode，为共享锁（mysql 8.0版本之后也可以写成for share）
select ...... for update，为排它锁

用 select... in share mode 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 update 或者 delete 操作。但是如果当前事务也需要对该记录进行更新操作,则有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select... for update 方式获得排他锁。

表锁也有两种：

• 共享锁（IX）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
• 排它锁（IS）：事务打算给数据行加行排它锁，事务在给一个数据行加排它锁前必须先取得该表的 IX 锁

表锁的是innodb内部的锁，不需要我们手动加。

Innodb行锁的实现方式

innodb的行锁是通过对索引项加锁实现的，如果没有索引项，会通过隐藏的聚簇索引对记录加锁。

• record lock： 又称“行锁”，对索引项加锁
• gap lock： 对索引项之间的“间隙”，比如数据库有age=1和age=5两条数据，在1-5之间会有间隙，可以加间隙锁，负无穷到-1，5到正无穷也有间隙，一般是左开又闭的
• next-key lock： 前两种的结合，对记录及其前面的间隙加锁

所以如果在不通过索引条件查询数据时，默认对所有记录都会加锁，innodb 会锁定表中的所有记录，跟表锁的效果一样；如果使用了索引条件查询，例如“select * from user where id=1 for update”这样的语句，如果id是索引，这句查询的效果只会对id=1的那条记录加行锁。

(有一种情况除外，如果数据表中的数据很少，mysql认为全表扫面的效率更高，可能也不会使用索引，此时就会对全表数据加锁，所以，在分析锁冲突时，最好使用explain语句求证下)

为什么使用next-key lock

当使用范围查找时，会把存在的数据和间隙都加锁，避免幻读的发生。

比如数据库中有id=1，2，...，10一共10条数据，如果使用条件id>9查询，会把id=10使用行锁（record lock）加锁，对（10，正无穷）之间使用间隙锁加锁，也就是使用了next-key lock。如果事务1读取了id>9的数据，此时事务2 插入一条id=11的数据，事务1再次读取时读到的数据就不一样，发生幻读，使用next-key lock就可以避免这个问题。另外，next-key lock 对恢复和复制也是需要的。

什么时候使用表锁

表锁是mysql内部加的，大部分时间都使用行锁，因为事务和行锁是我们使用innodb的理由，只在一些特殊场景中会使用表锁：

• 更新大表中的大部分数据。此时使用行锁，更新效率低，数据需要一行行加锁，效率不及表锁，而且容易造成其它事务等待锁，容易造成锁冲突和死锁；
• 涉及多个表。可能引起死锁造成大量事务回滚，可以用表锁，一次性锁住多个表。

innodb下，表锁使用可以使用语句"lock table"，解锁用"unlock table"，要说明的是，提交或回滚事务不会自动解锁，所以必须手动解锁，但是解锁会默认提交事务。

死锁优化

myisam不会发生死锁，因为myisam是一次性获取所有的锁，而innodb是操作数据时才获取对应的行锁或表锁，所以innodb会发生死锁。

发生死锁后，innodb 一般都能自动检测到，并使一个事务释放锁回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或表锁的情况下，innodb 并不能完全自动检测到死锁，只需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重的性能问题，甚至拖垮数据库。

通常来说，在业务流程，数据库表对象设计，事务大小，以及数据库sql语句进行合理的设计或改造，死锁都可以被避免或解决。

几种避免死锁的方法：

1.如果涉及多张表并发存取，应该尽量以相同的顺序来访问，相同顺序访问数据库，不会造成死锁；

2.在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；

3.在事务中，如果更新记录，应该申请排他锁更新，而不是想申请共享锁，更新时再申请排他锁的方式，因为不同的事务同时申请了共享锁后不释放，排它锁也可能互相等待；

查看 Innodb 行锁争用情况

通过查看 innodb_row_lock 状态变量可以分析数据库的锁竞争情况

image

如果发现锁争用比较严重，如 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值比较高。

本文主要内容引用自：
https://segmentfault.com/a/1190000011164489