Oracle DBA FAQ - Understanding SQL Transaction Management

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

What Are Transaction Isolation Levels Supported by Oracle?

Oracle supports two transaction isolation levels:

READ COMMITTED (the default option). If the transaction contains DML that requires row locks held by another transaction, then the DML statement waits until the row locks are released.
SERIALIZABLE. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, then the DML statement fails.

What Is a Data Lock?

A data lock is logical flag the Oracle server is placed on data objects to give an exclusive right to a transaction. Statements in other transactions needs to respect data locks based on certain rules. Rules on data locks are:

SELECT query statements do not create any data locks.
INSERT, UPDATE, and DELETE statements create data locks on the affected rows.
Data locks are released when the owner transaction ends.

How Data Locks Are Respected?

Here are the rules on how data locks are respected:

All statements ignore data locks owned its own transaction.
SELECT query statements ignores data locks owned by any transactions.
INSERT, UPDATE, and DELETE statements in a READ COMMITTED transaction will wait for data locks on their targeted rows by other transactions to be released.
INSERT, UPDATE, and DELETE statements in a SERIALIZABLE transaction will fail if their targeted rows has data locks owned by other transactions.

How To Experiment a Data Lock?

If you want to have some experience with data locks, you can create two windows runing two SQL*Plus sessions. In session 1, you can run a UPDATE statements to create a data lock. Before committing session 2, switch to session 2, and run a UPDATE statements on the same row as session 1. The UPDATE statement will be put into wait status because of the data lock. Follow the tutorial exercise below to experience yourself:

(session 1)
SQL> connect HR/fyicenter

SQL> SET TRANSACTION 
  ISOLATION LEVEL 
  READ COMMITTED;
Transaction set.

SQL> SELECT * FROM fyi_links;
 ID URL              NOTES   
--- ---------------- --------
101 FYICENTER.COM            
110 CENTERFYI.COM            

SQL> UPDATE fyi_links 
  SET url='fyicenter.com' 
  WHERE id=101;
1 row updated.
(lock created on row id=101)

                                   (session 2)
                                   SQL> connect HR/fyicenter
                                   
                                   SQL> SET TRANSACTION 
                                     ISOLATION LEVEL 
                                     READ COMMITTED;
                                   Transaction set.

                                   SQL> UPDATE fyi_links SET
                                     notes='FAQ Resource' 
                                     WHERE id=101;
                                   (wait on lock at id=101)

SQL> COMMIT;
(lock on row id=101 released)
                                   (ready to run UPDATE)
                                   1 row updated.

SQL> SELECT * FROM fyi_links;
 ID URL              NOTES
--- ---------------- --------
101 fyicenter.com
110 CENTERFYI.COM

                                   SQL> COMMIT;

SQL> SELECT * FROM fyi_links;
 ID URL              NOTES
--- ---------------- ------------
101 fyicenter.com    FAQ Resource
110 CENTERFYI.COM

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...