https://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html says:
Thus, intent locks block nothing but complete table queries (e.g. LOCK TABLES ... WRITE). The main purpose of IX and IS locks is to show that someone is blocking a row or blocking a row in a table.
This means that multiple threads may acquire IX locks. These locks are at the table level, not at the row level. Lock IX means that the thread holding it intends to update some rows somewhere in the table. IX locks are intended only for locking operations with a full table.
It can shed some light if you think that it is going in both directions - if the full table operation is performed, then this thread has a table-level lock that blocks IX lock.
DML operations must first obtain IX locks before they can try to lock rows. The reason is because you do not want DML to be resolved while ALTER TABLE running, or when some other thread has performed LOCK TABLES...WRITE .
Row level changes such as UPDATE , DELETE , SELECT..FOR UPDATE are not blocked by IX lock. They are blocked by other row-level changes or by actually completely locking the table ( LOCK TABLES or some DDL statements). But besides these table operations, several threads that work with DML may work simultaneously if they work on a set of rows that do not overlap.
Your comment:
The second SELECT...FOR UPDATE not blocked waiting for IX lock, it blocked waiting for X locks (at the row level) in rows that are already locked by X-locks in another thread.
I just tried this and then I ran SHOW ENGINE INNODB STATUS to see a locked 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
Cm? It says that he is waiting for the lock to lock_mode X in the index table's primary key test . This is row level locking.
Repeat your confusion regarding LOCK IN SHARE MODE :
You are talking about three levels of SELECT .
SELECT does not request locks. No locks block it, and it does not block other locks.SELECT ... LOCK IN SHARE MODE requests an IS lock on the table, and then S locks the rows that match the index scan. Multiple threads may contain IS locks or IX locks on the table. Multiple threads can simultaneously hold S-locks.SELECT ... FOR UPDATE requests an IX lock on the table, and then X locks the rows matching the index check. X locks are exclusive, which means that no other thread can have X lock or S lock on the same line.
But neither X nor S blocks IX or IS locks.
Think of this analogy: imagine a museum.
The museum includes many people, both visitors and curators. Visitors want to see the paintings, so they wear a badge that says "IS". Curators can replace the paintings, so they wear a badge with the inscription "IX". A museum can have many people at the same time with both icons. They do not block each other.
During their visit, serious art fans will come as close as possible to the painting and study it for long periods of time. They are happy that other art fans are standing next to them until the same picture. So they do a SELECT ... LOCK IN SHARE MODE , and they have S locks because they at least don't want the painting to be replaced during study.
Curators can replace the picture, but they are serious fans of art, and they will wait until these viewers pass and pass. So they are trying to do SELECT ... FOR UPDATE (or just UPDATE or DELETE ). At this time, they will acquire the “X” locks, having hung a small signature, indicating that “the exhibition will be redesigned”. Serious art fans want to see art presented properly, with beautiful lighting and some descriptive feast. They will wait for the redesign to be completed before they get close (they expect blocking if they try).
In addition, you were probably in a museum where more casual visitors roam, trying to ignore other people. They look at the pictures from the middle of the room, do not get too close. They can look at the same pictures that other viewers are looking at, and they can look behind the shoulders of serious art fans, look at these pictures, which are also visible. They can even brag about curators while they replace paintings (they don’t care if they look into a picture that is not yet mounted and well lit). Therefore, these random visitors do not block anyone, and no one blocks their viewing. They just do a SELECT , and they do not request any locks.
But there are builders who need to tear down walls and stuff, but they won’t work until someone in the building. They will wait until everyone leaves, and as soon as they begin their work, they will not let anyone in. That the presence of IS and IX badges blocks DDL (construction work) and vice versa.