after reading mysql’s manual about innodb lock mode,something puzzle me!
1.As it said, to “supports multiple granularity locking” Innodb supply “Intention lock”.
My question is — Does innodb 's “intention lock” is something like myisam’s table lock,that locks all table
or just lock individual rows!
2.As for the lock type compatibility matrix.
X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
As In Manual it said "A SELECT … FOR UPDATE reads the latest available data, setting exclusive locks on each row it
reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows. "
3.Does SELECT … FOR UPDATE just sets an IX lock,or set both IX lock and X lock!
It seems “select * from table where id=1 for update” wont’ block “select * from table where id=2 for update”,
but"select * from table where id=1 for update" will block “select * from table where id=2 for update”,
which make “select for update” behave like “row-level lock” not “table-lock” in my opinion!
4.If isolation level is “Read committed” or “repeatable read”,when innodb will use S lock?
Thanks in advance!