Oracle DBA FAQ - Understanding SQL Transaction Management

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

A collection of 22 FAQs on Oracle SQL SELECT query statements. Clear answers are provided with tutorial exercises on starting and ending transactions, committing and rolling back transactions, transaction/statement-level read consistency, read committed isolation level, locks and dead locks. Topics included in this FAQ are:

What Is a Transaction?
How To Start a New Transaction?
How To End the Current Transaction?
How To Create a Testing Table?
How To Commit the Current Transaction?
How To Rollback the Current Transaction?
What Happens to the Current Transaction If a DDL Statement Is Executed?
What Happens to the Current Transaction If the Session Is Ended?
What Happens to the Current Transaction If the Session Is Killed?
How Does Oracle Handle Read Consistency?
What Is a READ WRITE Transaction?
What Is a READ ONLY Transaction?
How To Set a Transaction To Be READ ONLY?
What Are the Restrictions in a READ ONLY Transaction?
What Are the General Rules on Data Consistency?
What Are Transaction Isolation Levels Supported by Oracle?
What Is a Data Lock?
How Data Locks Are Respected?
How To Experiment a Data Lock?
How To View Existing Locks on the Database?
What Is a Dead Lock?
How Oracle Handles Dead Locks?

Sample scripts used in this FAQ assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server.

Some sample scripts may require database tables created by other samples in the beginning of the collection.

What Is a Transaction?

A transaction is a logical unit of work requested by a user to be applied to the database objects. Oracle server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

How To Start a New Transaction?

There is no SQL statement to explicitly start a new transaction. Oracle server implicitly starts a new transaction with the following two conditions:

The first executable statement of a new user session will automatically start a new transaction.
The first executable statement after a previous transaction has been ended will automatically start a new transaction.

How To End the Current Transaction?

There are several ways the current transaction can be ended:

Running the COMMIT statement will explicitly end the current transaction.
Running the ROLLBACK statement will explicitly end the current transaction.
Running any DDL statement will implicitly end the current transaction.
Disconnecting a user session will implicitly end the current transaction.
Killing a user session will implicitly end the current transaction.

How To Create a Testing Table?

If you want to practice DML statements, you should create a testing table as shown in the script below:

>cd (OracleXE home directory)
>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE fyi_links (id NUMBER(4) PRIMARY KEY,
  url VARCHAR2(16) NOT NULL,
  notes VARCHAR2(16),
  counts NUMBER(4),
  created DATE DEFAULT (sysdate));
Table created.

You should keep this table for to practice other tutorial exercises presented in this collection.

How To Commit the Current Transaction?

If you have used some DML statements updated some data objects, and you want to have the updates to be permanently recorded in the database, you can use the COMMIT statement. It will make all the database changes made in the current transaction become permanent and end the current transaction. The following tutorial exercise shows you how to use COMMIT statements:

SQL> connect HR/fyicenter

SQL> INSERT INTO fyi_links (url, id) 
  2  VALUES ('fyicenter.com', 101);

SQL> INSERT INTO fyi_links (url, id) 
  2  VALUES ('centerfyi.com', 110);

SQL> SELECT * FROM fyi_links;
     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 fyicenter.com                          07-MAY-06
    110 centerfyi.com                          07-MAY-06

SQL> COMMIT;
Commit complete.

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...