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

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!

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

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!