mysql - 結果 - ストアドプロシージャ トランザクション 違い
MySQL:ストアドプロシージャ内でのトランザクション (3)
私のストアドプロシージャの基本的な構造は、
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'の結果がコミットされます。
- いずれかのクエリが失敗した場合、どのようにトランザクションをロールバックできますか?
MySQLストアドプロシージャのトランザクション
MySQLストアドプロシージャでROLLBACKを実行するには、ストアドプロシージャでexitハンドラを宣言する必要があります。 MySQLストアドプロシージャには、2種類のハンドラがあります。- sqlexception
- sqlwarning
sqlexceptionはクエリの実行中にエラーが発生したときに実行され、MySQLストアドプロシージャで警告が発生するとsqlwarningが実行されます。 これらのブロックをストアドプロシージャでどのように保持できるかを見てみましょう。
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
$$
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に設定されていることを前提としています。