问题描述
根据 innodb锁定模式 锁类型兼容性矩阵
X IX S IS X Conflict Conflict Conflict Conflict IX Conflict Compatible Conflict Compatible S Conflict Conflict Compatible Compatible IS Conflict Compatible Compatible Compatible
IX与IX兼容，但事实是，如果我们在会话1中通过select c1 from z where c1 = 1 for update获取一个IX锁，则尝试在 2中阻止通过select c1 from z where c1 = 1 for update获取IX的尝试，因此我认为它们不兼容.我在这里想念任何东西吗?
IX is compatible with IX, but the fact is if we acquire one IX lock by select c1 from z where c1 = 1 for update in session 1, trying to acquire IX by select c1 from z where c1 = 1 for update will be blocked in session 2, so I think they are not compatible. Did I miss anything here?
接受答案后进行
一个会话中的SELECT ... FOR UPDATE阻止另一个会话中的SELECT ... FOR UPDATE的原因是，他们不仅要询问表级别的IX锁，还要问行级别的X锁.都是因为X锁.
The reason why SELECT ... FOR UPDATE in one session blocks SELECT ... FOR UPDATE in another is they are asking not only IX lock on table level but also X lock on row level. It is all because of X lock.
推荐答案
https://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html 说:
这意味着多个线程可以获取IX锁.这些锁位于表级别，而不是行级别. IX锁意味着持有它的线程打算在表中的 somewhere 中更新某些行. IX锁仅用于阻止全表操作.
This means multiple threads can acquire IX locks. These locks are at the table-level, not the row-level. An IX lock means that the thread holding it intends to update some rows somewhere in the table. IX locks are only intended to block full-table operations.
如果您认为这是双向的，则可能会有所启发-如果正在进行全表操作，则该线程具有表级锁，该锁将阻止IX锁.
It may shed some light if you consider that it goes both ways -- if a full-table operation is in progress, then that thread has a table-level lock that blocks an IX lock.
DML操作必须首先获取IX锁，然后才能尝试行级锁.原因是您不希望在ALTER TABLE进行中或其他线程完成LOCK TABLES...WRITE时允许DML.
DML operations must first acquire an IX lock before they can attempt row-level locks. The reason is that you don't want DML to be allowed while an ALTER TABLE is in progress, or while some other thread has done LOCK TABLES...WRITE.
行级更改(例如UPDATE，DELETE，SELECT..FOR UPDATE)不会被IX锁定阻止.它们被其他行级更改或实际的全表锁(LOCK TABLES或某些DDL语句)阻止.但是，除了这些表操作之外，运行DML的多个线程可能可以同时工作，只要它们每个都在一组不重叠的行上工作即可.
Row-level changes like UPDATE, DELETE, SELECT..FOR UPDATE are not blocked by an IX lock. They are blocked by other row-level changes, or by an actual full table lock (LOCK TABLES, or certain DDL statements). But aside from those table operations, multiple threads running DML can probably work concurrently, as long as they are each working on a set of rows that don't overlap.
发表您的评论
第二个SELECT...FOR UPDATE未被阻止等待IX锁，它被阻止了等待已被另一个线程中的X锁锁定的行的X(行级)锁.
The second SELECT...FOR UPDATE is not blocked waiting on the IX lock, it's blocked waiting on the X (row-level) locks on rows that are already locked by X-locks in another thread.
我刚刚尝试过此操作，然后又运行了SHOW ENGINE INNODB STATUS，所以我可以看到被阻止的交易:
I just tried this and then I ran SHOW ENGINE INNODB STATUS so I could see the blocked transaction:
---TRANSACTION 71568, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 10, OS thread handle 140168480220928, query id 288 localhost root statistics select * from test where id=1 for update ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 802 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 71568 lock_mode X locks rec but not gap waiting
看到了吗?它说它正在等待被授予表test的主键索引上的lock_mode X锁.那是行级锁.
See? It says it's waiting for to be granted the lock with lock_mode X on the primary key index of the table test. That's a row-level lock.
让您对LOCK IN SHARE MODE感到困惑:
您正在谈论三个级别的SELECT.
- SELECT不请求任何锁定.没有锁会阻止它，也不会阻止其他锁.
- SELECT ... LOCK IN SHARE MODE请求对表进行IS锁定，然后对与索引扫描匹配的行使用S锁定.多个线程可以在一个表上持有IS锁或IX锁.多个线程可以同时持有S个锁.
- SELECT ... FOR UPDATE请求在表上使用IX锁，然后在与索引扫描匹配的行上使用X锁. X锁是专有的，这意味着它们不能在任何其他线程的同一行上拥有X锁或 S锁.
- SELECT requests no locks. No locks block it, and it blocks no other locks.
- SELECT ... LOCK IN SHARE MODE requests an IS lock on the table, and then S locks on rows that match the index scan. Multiple threads can hold IS locks or IX locks on a table. Multiple threads can hold S locks at the same time.
- SELECT ... FOR UPDATE requests an IX lock on the table, and then X locks on rows that match the index scan. X locks are exclusive which means they can't any other thread to have an X lock or an S lock on the same row.
但是X和S锁都不关心IX或IS锁.
But neither X nor S locks care about IX or IS locks.
以此类推:想像一个博物馆.
许多人，包括访客和策展人，都进入博物馆.参观者想观看绘画，因此他们戴着标有"IS"的徽章.策展人可能会替换绘画，因此会戴上标有"IX"的徽章.博物馆里可以同时有很多人，有两种徽章.他们不会互相阻挡.
Many people, both visitors and curators, enter the museum. The visitors want to view paintings, so they wear a badge labeled "IS". The curators may replace paintings, so they wear a badge labeled "IX". There can be many people in the museum at the same time, with both types of badges. They don't block each other.
在访问期间，认真的艺术迷将尽可能地靠近这幅画，并对其进行长时间的研究.他们很乐意让其他艺术迷在同一幅画前站在他们旁边.因此，他们正在做SELECT ... LOCK IN SHARE MODE，并且具有"S"锁，因为他们至少在学习过程中不希望替换绘画.
During their visit, the serious art fans will get as close to the painting as they can, and study it for lengthy periods. They're happy to let other art fans stand next to them before the same painting. They therefore are doing SELECT ... LOCK IN SHARE MODE and they have "S" locks because they at least don't want the painting to be replaced while they're studying it.
策展人可以替换一幅画，但是他们对认真的艺术迷们很有礼貌，他们会等到这些观众完成后继续前进.因此，他们正在尝试执行SELECT ... FOR UPDATE(或者只是UPDATE或DELETE).他们现在会通过挂个小提示展示正在重新设计"来获得"X"锁.认真的艺术迷希望以适当的方式观看艺术作品，并带有漂亮的灯光和一些描述性的漆面.他们将在他们接近之前等待重新设计完成(如果尝试，他们将得到锁定等待).
The curators can replace a painting, but they are courteous to the serious art fans, and they'll wait until these viewers are done and move on. So they are trying to do SELECT ... FOR UPDATE (or else simply UPDATE or DELETE). They will acquire "X" locks at this time, by hanging a little sign up saying "exhibit being redesigned." The serious art fans want to see the art presented in a proper manner, with nice lighting and some descriptive placque. They'll wait for the redesign to be done before they approach (they get a lock wait if they try).
此外，您可能去过一家博物馆，那里有更多休闲游客四处逛逛，试图远离他人.他们从房间中间看画，但不要太近.他们可以看到其他观众正在看的同一幅画，并且可以窥视严肃的艺术迷的肩膀，也可以看到正在观看的那些画.在替换绘画时，他们甚至可能凝视策展人(他们不在乎是否瞥见尚未正确安装和照明的绘画).因此，这些临时访客不会阻止任何人，也没有人阻止他们的观看.他们只是在做SELECT并且他们不要求任何锁.
Also, you've probably been in a museum where more casual visitors wander about, trying to stay out of other people's way. They look at paintings from the middle of the room, not approaching too close. They can look at the same paintings other viewers are looking at, and they can peek over the shoulders of the serious art fans, to look at those paintings being viewed too. They may even gawk at the curators while they're replacing paintings (they don't care if they glimpse a painting that hasn't been mounted and lighted properly yet). So these casual visitors don't block anyone, and no one blocks their viewing. They are just doing SELECT and they don't request any locks.
但是，也有一些建筑工人应该拆除墙壁和东西，但是当建筑物中有人时，他们将无法工作.他们将等待所有人离开，一旦他们开始工作，就不会让任何人进入.这就是IS和IX徽章的存在如何阻碍DDL(施工工作)，反之亦然.
But there are also construction workers who are supposed to tear down walls and stuff, but they won't work while there's anyone in the building. They'll wait for everyone to leave, and once they start their work, they won't let anyone in. That's how the presence of either IS and IX badges block DDL (the construction work), and vice-versa.
这篇关于为什么IX锁与InnoDB中的另一个IX锁兼容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！