社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
mysql> show status like 'innodb_row_lock%';
在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
我先给你举个例子。在下面的操作序列中,事务 B 的 update 语句执行时会是什么现象呢?假设字段 id 是表 t 的主键。
这个问题的结论取决于事务 A 在执行完两条 update 语句后,持有哪些锁,以及在什么时候释放。
实际上事务 B 的 update 语句会被阻塞,直到事务 A 执行 commit 之后,事务 B 才能继续执行。
事务 A 持有的两个记录的行锁,都是在 commit 的时候才释放的。
也就是说,在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
InnoDB实现了以下两种类型的行锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
如果一个事务请求的锁模式与当前的锁兼容,InnoDB 就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。
意向锁是InnoDB自动加的,不需用户干预。
对于UPDATE、DELETE和INSERT语句, InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;
事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁(S):
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
排他锁(X):
SELECT * FROM table_name WHERE ... FOR UPDATE;
用SELECT ... IN SHARE MODE
获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE
或者DELETE
操作。
但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT... FOR UPDATE
方式获得排他锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3种情形。
InnoDB这种行锁实现特点意味着:如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样!
(1)在不通过索引条件查询时,InnoDB会锁定表中的所有记录。
(2)由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
(4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB也会对所有记录加锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的Next-Key锁。
举例来说,假如emp表中只有101条记录,其empid的值分别是1、2、…、100、101,下面的SQL:
Select * from emp where empid > 100 for update;
是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。 InnoDB使用Next-Key锁的目的,是为了防止幻读,以满足相关隔离级别的要求。
范围条件加锁时使用Next-Key锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用Next-Key锁!
对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们选择InnoDB表的理由。
但在个别特殊事务中,也可以考虑使用表级锁。
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。
这时候,事务 A 在等待事务 B 释放 id=2 的行锁,而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放,就是进入了死锁状态。当出现死锁以后,有两种策略:
在 InnoDB 中,innodb_lock_wait_timeout
的默认值是 50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过 50s 才会超时退出,然后其他线程才有可能继续执行。
所以,正常情况下我们还是要采用第二种策略,即:主动死锁检测,而且 innodb_deadlock_detect
的默认值本身就是 on。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。
问题的症结在于,死锁检测要耗费大量的 CPU 资源。
一种头痛医头的方法,就是如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。但是这种操作本身带有一定的风险,因为业务设计的时候一般不会把死锁当做一个严重错误,毕竟出现死锁了,就回滚,然后通过业务重试一般就没问题了,这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。
另一个思路是控制并发度。根据上面的分析,如果并发能够控制住,比如同一行同时最多只有 10 个线程在更新,那么死锁检测的成本很低,就不会出现这个问题。一个直接的想法就是,在客户端做并发控制。但是,你会很快发现这个方法不太可行,因为客户端很多。
因此,这个并发控制要做在数据库服务端。如果你有中间件,可以考虑在中间件实现;如果你的团队有能修改 MySQL 源码的人,也可以做在 MySQL 里面。基本思路就是,对于相同行的更新,在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
对于InnoDB表,本章主要讨论了以下几项内容。
在了解InnoDB锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括以下几项:
注:文章内容参考自
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!