mysql 自治事务

DELIMITER //

CREATE PROCEDURE autonomous_transaction_example()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    -- 执行一些操作
    INSERT INTO log_table (message) VALUES ('Operation started');
    -- 模拟错误
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Simulated error';
    INSERT INTO log_table (message) VALUES ('Operation completed');
    COMMIT;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE autonomous_transaction_example()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    -- 使用锁机制
    LOCK TABLES log_table WRITE;
    INSERT INTO log_table (message) VALUES ('Operation started');
    UNLOCK TABLES;
    COMMIT;
END //

DELIMITER ;