Oracle Database

If a DDL statement is executed, the current transaction will be committed and ended. All the database changes made in the current transaction will become permanent. This is called an implicit commit by a DDL statement. The following tutorial exercise shows you that the CREATE TABLE statement forced the current transaction to be committed and ended. The subsequent ROLLBACK statement has no effects on the closed transaction.

SQL> connect HR/globalguideline

SQL> INSERT INTO ggl_links (url, id)

2 VALUES ('oracle.com', 112);

SQL> INSERT INTO ggl_links (url, id)

2 VALUES ('sql.com', 113);

SQL> CREATE TABLE ggl_temp AS (SELECT * FROM ggl_links);

Table created.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 rendc.org     07-MAY-06

 110 rendc.org/html     07-MAY-06

 112 oracle.com      07-MAY-06

 113 sql.com      07-MAY-06

If a session is ended, the current transaction in that session will be committed and ended. All the database changes made in the current transaction will become permanent. This is called an implicit commit when session is ended. The following tutorial exercise shows you that the "disconnect" command forces the current transaction to be committed and ended. When the session is reconnected, you can see the changes made by the UPDATE statements.

SQL> connect HR/globalguideline

SQL> UPDATE ggl_links SET url = 'globalguideline.COM'

2 WHERE id = 101;

SQL> UPDATE ggl_links SET url = 'globalguideline.COM/html'

2 WHERE id = 110;

SQL> disconnect

SQL> connect HR/globalguideline

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 globalguideline.COM     07-MAY-06

 110 globalguideline.COM/html     07-MAY-06

 112 oracle.com      07-MAY-06

 113 sql.com      07-MAY-06

If a session is killed by the DBA, the current transaction in that session will be rolled back and ended. All the database changes made in the current transaction will be removed. This is called an implicit rollback when session is killed. The following tutorial exercise shows you that the DBA KILL SESSION command forces the current transaction to be rolled back with all the changes uncommitted.

SQL> connect HR/globalguideline

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 globalguideline.COM     07-MAY-06

 110 globalguideline.COM     07-MAY-06

 112 oracle.com      07-MAY-06

 113 sql.com      07-MAY-06

SQL> DELETE FROM ggl_links where id = 112;

1 row deleted.

SQL> DELETE FROM ggl_links where id = 113;

1 row deleted.

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 globalguideline.COM     07-MAY-06

 110 globalguideline.COM     07-MAY-06

A READ WRITE transaction is a transaction in which the read consistency is set at the statement level. In a READ WRITE transaction, a logical snapshot of the database is created at the beginning of the execution of each statement and released at the end of the execution. This guaranties that all reads within a single statement get consistent data from the database.

For example, if you have a query statement that takes 10 minutes to be executed, a snapshot of the database will be created for this statement for 10 minutes. If a subquery is used in this statement, it will get the consistent data no matter when it gets executed within this 10 minutes. In another word, data changes made during this 10 minutes by other users will not impact the execution of this query statement.

By default, all transactions are started as READ WRITE transactions.

A READ ONLY transaction is a transaction in which the read consistency is set at the transaction level. In a READ ONLY transaction, a logical snapshot of the database is created at the beginning of the transaction and released at the end of the transaction. This guaranties that all reads in all statements within this transaction get consistent data from the database.

For example, if you have a transaction with many statements that takes 10 hours to be executed, a snapshot of the database will be created for this transaction for 10 hours. If a query statement is executed at the beginning of the transaction and at the end of the transaction, it will return the same result guarantied. In another word, data changes made during this 10 hours by other users will not impact the execution of statements within this transaction.

There are lots of restrictions in a READ ONLY transaction:

* You can not switch to READ WRITE mode.

* You can not run any INSERT, UPDATE, DELETE statements.

* You can run SELECT query statements.

The tutorial exercise below shows you some of the restrictions:

SQL> connect HR/globalguideline

SQL> SET TRANSACTION READ ONLY;

Transaction set.

SQL> SET TRANSACTION READ WRITE;

ORA-01453: SET TRANSACTION must be first statement of

transaction

SQL> INSERT INTO ggl_links (url, id)

2 VALUES ('sql.com', 113);

ORA-01456: may not perform insert/delete/update operation

inside a READ ONLY transaction

SQL> DELETE FROM ggl_links where id = 110;

ORA-01456: may not perform insert/delete/update operation

inside a READ ONLY transaction

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 globalguideline.COM     07-MAY-06

 110 globalguideline.COM     07-MAY-06

If you want a transaction to be set as READ ONLY, you need to the transaction with the SET TRANSACTION READ ONLY statement. Note that a DML statement will start the transaction automatically. So you have to issue the SET TRANSACTION statement before any DML statements. The tutorial exercise below shows you a good example of READ ONLY transaction:

SQL> connect HR/globalguideline

SQL> SET TRANSACTION READ ONLY;

Transaction set.

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 globalguideline.COM  07-MAY-06

 110 globalguideline.COM  07-MAY-06

 112 oracle.com      07-MAY-06

 113 sql.com      07-MAY-06

A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.

Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:

ggl_XE =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = www.rendc.org)

(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

The above "connect identifier" defines "TNS_XE" with the following connection information:

* The network hostname: www.rendc.org.

* The network port number: 1521.

* The name of the target database instance: XE.

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/globalguideline

SQL> SET TRANSACTION

ISOLATION LEVEL

READ COMMITTED;

Transaction set.

SQL> SELECT * FROM ggl_links;

ID URL NOTES

--- ---------------- --------

101 globalguideline.COM

110 globalguideline.COM

SQL> UPDATE ggl_links

SET url='rendc.org'

WHERE id=101;

1 row updated.

(lock created on row id=101)

As can see from the pervious tutorial exercise, performance of the second session is greatly affected by the data lock created on the database. To maintain a good performance level for all sessions, you need to monitor the number of data locks on the database, and how long do they last.

Oracle maintains current existing data locks in a Dynamic Performance View called V$LOCK with columns like:

* SID - Session ID to identify the session that owns this lock.

* TYPE - The type of the lock, like TM (DML enqueue) and TX (Transaction enqueue).

* LMODE - The lock mode in which the session holds the lock.

* REQUEST - The lock mode in which the session requests the lock.

* CTIME - The time since current lock mode was granted

* BLOCK - A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

The following tutorial exercise shows you how to view existing locks on the database:

(session 1)

SQL> connect HR/globalguideline

SQL> UPDATE ggl_links

SET url='rendc.org'

WHERE id=110;

1 row updated.

Oracle server automatically detects dead locks. When a dead lock is detected, Oracle server will select a victim transaction, and fail its statement that is blocked in the dead lock to break the dead lock. The tutorial exercise below shows you an example of statements failed by Oracle server because of dead locks:

(session 1)

SQL> connect HR/globalguideline

SQL> UPDATE ggl_links

SET notes='Session 1'

WHERE id=101;

1 row updated.

       (session 2)

       SQL> connect HR/globalguideline

       SQL> UPDATE ggl_links 

       SET notes='Session 2' 

       WHERE id=110;

       1 row updated.

       SQL> UPDATE ggl_links 

       SET notes='Session 2' 

       WHERE id=101;

       (blocked by lock id=101)

SQL> UPDATE ggl_links 

 SET notes='Session 1' 

 WHERE id=110;

(blocked by lock on row id=110)

       ORA-00060: deadlock 

       detected w