btw, evo ti primer
Code:
mysql> DROP TABLE IF EXISTS `d1`;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `d1` (
-> `id` int(11) NOT NULL,
-> `a` char(10) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `d1` (id, a) VALUES (100, 'bbb');
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS p ;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION
-> BEGIN
-> SELECT "ERROR";
-> ROLLBACK;
-> END;
-> SET autocommit=0;
->
-> START TRANSACTION;
-> DELETE FROM `d1`;
-> INSERT INTO `d1` (id, a) VALUES (1,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (2,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (3,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (4,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (5,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (6,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (7,'aaa');
->
-> INSERT INTO `d1` (id, a) VALUES (3,'aaa');
->
-> INSERT INTO `d1` (id, a) VALUES (8,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (9,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (10,'aaa');
-> COMMIT;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call p();
+-------+
| ERROR |
+-------+
| ERROR |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from d1;
+-----+------+
| id | a |
+-----+------+
| 100 | bbb |
+-----+------+
1 row in set (0.00 sec)
mysql>
dakle kao sto vidis hendler je zakinuo celu transakciju
naravno ako nemas gresku (ona trojka) onda to prodje ok:
Code:
mysql> DROP TABLE IF EXISTS `d1`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `d1` (
-> `id` int(11) NOT NULL,
-> `a` char(10) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `d1` (id, a) VALUES (100, 'bbb');
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS p ;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION
-> BEGIN
-> SELECT "ERROR";
-> ROLLBACK;
-> END;
-> SET autocommit=0;
->
-> START TRANSACTION;
-> DELETE FROM `d1`;
-> INSERT INTO `d1` (id, a) VALUES (1,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (2,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (3,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (4,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (5,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (6,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (7,'aaa');
->
-> -- INSERT INTO `d1` (id, a) VALUES (3,'aaa');
->
-> INSERT INTO `d1` (id, a) VALUES (8,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (9,'aaa');
-> INSERT INTO `d1` (id, a) VALUES (10,'aaa');
-> COMMIT;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call p();
Query OK, 0 rows affected (0.00 sec)
mysql> select * from d1;
+----+------+
| id | a |
+----+------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
| 5 | aaa |
| 6 | aaa |
| 7 | aaa |
| 8 | aaa |
| 9 | aaa |
| 10 | aaa |
+----+------+
10 rows in set (0.00 sec)
nadam se da je jasnije