Question about innodb's lock mode

[dizhenxiong]

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

[B]

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!

[istvan.podor]

Dear dizhenxiong,

At first, please try avoid Huge Red LONG lines.

So at first,

[B]Quote:[/B]

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!

There are two kind of “Intention lock”, shared and exclusive (IS and IX).
It’s definitely not like myisam’s table lock. Each kind of intention lock lock only rows. however if you select a range, all rows will be locked

For the second question:

[B]Quote:[/B]

why X locks conflict with X locks ,but IX locks compatible with IX locks

Let me quote from the official documentation:
Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on the table.

In nutshell, X means LOCK, so you can’t lock the same resource two time, this is why X conflict with X, IX means that someone or something will lock that resources its more like an intention.

For the 3rd question:

[B]Quote:[/B]

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!

• Select for update use IX lock only.
• this two case you mentioned looks exactly the same to me:
  “select * from table where id=1 for update” won’t
  “select * from table where id=2 for update”
  and
  “select * from table where id=1 for update” will
  “select * from table where id=2 for update”
  So what case happened at you exactly?

Because in this case innodb shouldn’t block the execution of those queries.

For the last question

[B]Quote:[/B]

4.If isolation level is “Read committed” or “repeatable read”,when innodb will use S lock?

Isolation levels doesn’t affect locking those belongs to MVCC implementation.

I hope I answered all your questions )

Bests,
Istvan

[dizhenxiong]

Dear istvan.podor:

Thanks a million for your kind reply!

But i can’t quite agree with you at some point! As for select … for update,i think it set both x lock

and ix lock,so it can block something like update!