Я не могу найти оптимальный способ использования транзакций в хранимой процедуре MySql. Я хочу ROLLBACK, если что-то не удается:
BEGIN
SET autocommit=0;
START TRANSACTION;
DELETE FROM customers;
INSERT INTO customers VALUES(100);
INSERT INTO customers VALUES('wrong type');
COMMIT;
END
1) Требуется ли autocommit=0?
2) Если второй INSERT ломается (и это, конечно), первый INSERT не откат. Процедура просто продолжается до COMMIT. Как я могу предотвратить это?
3) Я нашел, что могу DECLARE HANDLER, следует ли использовать эту инструкцию или есть более простой способ сказать, что если какая-либо команда выходит из строя, хранимая процедура должна ROLLBACK и сбой тоже?
DECLARE HANDLER отлично работает, но поскольку у меня есть версия MySql 5.1, я не могу использовать RESIGNAL. Поэтому, если возникает ошибка, вызывающий абонент не будет уведомлен:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
-- RESIGNAL; not in my version :(
END;
START TRANSACTION;
Ответ на 1: вам не нужно устанавливать autocommit = 0
С помощью START TRANSACTION автокоммит остается отключенным до тех пор, пока вы не закончите транзакция с COMMIT или ROLLBACK. Затем режим автокоманды возвращается к его предыдущему состоянию.
Разный подход к ответу 2: вы можете использовать логическую переменную, чтобы знать, должны ли вы COMMIT или ROLLBACK. Например:
BEGIN
DECLARE `should_rollback` BOOL DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `should_rollback` = TRUE;
START TRANSACTION;
DELETE FROM customers;
INSERT INTO customers VALUES(100);
INSERT INTO customers VALUES('wrong type');
IF `should_rollback` THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
Или, вы могли бы использовать свой очень полезный 3)