Announcement

Announcement Module
Collapse
No announcement yet.

Question about innodb's lock mode

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Question about innodb's lock mode

    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


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

    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!

  • #2
    Dear dizhenxiong,

    At first, please try avoid Huge Red LONG lines.

    So at first,

    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!



    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:
    Quote:


    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:
    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!



    - 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

    Quote:


    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

    Comment


    • #3
      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!

      Comment


      • #4
        Hi there,

        If you don't agree, that's OK. I'm afraid of those times when I'll be right all the time

        So I don't really agree with you neither:

        Quote:


        For example, SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock.



        That's what innodb documentation say. As it also say that IX conflict with X.

        So here is my test:

        mysql> show create table locktable\G
        *************************** 1. row ***************************
        Table: locktable
        Create Table: CREATE TABLE `locktable` (
        `id` int(11) NOT NULL default '0',
        `something` char(5) default NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1
        1 row in set (0.00 sec)

        The rows inside:
        mysql> insert into locktest values (1, 'a');
        ERROR 1146 (42S02): Table 'locktest.locktest' doesn't exist
        mysql> insert into locktable values (1, 'a');
        Query OK, 1 row affected (0.01 sec)
        mysql> insert into locktable values (2, 'b');
        Query OK, 1 row affected (0.00 sec)
        mysql> insert into locktable values (3, 'c');
        Query OK, 1 row affected (0.01 sec)

        Locks after the insert:
        mysql> pager grep lock
        PAGER set to 'grep lock'
        mysql> show engine innodb status\G
        Total number of lock structs in row lock hash table 0
        1 row in set (0.00 sec)

        Now let's start a transaction and lock for update:

        mysql> begin;
        Query OK, 0 rows affected (0.00 sec)

        mysql> select * from locktable where id = 2 FOR UPDATE;
        +----+-----------+
        | id | something |
        +----+-----------+
        | 2 | b |
        +----+-----------+
        1 row in set (0.01 sec)

        great.. works yet..

        Let's start another query from a different window:
        mysql> select * from locktable FOR UPDATE;

        And the locks:
        RECORD LOCKS space id 0 page no 51 n bits 72 index `PRIMARY` of table `locktest/locktable` trx id 0 1294 lock_mode X waiting

        So, it's set X and yes, it can block an update )

        Bests,
        Istvan

        Comment


        • #5
          hi!

          Thanks again for your reply!

          I agree with you this time for the test you done!but i think it just show

          select ... for update set x lock on rows !as for the ix is compatible with

          IS,can you tell me why!

          Comment

          Working...
          X