MySQL: транзакция в хранимой процедуре

Основная структура моей хранимой процедуры:

BEGIN

    .. Declare statements ..

    START TRANSACTION;

        .. Query 1 ..
        .. Query 2 ..
        .. Query 3 ..

    COMMIT;

END

Версия MySQL: 5.1.61-0ubuntu0.11.10.1-log

В настоящее время, если "запрос 2" терпит неудачу, выполняется результат запроса 1.

Как я могу отменить транзакцию, если какой-либо запрос завершился неудачей?

Ответы

Ответ 1

Взгляните на http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

В основном вы объявляете обработчик ошибок, который вызывается откатом

START TRANSACTION;

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        EXIT PROCEDURE;
    END;
COMMIT;

Ответ 2

Просто альтернатива коду rkosegi,

BEGIN

    .. Declare statements ..

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
          .. set any flags etc  eg. SET @flag = 0; ..
          ROLLBACK;
    END;

    START TRANSACTION;

        .. Query 1 ..
        .. Query 2 ..
        .. Query 3 ..

    COMMIT;
    .. eg. SET @flag = 1; ..

END

Ответ 3

Транзакция в хранимой процедуре MySQL

Чтобы выполнить ROLLBACK в хранимой процедуре MySQL, нам нужно объявить обработчик exit в хранимой процедуре. В хранимой процедуре MySQL есть два типа обработчиков.

SQLException
sqlwarning

sqlexception будет выполнять при возникновении ошибки во время выполнения запроса, а sqlwarning будет выполнять, когда в MySQL хранится процедура предупреждения. Давайте посмотрим, как мы можем иметь этот блок в Хранимой процедуре.

DELIMITER $$

CREATE PROCEDURE `transaction_sp` ()

BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;
  INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com');
  UPDATE second_table set name="xyz" where id=4;
COMMIT;
END
$$

Ответ 4

Здесь приведен пример транзакции, которая будет откатываться при ошибке и возвращать код ошибки.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CREATE_SERVER_USER`(
    IN P_server_id VARCHAR(100),
    IN P_db_user_pw_creds VARCHAR(32),
    IN p_premium_status_name VARCHAR(100),
    IN P_premium_status_limit INT,
    IN P_user_tag VARCHAR(255),
    IN P_first_name VARCHAR(50),
    IN P_last_name VARCHAR(50)
)
BEGIN

    DECLARE errno INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
    SELECT errno AS MYSQL_ERROR;
    ROLLBACK;
    END;

    START TRANSACTION;

    INSERT INTO server_users(server_id, db_user_pw_creds, premium_status_name, premium_status_limit)
    VALUES(P_server_id, P_db_user_pw_creds, P_premium_status_name, P_premium_status_limit);

    INSERT INTO client_users(user_id, server_id, user_tag, first_name, last_name, lat, lng)
    VALUES(P_server_id, P_server_id, P_user_tag, P_first_name, P_last_name, 0, 0);

    COMMIT WORK;

END$$
DELIMITER ;

Предполагается, что для автообмена установлено значение 0. Надеюсь, это поможет.

Ответ 5

несколько раз процедура, которую я написал выше, не работает. Поэтому я изменяю структуру, как показано ниже.

begin
    declare exit handler for SQLEXCEPTION
    begin
        ROLLBACK;
        select 'An unpexpected error sprunged in your transaction.try again!' as 'Error';
    end;
    start transaction;
        insert into transact values(1,t_type,amount,tTime,accNo);
        insert into rate values(name,age,years);
        insert into tax values(ols,timed,sss);
    commit;
end;

оператор declare exit for SQLEXCEPTION, я написал в начале, в конце блока. Впервые я использовал его без начального, конечного блока. Но это делает ошибку для моей процедуры.