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;
