lock for update in Innodb

[noicy seal]

Hi, all

I was blocked by one problem.

I have a table named user with Innodb, and the table has just one column like:

mysql> show create table user;
+-------+---------------------------------------------------
| Table | Create Table
+-------+---------------------------------------------------
| user | CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------
1 row in set (0.00 sec)

there are some data in the table:

mysql> select * from user;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
| 7 |
| 9 |
| 20 |
+----+
7 rows in set (0.00 sec)

Then I opened two sessions which are all under the default isolation level.

In the 1st session, I issued the command:

mysql> select * from user where id < 3 for update;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

I didn't commit in the 1st session. Then I opened the 2nd session, issued the command:

mysql> insert into user values(70);

then the 2nd session is blocked until I commit the 1st session.

My question is:
From the mysql reference, we know that:

"For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set"

there are exceptions about this criteria, but I don't think this situation fell into this exception. So I suppose that the rows which id < 3 are locked for update.

In the second session, we want to insert the id 70, which is far beyond the 3, according to the mysql reference:

"INSERT sets an exclusive lock on the inserted row. "

so I suppose the lock is put onto the row with id equals to 70.

In conclusion, the session 2 should not be blocked. but the result surprised me.

will anybody give me some clue or correct me about the things above?

ps. to avoid the affect of the primary index or clustered index, I created the second column in the same table and created one index on the second column. then repeated the steps to the second column, the result is the same.