mysql事务的疑问???
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了??
+------------------+
| 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 发布时间: 2011-06-16
这个问题很严重 你设置了 REPEATABLE READ
mysql鸟都不鸟你 一股脑儿什么都执行了 ,如果这是个转账的程序 ,就悲剧了,混不下去了
mysql鸟都不鸟你 一股脑儿什么都执行了 ,如果这是个转账的程序 ,就悲剧了,混不下去了
作者: atomix 发布时间: 2011-06-16