CREATE TABLE `actor` (
`actor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL DEFAULT '',
`password` varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY (`actor_id`),
KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=gbk;

insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');
insert into actor(name,password) values('zzz','1234567');
.....

mysql> select * from actor;
+----------+-------+----------+
| actor_id | name  | password |
+----------+-------+----------+
|        1 | cat01 | 1234567  |
|        2 | cat02 | 1234567  |
|        3 | ddddd | 1234567  |
|        4 | aaaaa | 1234567  |
|        5 | xfd   | 1234567  |
|        6 | cat02 | 1234567  |
|        7 | ddddd | 1234567  |
|        8 | aaaaa | 1234567  |
|        9 | xfd   | 1234567  |
|       10 | cat02 | 1234567  |
|       11 | ddddd | 1234567  |
|       12 | aaaaa | 1234567  |
|       13 | xfd   | 1234567  |
|       14 | zzz   | 1234567  |
+----------+-------+----------+
14 rows in set (0.00 sec)

session1:

SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id < 4
AND actor_id <> 1 FOR UPDATE;

然后会把session 2的如下sql

SET AUTOCOMMIT=0;
BEGIN;

SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;  --wait

SELECT actor_id FROM actor WHERE actor_id = 4 FOR UPDATE;  --wait

SELECT actor_id FROM actor WHERE actor_id = 5 FOR UPDATE;  good

SELECT actor_id FROM actor WHERE name  = 'aaaaa' FOR UPDATE;  --wait

SELECT actor_id FROM actor WHERE name  = 'zzz' FOR UPDATE;  --good

delete from  actor WHERE actor_id = 5;   --good

锁住的记录是 id = 1， 2， 3， 4

##############

另外一种情况比较极端：
当表里的数据是
mysql> select * from actor;
+----------+-------+----------+
| actor_id | name  | password |
+----------+-------+----------+
|        1 | cat01 | 1234567  |
|        2 | cat02 | 1234567  |
|        3 | ddddd | 1234567  |
|        4 | aaaaa | 1234567  |
|        5 | zzz   | 1234567  |
|        6 | zzz   | 1234567  |
+----------+-------+----------+

在session 1中执行
SET AUTOCOMMIT=0;
BEGIN;
SELECT * FROM actor WHERE actor_id < 4
AND actor_id <> 1 FOR UPDATE;

session 2中下面sql都会wait

SELECT actor_id FROM actor WHERE actor_id = 4 FOR UPDATE;

SELECT actor_id FROM actor WHERE actor_id = 5 FOR UPDATE;

SELECT actor_id FROM actor WHERE actor_id = 5 FOR UPDATE;

ID从1-6都被锁住了

Show innodb status\G

mysql> explain SELECT actor_id FROM actor WHERE actor_id < 4
-> AND actor_id <> 1 FOR UPDATE\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2
Extra: Using where; Using index
1 row in set (0.01 sec)

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index                --全索引扫描
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 6
Extra: Using where; Using index
1 row in set (0.00 sec)