Understanding TRANSACTIONS In Database

What Is A Transaction ?

Transaction is logically joining multiple SQL statements (unit of work) which we can either commit/rollback.In either case, the affected locks on resources are released.

ACID Property :-

DBMS is the management of data that should remain integrated when any changes are done in it. It is because if the integrity of the data is affected, whole data will get disturbed and corrupted. Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties. The ACID properties are meant for the transaction that goes through a different group of tasks, and there we come to see the role of the ACID properties.

Atomicity –
The entire transaction takes place at once or dosen’t happen at all.

Consistency –
The database must be consistent before & after the transaction.

Isolation –
Multiple transactions occur independently without interference.

Durability –
Once a transaction completes successfully and COMMIT has been issued,the changes get saved into hard disk. In case of any system failures,the changes will NOT be erased.

Isolation Level :-

Isolation determines how transaction integrity is visible to other users and systems. It means that a transaction should take place in a system in such a way that it is the only transaction that is accessing the resources in a database system.

Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. A transaction isolation level is defined by the following phenomena :-

Dirty read –

The meaning of this term is as bad as it sounds. You’re permitted to read uncommitted, or dirty , data. You can achieve this effect by just opening an OS file that someone else is writing and reading whatever data happens to be there. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored.

Fuzzy/Nonrepeatable read –

This simply means that if you read a row at time T1 and try to reread that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.

Phantom read –

This means that if you execute a query at time T1 and re-execute it at time T2, additional rows may have been added to the database, which may affect your results. This differs from a nonrepeatable read in that with a phantom read, data you already read hasn’t been changed, but instead, more data satisfies your query criteria than before.

Based on these phenomena, The SQL standard defines four isolation levels :::

Read Uncommitted –
Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.

Read Committed –
If you specify READ COMMITTED, a DML statement that attempts to modify a table already modified in an uncommitted transaction will wait until those row locks are released. This is the default.

Serializable –
When you specify SERIALIZABLE, a DML statement that attempts to modify a table already modified in an uncommitted transaction will fail.

Repeatable Read –
This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.

SET TRANSACTION :-

The SET TRANSACTION statement allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment or assign a name to the current transaction.

This statement must be the first SQL statement in a transaction,and it can appear only once.

SET TRANSACTION [ READ ONLY | READ WRITE ]
[ ISOLATION LEVEL [SERIALIZABLE | READ COMMITED ]
[ USE ROLLBACK SEGMENT ‘segment_name’ ]
[ NAME ‘transaction_name’ ];

This statement comes in the following four flavours :::

SET TRANSACTION READ ONLY –

This version makes the current transaction as read-only. In a read-only transaction,all subsequent queries see only those changes that were committed before the transaction began.This statement is useful when you are executing long-running, multiple query reports,and you want to make sure that the data used in the report is consistent.

Session 1 :::

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        777                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

SQL> UPDATE emp SET sal = 5 WHERE ename = 'SMITH';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80          5                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 2 :::

SQL> SET TRANSACTION READ ONLY NAME 'RO_example';

Transaction set.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80          5                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 1 :::

SQL> UPDATE emp SET sal = 10 WHERE ename = 'SMITH';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         10                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 2 :::

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80          5                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

SET TRANSACTION READ WRITE –

This version makes the current transaction as read-write and is the default setting.

Session 1 :::

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         10                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

SQL> UPDATE emp SET sal = 15 WHERE ename = 'SMITH';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         15                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 2 :::

SQL> SET TRANSACTION READ WRITE;

Transaction set.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         15                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 1 :::

SQL> UPDATE emp SET sal = 20 WHERE ename = 'SMITH';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         20                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 2 :::

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         20                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | READ COMMITTED –

This version defines how transactions that modify the database should be handled.

When you specify SERIALIZABLE, a DML statement that attempts to modify a table already modified in an uncommitted transaction will fail.

If you specify READ COMMITTED, a DML statement that attempts to modify a table already modified in an uncommitted transaction will wait until those row locks are released. This is the default.

Session 1 :::

SQL> UPDATE emp SET sal = 25 WHERE ename = 'SMITH';

1 row updated.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         25                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 2 :::

SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction set.

SQL> UPDATE emp SET sal = 30 WHERE ename = 'SMITH';
<<< SESSION HANGS>>>

Session 1 :::

SQL> COMMIT;

Commit complete.

Session 2 :::

SQL> UPDATE emp SET sal = 30 WHERE ename = 'SMITH';
UPDATE EMP SET SAL = 30 WHERE ENAME = 'SMITH'
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

Session 1 :::

SQL> UPDATE emp SET sal = 45 WHERE ename = 'SMITH';

1 row updated.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         45                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1617        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1275        500         30
      7566 JONES      MANAGER         7839 02-APR-81       5205                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      20013       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2865                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2487                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3026                    20
      7839 KING       PRESIDENT            17-NOV-81       5034                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1524          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20
      7900 JAMES      CLERK           7698 03-DEC-81      12000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3029                    20
      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

Session 2 :::

SQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Transaction set.

SQL> UPDATE emp SET sal = 50 WHERE ename = 'SMITH';
<<< SESSION HANGS>>>

Session 1 :::

SQL> COMMIT;

Commit complete.

Session 2 :::

SQL> UPDATE emp SET sal = 50 WHERE ename = 'SMITH';

1 row updated.

SET TRANSACTION USE ROLLBACK SEGMENT –

This version assigns the current transaction to the specified rollback segment and establishes the transaction as read-write.

This statement cannot be used with SET TRANSACTION READ ONLY.

A Rollback Segment is a database object containing before-image of data written to the database.

Rollback segments are used to :-

 Undo changes when a transaction is rolled back.
 Recover the database to a consistent state in case of failures.

SET TRANSACTION USE ROLLBACK SEGMENT rs1;

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “Understanding FLASHBACK Technology”

  1. Due to this time-to-SCN mapping, if you specify a time that is slightly after a DDL operation (such as a table creation) Oracle Database might use an SCN that is just before the DDL operation, causing error ORA-1466. package to generate statistics for all tables involved in a Oracle Flashback Query. Keep the statistics current. Oracle Flashback Query uses the cost-based optimizer, which relies on these statistics.

Leave a Reply

Your email address will not be published. Required fields are marked *