쇼핑몰 운영에서 사용되는 아래와 같은 프로시저가 있습니다.

이프로시저가 START TRANSACTION에서 멈출때도 있고 잘될때도 있습니다... 멈추는 현상이 나고 START TRANSACITON 과 COMMIT 라인을 지우고 다시 실행하면 정상적으로 돕니다. 그리고 정상적으로 실행이되고 다시 START TRANSACTION 과 COMMIT 라인을 다시 삽입해도 정상적으로 잘 됩니다...

그러다 한동안 잘되다가 또 그부분에서 멈추는 현상이 납니다.. 도움 부탁드립니다ㅜㅜ

CREATE DEFINER=`admin`@`%` PROCEDURE `PROC_POINTMALL_POINT_PAY`(
IN usercode int,
IN martcode int,
IN paypoint int,
IN payRsn varchar(1000),
IN payorder varchar(200))
proc_pointpay : BEGIN


DECLARE pointcode int DEFAULT 104;
DECLARE countRet int DEFAULT 0;
    DECLARE payYYYYMM int DEFAULT 0;
    DECLARE payDtm datetime;
    DECLARE maxRecordSum int;
    DECLARE absPayPoint int;
    DECLARE leftPoint int;
    DECLARE exit handler for SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text); SELECT @full_error; 
ROLLBACK;    
        INSERT INTO err_log VALUES(null, @full_error , now());
        SELECT -1 AS result, 'SQLEXCEPTION' AS msg;
END;
DECLARE exit handler for SQLWARNING
BEGIN
ROLLBACK;
        SELECT -1 AS result, 'SQLWARNING' AS msg;
        INSERT err_log values(null, 'SQLWARNING' + msg , now());
END;
    INSERT err_log values(null, 0 , now());
    -- 0. 탈퇴여부 확인
    SELECT COUNT(*) INTO countRet FROM USER WHERE USER_SEQNO = usercode;

    IF countRet = 0 THEN
        SELECT -1 AS result, '탈퇴한 회원입니다.' AS msg;
LEAVE proc_pointpay;
    END IF;
    INSERT err_log values(null, 1 , now());
-- 3. 사용 포인트를 차감한다.
SET countRet = 0;
SET payDtm = NOW();
SET payYYYYMM = YEAR(payDtm) * 100 + MONTH(payDtm);    
        INSERT err_log values(null, 2 , now());
    -- 4. 입력 시작: POINT_USER 테이블에 결제기록 입력
INSERT err_log values(null, paypoint , now());


    SET absPayPoint = ABS(paypoint);
START TRANSACTION;
         INSERT err_log values(null, 3 , now());
CREATE TEMPORARY TABLE IF NOT EXISTS TEMP AS (SELECT
C.POINT_MNGMT_SEQNO
, C.EXTNL_SEQNO
, C.LEFT_POINT
, C.CUMULATIVE_SUM
FROM
(SELECT 
A.POINT_MNGMT_SEQNO
, A.EXTNL_SEQNO 
, A.LEFT_POINT
, IFNULL((SELECT SUM(B.LEFT_POINT) FROM POINT_MNGR AS B WHERE B.EXTNT_DATE <= A.EXTNT_DATE AND B.LEFT_POINT > 0 AND B.USER_SEQNO = usercode ORDER BY B.EXTNT_DATE),0) AS CUMULATIVE_SUM
FROM 
POINT_MNGR AS A 
WHERE 
A.LEFT_POINT > 0 
   AND A.USER_SEQNO =  usercode 
ORDER BY A.EXTNT_DATE
) AS C
WHERE 
C.CUMULATIVE_SUM < absPayPoint
ORDER BY C.CUMULATIVE_SUM DESC);

    INSERT err_log values(null, 4 , now());
    SET maxRecordSum = IFNULL((SELECT MAX(CUMULATIVE_SUM) FROM TEMP), 0);
    SET leftPoint = absPayPoint - maxRecordSum;
    INSERT err_log values(null, absPayPoint , now());
    INSERT err_log values(null, maxRecordSum , now());
    INSERT err_log values(null, leftPoint , now());
IF maxRecordSum > 0 THEN
BEGIN
 /*0 값 처리*/
UPDATE
POINT_MNGR AS PMA
, (
SELECT
POINT_MNGMT_SEQNO
                FROM
TEMP
) AS PMB
SET
LEFT_POINT = 0
WHERE
PMA.POINT_MNGMT_SEQNO
IN
(
PMB.POINT_MNGMT_SEQNO 
);
END;
END IF;
        INSERT err_log values(null, 5 , now());
    /*남은 짜투리 값 처리*/
UPDATE
POINT_MNGR
SET
LEFT_POINT = LEFT_POINT - leftPoint
WHERE
LEFT_POINT > 0
AND USER_SEQNO = usercode
ORDER BY EXTNT_DATE
LIMIT 1;
        INSERT err_log values(null, 6 , now());
    /*유저정보 업데이트*/
UPDATE USER SET
SVCBL_POINT = (IFNULL(SVCBL_POINT,0) + paypoint) 
WHERE
USER_SEQNO = usercode;
SELECT ROW_COUNT() INTO countRet;

IF countRet = 0 THEN
ROLLBACK;
        SELECT -1 AS result, '사용자 포인트 기록 업데이트에 실패했습니다. 롤백되었습니다.' AS msg;
LEAVE proc_pointpay;
END IF;
        INSERT err_log values(null, 7 , now());
     /*POINT_USE 기록*/
INSERT INTO 
POINT_USE 
(POINT_USE_SEQNO , POINT_MNGMT_SEQNO, EXTNL_SEQNO,USE_DIV_CODE, USE_POINT, RGST_DTM)
SELECT 
null AS POINT_USE_SEQNO, POINT_MNGMT_SEQNO, payorder,'2', LEFT_POINT AS USE_POINT, NOW()
FROM TEMP
UNION ALL
(SELECT 
null AS POINT_USE_SEQNO, POINT_MNGMT_SEQNO, payorder,'2', leftPoint AS USE_POINT, NOW()
FROM 
POINT_MNGR 
WHERE 
LEFT_POINT > 0 AND USER_SEQNO = usercode ORDER BY EXTNT_DATE LIMIT 1);

SELECT ROW_COUNT() INTO countRet;
IF countRet = 0 THEN
ROLLBACK;
        SELECT -1 AS result, '결제기록 입력에 실패했습니다. 롤백되었습니다.' AS msg;
LEAVE proc_pointpay;
END IF;
   INSERT err_log values(null, 8 , now());
    /*HISTORY 업데이트*/
        INSERT INTO 
POINT_USE_HSTRY
(
POINT_USE_HSTRY_SEQNO
, POINT_SEQNO
, USER_SEQNO
, POINT
, HSTRY
, USE_DIV_CODE
, RGST_DTM
, EXTNT_DATE
)
values
(
null
, pointcode
, usercode
, paypoint
, payRsn
, '1'
, NOW()
, null
);
SELECT ROW_COUNT() INTO countRet;
IF countRet = 0 THEN
ROLLBACK;
        SELECT -1 AS result, '결제기록 입력에 실패했습니다. 롤백되었습니다.' AS msg;
LEAVE proc_pointpay;
END IF;
-- 7. 커밋
    COMMIT;
    SELECT 1 AS result, '포인트결제에 성공했습니다.' AS msg;
END