PL-SQL Transactions

PL-SQL Transactions – Learn how to preserve data integrity with COMMIT, ROLLBACK , SAVEPOINT statements in transaction management. Also Learn in which situations COMMIT, ROLLBACK , SAVEPOINT statements will be used with examples.

For better understanding of transaction related example programs, please use the following table as reference.

CREATE TABLE EMP10
  (
    employee_id NUMBER UNIQUE,
    FIRST_NAME  VARCHAR2(100),
    LAST_NAME   VARCHAR2(100),
    EMAIL       VARCHAR2(100) UNIQUE
  );

here is the Sample data insertion queries for above table.

INSERT
INTO EMP10
  (
    employee_id,
    FIRST_NAME,
    LAST_NAME,
    EMAIL
  )
  VALUES
  (
    '9' ,
    'Raj',
    'Kumar',
    '[email protected]'
  );

INSERT
INTO EMP10
  (
    employee_id,
    FIRST_NAME,
    LAST_NAME,
    EMAIL
  )
  VALUES
  (
    10,
    'Bharath',
    'Milkuri',
    '[email protected]'
  );

INSERT
INTO EMP10
  (
    employee_id,
    FIRST_NAME,
    LAST_NAME,
    EMAIL
  )
  VALUES
  (
    11,
    'RANA',
    'PRATHAP',
    '[email protected]'
  );

Transaction- is a set of logically related read or write operations(dml operations) used to perform a task or a unit of work.

Read Operation relates to SELECT queries in SQL.
Write Operation relates to INSERT, UPDATE queries in SQL.
To preserve data integrity , transaction should satisfy the ACID properties.

ACID Properties

ATOMICITY – Transaction should execute all the operations with commit or roll back statements.(meaning a transaction has either commit or roll back all the transactions)
CONSISTENCY – DB should be consistent before and after execution of the transaction.
ISOLATION– Concurrent execution of 2 or more transactions should be equal to serial transactions (transaction done one after another).
DURABILITY– Transaction should be recoverable under any case of failure.

PL/SQL provides the following statements for transaction management.

COMMIT— makes current changes to be permanent save to database and visible to all other users.
ROLLBACK— undo all the changes that made during current transaction.
SAVEPOINT — using save points we can do partial roll backs (i.e, savepoints let you rollback part of a transaction instead of the whole transaction.)
splits the long transactions into smaller units by setting some check points. By setting savepoints within a long transaction, you can roll back to a checkpoint if required.
ROLLBACK TO SAVEPOINT-This statement rolls back all the changes up to the point, where you had marked savepoint.

Example Programs on Transaction Management

Example 1

Assume that a user ‘A’ trying to insert new employee details , as

INSERT
INTO EMP10
  (
    employee_id,
    FIRST_NAME,
    LAST_NAME,
    EMAIL
  )
  VALUES
  (
    1,
    'ROHITH',
    'NARA',
    '[email protected]'
  );

in the same way another user ‘B’ trying to access the details of all the employees.

SELECT * FROM EMP10;

Now the user ‘B’ not able to see the details of Employeed_id 1 which was inserted by user ‘A’ , Because user A inserted the details in the table but not committed the transaction.

Notes
If a transaction is not committed, then other user in different session not able to view the unsaved data.

To view the data to user B, user A has to commit the transaction.

Example 2

INSERT
INTO EMP10
  (
    employee_id,
    FIRST_NAME,
    LAST_NAME,
    EMAIL
  )
  VALUES
  (
    1,
    'ROHITH',
    'NARA',
    '[email protected]'
  );
COMMIT;

Using above commit statement, now other users able to access the committed data.
If we don’t commit the transaction , the transaction is in pending state(which is not complete) and locks will be applied for that transaction.
To release the locks on current transaction, one should use either commit to save the change in DB or roll back to undo the changes in DB.

Example 3

BEGIN
  INSERT
  INTO EMP10
    (
      employee_id,
      FIRST_NAME,
      LAST_NAME,
      EMAIL
    )
    VALUES
    (
      1,
      'ROHITH',
      'NARA',
      '[email protected]'
    );
  COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
  ROLLBACK;
END;

In the above program, we are trying to insert a duplicate employee_id which was there in the EMP10 table.
If we try to execute the above block , we will get DUP_VAL_ON_INDEX exception. Now the exception block perform rollback operations , that undo all the changes that was performed.

Example 4

BEGIN
  INSERT
  INTO EMP10
    (
      employee_id,
      FIRST_NAME,
      LAST_NAME,
      EMAIL
    )
    VALUES
    (
      12,
      'RAMU',
      'ANNANGI',
      '[email protected]'
    );
SAVEPOINT S;

UPDATE EMP10
SET FIRST_NAME ='swetha',
last_name ='mentham'
WHERE employee_id = 9;

UPDATE EMP10 SET employee_id=10 WHERE employee_id=1;
COMMIT;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO S;
END;

In the above program we are trying to do partial roll backs after save point.
If we try to execute the above block, insert statement execution will be committed because of save point. After savepoint if we get any exception, transaction will be rolled back upto save point.

PL-SQL Transactions