mysql行竞争_innodb行锁竞争为什么没有block?

本帖最后由 coolos 于 2012-5-25 16:45 编辑

这是一个innodb行锁的测试：

下面是环境

====================================================================================

mysql> show create table emp;

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                         |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+

| emp   | CREATE TABLE `emp` (

`id` int(11) NOT NULL DEFAULT '0',

`name` char(4) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> select * from emp;

+-----+------+

| id  | name |

+-----+------+

|  91 | abcd |

|  92 | abcd |

|  93 | abcd |

|  94 | abcd |

|  95 | abcd |

|  96 | abcd |

|  97 | abcd |

|  98 | abcd |

|  99 | abcd |

| 100 | abcd |

| 101 | abcd |

| 300 | 1234 |

| 400 | 5678 |

==============================================================================

下面是测试：

session A:

mysql>  set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp where id = 400 and name = "5678" for update;

+-----+------+

| id  | name |

+-----+------+

| 400 | 5678 |

+-----+------+

1 row in set (0.01 sec)

mysql> explain select * from emp where id = 400 and name = "5678" for update;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

|  1 | SIMPLE      | emp   | const | PRIMARY| PRIMARY | 4       | const |    1 |       |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.00 sec)

session B:

mysql>  set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp where id = 91 and name ="abcd" for update;

+----+------+

| id | name |

+----+------+

| 91 | abcd |

+----+------+

1 row in set (0.00 sec)

mysql> explain select * from emp where id = 91 and name ="abcd" for update;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

|  1 | SIMPLE      | emp   | const | PRIMARY| PRIMARY | 4       | const |    1 |       |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.01 sec)

innodb的行锁加于index之上,上面的两个sql都通过主键id访问不同的行，应该会因为竞争主键而block.可是这里为什么session A的 select ... for update 没有把session B的block住？