mysql事务的疑问？？？

atomix

mysql 版本：
+------------------+
| version()        |
+------------------+
| 5.1.41-community |
+------------------+

表定义
[code=SQL]
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) DEFAULT NULL,
  `c` decimal(5,3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=latin1;

[/code]

测试一：
存储过程pp1:
[code=SQL]

CREATE DEFINER=`root`@`localhost` PROCEDURE `pp1`()
begin

DECLARE i varchar(200)  ;

SET TRANSACTION ISOLATION LEVEL  Serializable ;

start  transaction ;



select name into i  from t2 where id=3;



select * from t2 where id=3;

select sleep(10);

select * from t2 where id=3;

update t2 set name='pp1' where id=3;

select * from t2 where id=3;





commit ;



end;
[/code]

存储过程pp2:
[code=SQL]

CREATE DEFINER=`root`@`localhost` PROCEDURE `pp2`()
begin

SET TRANSACTION ISOLATION LEVEL Serializable ;

start  transaction ;





/*  select * from t2 where id=3   for update;  */

select * from t2 where id=3 ;

insert into t2 (name ) values('test');



/*update t2 set name='pp2' where id=3 ;*/

select * from t2 where id=3  ;

commit;

end;
[/code]


打开2个控制台 一个执行 call pp1;  另一执行call pp2;
先执行call pp1;再 pp1睡眠时间里 再另一个控制台 执行pp2;
这里定义事务隔离级别为Serializable 原理上pp2再pp1执行过程中是不可以插入的  ，但是结果往往令人失望，2个控制台都执行成功   我快崩溃了 。。。


继续换成事务隔离级别  REPEATABLE READ 测试mysql事务隔离效果


测试二：
存储过程pp1:
[code=SQL]

CREATE DEFINER=`root`@`localhost` PROCEDURE `pp1`()
begin

DECLARE i varchar(200)  ;

SET TRANSACTION ISOLATION LEVEL   REPEATABLE READ ;

start  transaction ;



select name into i  from t2 where id=3;



select * from t2 where id=3;

select sleep(10);

select * from t2 where id=3;

update t2 set name='pp1' where id=3;

select * from t2 where id=3;





commit ;



end;
[/code]

存储过程pp2:
[code=SQL]

CREATE DEFINER=`root`@`localhost` PROCEDURE `pp2`()
begin

SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ ;

start  transaction ;





/*  select * from t2 where id=3   for update;  */

select * from t2 where id=3 ;

/*insert into t2 (name ) values('test'); */



update t2 set name='pp2' where id=3 ;

select * from t2 where id=3  ;

commit;

end;
[/code]
测试方法和测试一类似 结果令人失望 mysql执行成功了

测试三：
在sqlserver2000下 修改上述代码 通过测试  REPEATABLE READ，Serializable 都测试成功
代码略


这是何故啊 ？？？真是搞不懂mysql了？？

atomix

这个问题很严重  你设置了 REPEATABLE READ  
mysql鸟都不鸟你 一股脑儿什么都执行了 ，如果这是个转账的程序 ，就悲剧了，混不下去了

atomix

求关注啊

horizonhyg

回复 1# atomix


    try

1. set global transaction isolation level

复制代码

atomix

回复楼上 还是一样没有效果

atomix

我将持续关注此问题 知道有解 ，希望有高手帮忙解决下啊是什么原因

atomix

高手呢 ？这么经典的问题难倒没人能解决呢？？

clyet

没看出有什么问题，pp2已经提交了，pp1自然也可以update

horizonhyg

回复 2# atomix


   

这里定义事务隔离级别为Serializable 原理上pp2再pp1执行过程中是不可以插入的

     innodb默认使用一致性非锁定读,在pp1执行时并没有对行加X锁,所以pp2是可以执行的.而且你在pp2中并没有对id=3的行进行修改操作,并不影响执行.
要注意innodb一般情况下是使用行锁的,你把pp1中的

1. select * from t2 where id=3

复制代码

改为

1. select * from t2 where id=3 for update;

复制代码

pp2中要对id=3的那一行进行更改操作,就会出现不执行的情况了.更多信息参见<innodb 存储引擎>6.2.2章

horizonhyg

刚才说的优点错误,对于一致性非锁定读,即使被读取的行已经被使用了select For update,也是可以读取的,只不过对于select for update 来说,他会读取的行加一个X锁(排他),任何其他事务想在这些行上加任何锁都会被阻塞.