Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Question about innodb's lock mode

dizhenxiongdizhenxiong ContributorInactive User Role Beginner
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!

Comments

  • istvan.podoristvan.podor Contributor Inactive User Role Beginner
    Dear dizhenxiong,

    At first, please try avoid Huge Red LONG lines.

    So at first,

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

    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!

    </td></tr></table>

    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:
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

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

    </td></tr></table>

    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:
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

    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!

    </td></tr></table>

    - 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

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

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

    </td></tr></table>

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

    I hope I answered all your questions )

    Bests,
    Istvan
  • dizhenxiongdizhenxiong Contributor Inactive User Role Beginner
    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!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.