innodb不同事务隔离级别下行锁的释放

innodb使用的是行锁,原来一直以为InnoDB不是只在最终符合where条件的行上加锁,而是在被扫描过的所有行上加锁.见http://kabike.iteye.com/blog/1698478

最近看 <<high performance mysql>>,里面提到

引用

InnoDB locks rows only when it accesses them, and an index can reduce the number
of rows InnoDB accesses and therefore locks. However, this works only if InnoDB can
filter out the undesired rows at the storage engine level. If the index doesn’t permit
InnoDB to do that, the MySQL server will have to apply a WHERE clause after InnoDB
retrieves the rows and returns them to the server level. At this point, it’s too late to
avoid locking the rows: InnoDB will already have locked them, and they will remain
locked for some period of time. In MySQL 5.1 and newer, InnoDB can unlock rows
after the server filters them out; in older versions of MySQL, InnoDB doesn’t unlock
the rows until the transaction commits

按这里的说法,innodb可以实现只在最终匹配的row上加锁,而不是扫描的所有行上都有锁.

仔细看了下mysql文档,发现mysql5.1自己官方文档也有好多不清楚的地方,
比如它在14.6.7.6. Locks Set by Different SQL Statements in InnoDB
里说

引用

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 (for example, if they do not meet the criteria given in the WHERE clause).

但是这种情况的发生是有条件的,比如其中一个条件就是事务隔离级别是READ COMMITTED
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed里说

引用

In MySQL 5.1, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.

而且行锁的释放不仅仅局限于blocking read(SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE),还包括了update和delete语句

做个试验验证一下

CREATE TABLE `20130312t1` (
  `id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from 20130312t1

1 1 2 1
2 1 1 2
3 2 1 3

update 20130312t1 set c=2 where a=1 and c=1;
	update 20130312t1 set c=2 where b=1 and c=3;
	[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
delete from  20130312t1  where a=1 and c=1;
	delete from  20130312t1 where b=1 and c=3;
	[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
select * from 20130312t1  where a=1 and c=1 for UPDATE;
	select * from  20130312t1 where b=1 and c=3 for UPDATE;
	[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

上面的第二个事务被block住,因为innodb没有把"record locks for nonmatching rows"释放
两个session中分别执行
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
就没有block了,不过如果分别执行

select * from 20130312t1  where a=1 and c=1 for UPDATE;
select * from 20130312t1  where a=1 and c=2 for UPDATE;

还是会block,我想因为它只是release了"record locks for nonmatching rows",而没有释放index上的锁,
这样猜测innodb在index和row上都是有锁的么?

欢迎大家讨论.