1. 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).
2. 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.
3. 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.
4. How To Create a Test Table for Transaction Testing?
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.
5. 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.
6. How To Rollback the Current Transaction?
If you have used some DML statements updated some data objects, you find a problem with those updates, and you don't want those updates to be permanently recorded in the database, you can use the ROLLBACK statement. It will remove all the database changes made in the current transaction and end the current transaction. The following tutorial exercise shows you how to use ROLLBACK statements:
SQL> connect HR/fyicenter
SQL> INSERT INTO fyi_links (url, id)
2 VALUES ('google.com', 102);
SQL> INSERT INTO fyi_links (url, id)
3 VALUES ('myspace.com', 103);
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 fyicenter.com 07-MAY-06
110 centerfyi.com 07-MAY-06
102 google.com 07-MAY-06
103 myspace.com 07-MAY-06
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 fyicenter.com 07-MAY-06
110 centerfyi.com 07-MAY-06
As you can see, the two new records inserted into the table were removed by the ROLLBACK statement.
7. What Happens to the Current Transaction If a DDL Statement Is Executed?
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/fyicenter
SQL> INSERT INTO fyi_links (url, id)
2 VALUES ('oracle.com', 112);
SQL> INSERT INTO fyi_links (url, id)
2 VALUES ('sql.com', 113);
SQL> CREATE TABLE fyi_temp AS (SELECT * FROM fyi_links);
Table created.
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 fyicenter.com 07-MAY-06
110 centerfyi.com 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
8. What Happens to the Current Transaction If the Session Is Ended?
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/fyicenter
SQL> UPDATE fyi_links SET url = 'FYICENTER.COM'
2 WHERE id = 101;
SQL> UPDATE fyi_links SET url = 'CENTERFYI.COM'
2 WHERE id = 110;
SQL> disconnect
SQL> connect HR/fyicenter
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
9. What Happens to the Current Transaction If the Session Is Killed?
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/fyicenter
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
SQL> DELETE FROM fyi_links where id = 112;
1 row deleted.
SQL> DELETE FROM fyi_links where id = 113;
1 row deleted.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
Keep the "HR" SQL*Plus window as is, and open another window to run another instance of SQL*Plus.
>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/password
Connected.
SQL> SELECT sid, serial#, username, status, type
2 FROM V$SESSION WHERE username = 'HR';
SID SERIAL# USERNAME STATUS TYPE
---------- ---------- ------------------ -------- -----
39 141 HR INACTIVE USER
SQL> ALTER SYSTEM KILL SESSION '39,141';
System altered.
Go back to the "HR" SQL*Plus window.
SQL> SELECT * FROM fyi_links;
ORA-00028: your session has been killed
SQL> connect HR/fyicenter
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
As you can see, two records were rolled back as the session got killed by another session.
10. How Does Oracle Handle Read Consistency?
Oracle supports two options for you on how to maintain read consistency:
◉ READ WRITE (the default option), also called statement-level read consistency.
◉ READ ONLY, also called transaction-level read consistency.
11. What Is a READ WRITE Transaction?
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.
12. What Is a READ ONLY Transaction?
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.
13. How To Set a Transaction To Be READ ONLY?
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/fyicenter
SQL> SET TRANSACTION READ ONLY;
Transaction set.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
Keep the "HR" SQL*Plus window as is, and open another window to run another instance of SQL*Plus.
>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/password
Connected.
SQL> DELETE FROM hr.fyi_links where id = 112;
1 row deleted.
SQL> DELETE FROM hr.fyi_links where id = 113;
1 row deleted.
SQL> COMMIT;
Commit complete.
Go back to the "HR" SQL*Plus window.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
As you can see that two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.
14. What Are the Restrictions in a READ ONLY 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/fyicenter
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 fyi_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 fyi_links where id = 110;
ORA-01456: may not perform insert/delete/update operation
inside a READ ONLY transaction
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
15. What Are the General Rules on Data Consistency?
Here is a list of general rules on data consistency:
◉ All SQL statements always work with a snapshot of the database to provide data consistency.
◉ For READ WRITE transactions, the snapshot is taken when each statement starts.
◉ For READ ONLY transactions, the snapshot is taken when the transaction starts.
◉ The snapshot never include uncommitted changes from other transactions.
◉ The snapshot always include uncommitted changes from its own transaction.
16. 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.
17. 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.
18. 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.
19. 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
20. How To View Existing Locks on the Database?
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/fyicenter
SQL> UPDATE fyi_links
SET url='centerfyi.com'
WHERE id=110;
1 row updated.
(session 2)
SQL> connect HR/fyicenter
SQL> INSERT INTO fyi_links
(url, id) VALUES
('oracle.com', 112);
1 row created.
SQL> UPDATE fyi_links
SET notes='FYI Resource'
WHERE id=110;
(wait on lock at id=110)
Now keep those two sessions as is. You need to open a third window to connect to the database as SYSTEM to view all current locks:
(session 3)
SQL> connect SYSTEM/password
SQL> select sid, username from v$session
2 where username='HR';
SID USERNAME
---------- ------------------------------
23 HR
39 HR
SQL> SELECT sid, type, lmode, request, ctime, block
FROM V$LOCK WHERE sid in (23, 39) ORDER BY ctime DESC;
SID TY LMODE REQUEST CTIME BLOCK
---- -- ---------- ---------- ---------- ----------
1 39 TX 6 0 84 1
2 39 TM 3 0 84 0
3 23 TM 3 0 27 0
4 23 TX 6 0 27 0
5 23 TX 0 6 18 0
You should read the output as:
◉ Line #1 and #2 represent the lock resulted from the UPDATE statement in session #1 on row id=110.
◉ Line #3 and #4 represent the lock resulted from the INSERT statement in session #2 on row id=112.
◉ Line #5 represents a request of lock resulted from the UPDATE statement in session #2 on row id=110, which is blocked by the lock from line #1 and #2.
21. What Is a Dead Lock?
A dead lock is phenomenon happens between two transactions with each of them holding a lock that blocks the other transaction as shown in the following diagram:
(transaction 1) (transaction 2)
update row X to create lock 1
update row Y to create lock 2
update row X
(blocked by lock 1)
update row Y
(blocked by lock 2)
(dead lock created)
22. How Oracle Handles Dead Locks?
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/fyicenter
SQL> UPDATE fyi_links
SET notes='Session 1'
WHERE id=101;
1 row updated.
(session 2)
SQL> connect HR/fyicenter
SQL> UPDATE fyi_links
SET notes='Session 2'
WHERE id=110;
1 row updated.
SQL> UPDATE fyi_links
SET notes='Session 2'
WHERE id=101;
(blocked by lock id=101)
SQL> UPDATE fyi_links
SET notes='Session 1'
WHERE id=110;
(blocked by lock on row id=110)
ORA-00060: deadlock
detected while waiting
for resource
(statement failed)
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).
2. 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.
3. 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.
4. How To Create a Test Table for Transaction Testing?
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.
5. 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.
6. How To Rollback the Current Transaction?
If you have used some DML statements updated some data objects, you find a problem with those updates, and you don't want those updates to be permanently recorded in the database, you can use the ROLLBACK statement. It will remove all the database changes made in the current transaction and end the current transaction. The following tutorial exercise shows you how to use ROLLBACK statements:
SQL> connect HR/fyicenter
SQL> INSERT INTO fyi_links (url, id)
2 VALUES ('google.com', 102);
SQL> INSERT INTO fyi_links (url, id)
3 VALUES ('myspace.com', 103);
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 fyicenter.com 07-MAY-06
110 centerfyi.com 07-MAY-06
102 google.com 07-MAY-06
103 myspace.com 07-MAY-06
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 fyicenter.com 07-MAY-06
110 centerfyi.com 07-MAY-06
As you can see, the two new records inserted into the table were removed by the ROLLBACK statement.
7. What Happens to the Current Transaction If a DDL Statement Is Executed?
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/fyicenter
SQL> INSERT INTO fyi_links (url, id)
2 VALUES ('oracle.com', 112);
SQL> INSERT INTO fyi_links (url, id)
2 VALUES ('sql.com', 113);
SQL> CREATE TABLE fyi_temp AS (SELECT * FROM fyi_links);
Table created.
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 fyicenter.com 07-MAY-06
110 centerfyi.com 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
8. What Happens to the Current Transaction If the Session Is Ended?
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/fyicenter
SQL> UPDATE fyi_links SET url = 'FYICENTER.COM'
2 WHERE id = 101;
SQL> UPDATE fyi_links SET url = 'CENTERFYI.COM'
2 WHERE id = 110;
SQL> disconnect
SQL> connect HR/fyicenter
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
9. What Happens to the Current Transaction If the Session Is Killed?
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/fyicenter
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
SQL> DELETE FROM fyi_links where id = 112;
1 row deleted.
SQL> DELETE FROM fyi_links where id = 113;
1 row deleted.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
Keep the "HR" SQL*Plus window as is, and open another window to run another instance of SQL*Plus.
>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/password
Connected.
SQL> SELECT sid, serial#, username, status, type
2 FROM V$SESSION WHERE username = 'HR';
SID SERIAL# USERNAME STATUS TYPE
---------- ---------- ------------------ -------- -----
39 141 HR INACTIVE USER
SQL> ALTER SYSTEM KILL SESSION '39,141';
System altered.
Go back to the "HR" SQL*Plus window.
SQL> SELECT * FROM fyi_links;
ORA-00028: your session has been killed
SQL> connect HR/fyicenter
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
As you can see, two records were rolled back as the session got killed by another session.
10. How Does Oracle Handle Read Consistency?
Oracle supports two options for you on how to maintain read consistency:
◉ READ WRITE (the default option), also called statement-level read consistency.
◉ READ ONLY, also called transaction-level read consistency.
11. What Is a READ WRITE Transaction?
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.
12. What Is a READ ONLY Transaction?
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.
13. How To Set a Transaction To Be READ ONLY?
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/fyicenter
SQL> SET TRANSACTION READ ONLY;
Transaction set.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
Keep the "HR" SQL*Plus window as is, and open another window to run another instance of SQL*Plus.
>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/password
Connected.
SQL> DELETE FROM hr.fyi_links where id = 112;
1 row deleted.
SQL> DELETE FROM hr.fyi_links where id = 113;
1 row deleted.
SQL> COMMIT;
Commit complete.
Go back to the "HR" SQL*Plus window.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
As you can see that two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.
14. What Are the Restrictions in a READ ONLY 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/fyicenter
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 fyi_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 fyi_links where id = 110;
ORA-01456: may not perform insert/delete/update operation
inside a READ ONLY transaction
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
15. What Are the General Rules on Data Consistency?
Here is a list of general rules on data consistency:
◉ All SQL statements always work with a snapshot of the database to provide data consistency.
◉ For READ WRITE transactions, the snapshot is taken when each statement starts.
◉ For READ ONLY transactions, the snapshot is taken when the transaction starts.
◉ The snapshot never include uncommitted changes from other transactions.
◉ The snapshot always include uncommitted changes from its own transaction.
16. 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.
17. 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.
18. 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.
19. 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
20. How To View Existing Locks on the Database?
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/fyicenter
SQL> UPDATE fyi_links
SET url='centerfyi.com'
WHERE id=110;
1 row updated.
(session 2)
SQL> connect HR/fyicenter
SQL> INSERT INTO fyi_links
(url, id) VALUES
('oracle.com', 112);
1 row created.
SQL> UPDATE fyi_links
SET notes='FYI Resource'
WHERE id=110;
(wait on lock at id=110)
Now keep those two sessions as is. You need to open a third window to connect to the database as SYSTEM to view all current locks:
(session 3)
SQL> connect SYSTEM/password
SQL> select sid, username from v$session
2 where username='HR';
SID USERNAME
---------- ------------------------------
23 HR
39 HR
SQL> SELECT sid, type, lmode, request, ctime, block
FROM V$LOCK WHERE sid in (23, 39) ORDER BY ctime DESC;
SID TY LMODE REQUEST CTIME BLOCK
---- -- ---------- ---------- ---------- ----------
1 39 TX 6 0 84 1
2 39 TM 3 0 84 0
3 23 TM 3 0 27 0
4 23 TX 6 0 27 0
5 23 TX 0 6 18 0
You should read the output as:
◉ Line #1 and #2 represent the lock resulted from the UPDATE statement in session #1 on row id=110.
◉ Line #3 and #4 represent the lock resulted from the INSERT statement in session #2 on row id=112.
◉ Line #5 represents a request of lock resulted from the UPDATE statement in session #2 on row id=110, which is blocked by the lock from line #1 and #2.
21. What Is a Dead Lock?
A dead lock is phenomenon happens between two transactions with each of them holding a lock that blocks the other transaction as shown in the following diagram:
(transaction 1) (transaction 2)
update row X to create lock 1
update row Y to create lock 2
update row X
(blocked by lock 1)
update row Y
(blocked by lock 2)
(dead lock created)
22. How Oracle Handles Dead Locks?
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/fyicenter
SQL> UPDATE fyi_links
SET notes='Session 1'
WHERE id=101;
1 row updated.
(session 2)
SQL> connect HR/fyicenter
SQL> UPDATE fyi_links
SET notes='Session 2'
WHERE id=110;
1 row updated.
SQL> UPDATE fyi_links
SET notes='Session 2'
WHERE id=101;
(blocked by lock id=101)
SQL> UPDATE fyi_links
SET notes='Session 1'
WHERE id=110;
(blocked by lock on row id=110)
ORA-00060: deadlock
detected while waiting
for resource
(statement failed)
Posts
0 comments:
Post a Comment