PRAGMA AUTONOMOUS_TRANSACTION in ORACLE PLSQL - Use case

In the airline industry's cargo management system, autonomous transactions can play a critical role in ensuring the integrity and consistency of operations without affecting the main transactional flow. A cargo management system typically handles processes like booking, tracking, updating cargo status, and managing inventory across various stages of transport. During these operations, there are numerous auxiliary tasks, such as logging, auditing, or updating external systems, which should occur independently of the main transaction that handles cargo updates.

Scenario: Using PRAGMA AUTONOMOUS_TRANSACTION in Cargo Management System

Use Case: Tracking Cargo and Logging Events

Let’s consider a scenario where a cargo shipment is being booked, updated, or processed within the system. In addition to updating the cargo status in the main system (e.g., confirming that the cargo has been loaded onto a flight), we also want to maintain an audit log of every status change. This logging should not interfere with the main process, such as the cargo booking or status updates.

Process:

1. Main Transaction (Booking or Status Update):

A cargo shipment is booked, or its status is updated (e.g., "cargo loaded onto flight").

The main transaction involves updating the cargo’s status in the database.

1. Autonomous Transaction (Logging Events):

Simultaneously, the system needs to log this change in a cargo_events_log table to keep track of every step in the process for auditing or troubleshooting purposes.

The key point is that the logging should not interfere with the main cargo update, and even if an error occurs during the logging process (like a database constraint violation), it should not affect the successful update of the cargo status.

PL/SQL Example Using PRAGMA AUTONOMOUS_TRANSACTION:

-- Create a table for logging cargo events
CREATE TABLE cargo_events_log (
log_id NUMBER PRIMARY KEY,
cargo_id NUMBER,
event_description VARCHAR2(255),
event_timestamp DATE,
operator_id NUMBER
);

-- Main procedure to update cargo status and log the event
CREATE OR REPLACE PROCEDURE update_cargo_status (
p_cargo_id IN NUMBER,
p_new_status IN VARCHAR2,
p_operator_id IN NUMBER) IS

-- Variable to store the old status of the cargo
v_old_status VARCHAR2(255);

-- Autonomous transaction procedure to log cargo event
PROCEDURE log_cargo_event(p_cargo_id IN NUMBER, p_event_description IN VARCHAR2, p_operator_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;  -- Marks this procedure as an autonomous transaction
BEGIN
    -- Insert event description into the cargo_events_log table
    INSERT INTO cargo_events_log (log_id, cargo_id, event_description, event_timestamp, operator_id)
    VALUES (cargo_events_log_seq.NEXTVAL, p_cargo_id, p_event_description, SYSDATE, p_operator_id);

    -- Commit the autonomous transaction to make the log permanent
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- Handle any errors during the logging (e.g., database issue in the log insertion)
        DBMS_OUTPUT.PUT_LINE('Error logging cargo event: ' || SQLERRM);
END log_cargo_event;

BEGIN
-- Fetch the old status of the cargo
SELECT status INTO v_old_status
FROM cargo_shipments
WHERE cargo_id = p_cargo_id FOR UPDATE;

-- Update the cargo status in the main system
UPDATE cargo_shipments
SET status = p_new_status
WHERE cargo_id = p_cargo_id;

-- Commit the main transaction (cargo status update)
COMMIT;

-- Log the cargo event asynchronously using an autonomous transaction
log_cargo_event(p_cargo_id, 'Status updated from ' || v_old_status || ' to ' || p_new_status, p_operator_id);

EXCEPTION
WHEN OTHERS THEN
-- Handle errors in the main transaction (e.g., status update failure)
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error updating cargo status: ' || SQLERRM);
END update_cargo_status;

Explanation:

1. Main Transaction:

Fetching Cargo Status: The old status of the cargo is fetched using a SELECT statement (locking the record to ensure it’s up-to-date for the status change).

Updating Cargo Status: The cargo’s status is then updated with the new status (e.g., "Loaded on flight").

Committing the Main Transaction: After successfully updating the status, the main transaction is committed.

1. Autonomous Transaction (Logging the Event):

Logging the Event: The log_cargo_event procedure is called, which inserts a log entry into the cargo_events_log table, recording the event (e.g., "Status updated from Pending to Loaded on flight").

Autonomous Commit: Since this procedure is marked with PRAGMA AUTONOMOUS_TRANSACTION, it operates independently of the main transaction. Even if the main status update fails or is rolled back, the log entry will still be committed and preserved.

1. Error Handling:

If there is an error during the logging process (e.g., a constraint violation or database issue in the log insertion), the OTHERS exception handler ensures that the error is logged using DBMS_OUTPUT, but it does not affect the main transaction.

If the main transaction encounters an issue, the ROLLBACK ensures the cargo status update is reversed, but the autonomous log entry will still exist.

Why Use PRAGMA AUTONOMOUS_TRANSACTION in This Case:

Separation of Concerns: The main transaction focuses on updating the cargo status, while the autonomous transaction is responsible for logging the event. The two operations are independent, ensuring that logging does not interfere with the critical task of updating the cargo status.

Transaction Integrity: The logging action should not block or cause issues with the main cargo update. For example, if there is an error while logging (due to a database issue or constraint violation), the cargo status update should still be successfully committed.

Audit & Compliance: In industries like the airline industry, logging cargo events for audit and compliance is critical. Using an autonomous transaction ensures that even if the main update fails, the log of the event is still stored, which is essential for tracking cargo movements.

Asynchronous Nature: Logging is typically an ancillary operation and does not need to be in sync with the main transaction’s lifecycle, making it a good candidate for autonomous transactions.

Use Case Summary:

In this cargo management system, PRAGMA AUTONOMOUS_TRANSACTION ensures that the logging process (e.g., tracking cargo status changes) occurs independently of the main operations like cargo status updates. This design pattern guarantees that logging continues to function even if the main process encounters issues, allowing for accurate audit trails and minimizing disruption to the core functionality of the system.