想问 是否一定要加上 start transaction;以下2个存储都能执行成功嗯
CREATE PROCEDURE `ba3`(id int)
begin
START TRANSACTION;------加上了
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end if;
end;
CREATE PROCEDURE `ba3`(id int)
begin
------------------没有加那句话
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end if;
end;
问下2者之间的差别 ??? (或者是否有差别)
CREATE PROCEDURE `ba3`(id int)
begin
START TRANSACTION;------加上了
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end if;
end;
CREATE PROCEDURE `ba3`(id int)
begin
------------------没有加那句话
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end if;
end;
问下2者之间的差别 ??? (或者是否有差别)
begin
------------------没有加那句话
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;----------------------------没有错误就提交了
else
rollback;
end if;
end; 所以一般没有差别 但是从严密上说 还是要加上 START TRANSACTION;
否则当外部
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)外部delete或者update---是ok
之后call你的ba3----是失败的那么他会把前面delete或者update全部回困
好难找到例子来证明。(我用
mysql> delete from ba2 where a='eee999';
Query OK, 0 rows affected (0.00 sec)mysql> select @@error_count;
+---------------+
| @@error_count |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
)实在找不到
@@error_count =1 的例子 。 楼上可以找个@@error_count =1 的例子吗??
update xxx set xx=xxx;
call bas() // 想办法触发rolback ,你可以检查 update xxx set xx=xxx;有没有被ROLLBACK
update xxx set xx=xxx;
call bas() // 想办法触发rolback ,你可以检查 update xxx set xx=xxx;有没有被ROLLBACK掉
rollback;
end;
begin
START TRANSACTION;------加上了
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end if;
end;
测试人 时间 2009-01-26 1。07pm第一window autocommit=0;mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| NULL | 1 |
| NULL | 2 |
| 1 | 102 |
| 455 | 103 |
+------+-----+
4 rows in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 1 | 1 |
| NULL | 2 |
| 1 | 102 |
| 455 | 103 |
+------+-----+-----必须提交之后 第2窗口看到
4 rows in set (0.00 sec)第2windowmysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 2 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
6 rows in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 2 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
6 rows in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 3 | -----------说明delete有效果 , 事务内部进行了会滚
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+-------------跟数据库的db是一样的
5 rows in set (0.00 sec)
第2个存储过程
CREATE PROCEDURE `ba3`(id int)
begin
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end if;
end;
测试人 时间 2009-01-26 1。17pm
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
5 rows in set (0.00 sec)mysql> delete from ba2 where a=3;
Query OK, 1 row affected (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
4 rows in set (0.00 sec)mysql> call ba3(102);
ERROR 1054 (42S22): Unknown column 'id3343' in 'where clause'
mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 1 | 1 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
4 rows in set (0.00 sec)第2个窗口
mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
5 rows in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
5 rows in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
5 rows in set (0.00 sec)mysql> select * from ba2;
+------+-----+
| b | a |
+------+-----+
| 0 | 1 |
| NULL | 3 |
| 0 | 102 |
| 0 | 103 |
| 455 | 104 |
+------+-----+
5 rows in set (0.00 ----一致都不能在第2窗口反应来自第1口的变化 甚至删除都没有影响 整体回滚