DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01453 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01453: SET TRANSACTION Must Be First Statement of Transaction

ORA-01453 is an Oracle error that occurs when the SET TRANSACTION statement is not the very first statement in a transaction. Oracle requires this command to be issued immediately after a COMMIT or ROLLBACK, before any DML or query operations begin. If any statement has already started an implicit or explicit transaction, calling SET TRANSACTION will immediately raise this error.

Top 3 Causes

1. Executing DML Before SET TRANSACTION

The most common cause is running an INSERT, UPDATE, or DELETE statement before attempting to set transaction properties.

-- WRONG: DML executed first, then SET TRANSACTION attempted
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

SET TRANSACTION READ ONLY; -- ORA-01453 raised here!
Enter fullscreen mode Exit fullscreen mode

Fix: Always place SET TRANSACTION before any DML.

-- CORRECT: Ensure clean state, then set transaction properties first
COMMIT;

SET TRANSACTION READ ONLY;

SELECT employee_id, salary
FROM employees
WHERE department_id = 20;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

2. Setting Isolation Level After Implicit Transaction Start

Oracle automatically begins a transaction the moment any DML executes — no explicit BEGIN is required. Developers unfamiliar with this behavior often attempt to set the isolation level after an implicit transaction has already started.

-- WRONG: Implicit transaction already started
INSERT INTO audit_log (log_date, action) VALUES (SYSDATE, 'START');

-- ORA-01453 raised here!
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Enter fullscreen mode Exit fullscreen mode

Fix: Set isolation level before any DML.

-- CORRECT
COMMIT; -- Ensure no pending transaction

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT INTO audit_log (log_date, action) VALUES (SYSDATE, 'START');
INSERT INTO orders (order_id, amount) VALUES (2001, 9900);

COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. Missing COMMIT/ROLLBACK in Connection Pool Environments

In connection pool environments, connections are reused across sessions. If a previous operation left an open transaction without a proper COMMIT or ROLLBACK, the next attempt to call SET TRANSACTION on that reused connection will fail.

-- Session A leaves an open transaction
UPDATE inventory SET qty = qty - 1 WHERE product_id = 101;
-- No COMMIT or ROLLBACK issued before returning connection to pool

-- Session B reuses the same connection
-- ORA-01453 raised here!
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Enter fullscreen mode Exit fullscreen mode

Fix: Always close transactions before returning connections to the pool.

-- Always execute before returning connection to pool
ROLLBACK; -- or COMMIT, depending on business logic

-- Now SET TRANSACTION works correctly
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE inventory SET qty = qty - 1 WHERE product_id = 101;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use DBMS_TRANSACTION package as a safer alternative inside PL/SQL:

BEGIN
  COMMIT; -- Ensure clean transaction state

  DBMS_TRANSACTION.READ_ONLY; -- Equivalent to SET TRANSACTION READ ONLY

  -- Perform read operations
  FOR rec IN (SELECT * FROM employees WHERE department_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.last_name || ': ' || rec.salary);
  END LOOP;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Establish coding standards that require COMMIT or ROLLBACK before every SET TRANSACTION call. Include this as a mandatory checkpoint in your code review checklist.
  • Use framework-level transaction management such as Spring @Transactional or JTA to enforce clear transaction boundaries automatically, reducing the risk of stale transactions in pooled connections.
  • Prefer DBMS_TRANSACTION over raw SET TRANSACTION in PL/SQL code for better control and readability.

Related Errors

Error Code Description
ORA-01456 DML attempted inside a READ ONLY transaction
ORA-01555 Snapshot too old — common with long-running READ ONLY transactions
ORA-02089 COMMIT not allowed in a subordinate session of a distributed transaction

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)