MySQL : 저장 프로시저 내의 트랜잭션

MySQL : 저장 프로시저 내의 트랜잭션

저장 프로시저의 기본 구조는

BEGIN

    .. Declare statements ..

    START TRANSACTION;

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

    COMMIT;

END

MySQL 버전: 5.1.61-0ubuntu0.11.10.1-log

현재 'query 2'가 실패하면 'query 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;

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

다음은 오류 발생 시 롤백하여 오류 코드를 반환하는 트랜잭션의 예입니다.

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 으로 설정되어 있는 것을 전제로 하고 있습니다.이게 도움이 됐으면 좋겠다.

[이것은 다른 답변에서는 다루지 않는 설명일 뿐]

적어도 MySQL의 최신 버전에서는 첫 번째 쿼리가 커밋되지 않습니다.

같은 세션에서 쿼리하면 변경이 표시되지만 다른 세션에서 쿼리하면 변경이 존재하지 않고 커밋되지 않습니다.

무슨 일이야?

트랜잭션을 열었을 때 트랜잭션 내부의 쿼리가 실패하면 트랜잭션은 열린 상태로 유지되며 변경 내용을 커밋하거나 롤백하지 않습니다.

따라서 다음과 같은 이전 쿼리로 잠긴 테이블/행은 모두 주의해야 합니다.SELECT ... FOR SHARE/UPDATE,UPDATE,INSERT또는 다른 모든 잠금 해제 세션이 종료(및 롤백 실행)되거나 후속 쿼리가 명시적으로 커밋될 때까지 잠긴 상태를 유지합니다(COMMIT또는 암묵적으로 부분 변경을 영속적으로 합니다(트랜잭션이 대기 상태일 때 몇 시간 후에 발생할 수 있습니다).

그렇기 때문에 이 솔루션에서는 핸들러를 즉시 선언해야 합니다.ROLLBACK에러가 발생했을 때.

---

추가의

핸들러 내부에서는, 다음의 방법으로 에러를 재기동할 수도 있습니다.RESIGNAL그렇지 않으면 저장 프로시저가 "성공적"으로 실행됩니다.

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            ROLLBACK;
            RESIGNAL;
        END;

    START TRANSACTION;
        #.. Query 1 ..
        #.. Query 2 ..
        #.. Query 3 ..
    COMMIT;
END

언급URL : https://stackoverflow.com/questions/9974325/mysql-transaction-within-a-stored-procedure