Basic stuff

A transaction begins with a first statement, and ends up with COMMIT or ROLL BACK.

ACID properties

In Oracle, transactions have ACID properties.

ATOMICITY

Transactions are atomic operations. Individual database statements and stored procedures are treated as atomic statements. They will either succeed or fail in its entirety.

CONSISTENCY

Any transaction takes the database from one logically consistent state to another logically consistent state. In other words the set of business rules is not violated before the transaction started and the time the commit takes place.

ISOLATION

Isolation is the capability of the database to permit concurrent modifications and reads of data within it. The isolation levels are based on three ‘reads’ that are permitted or illegal for each of the isolation levels.

Dirty Read: permits read of uncommitted ‘dirty’ data
Non-repeatable read: a row might change in two consecutive reads
Phantom read: some rows might be added in two consecutive reads (which implies that
more data might satisfy your original query).

Oracle by default won’t allow dirty reads.

Oracle Isolation Levels

Oracle provides these transaction isolation levels.

Isolation Level	Description
Read committed	This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.
Serializable	Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.
Read-only	Read-only transactions see only those changes that were committed at the time the transaction began and do not allows INSERT, UPDATE, and DELETE statements.

Set the Isolation Level

Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

SET TRANSACTION READ ONLY;

CONCURRENCY CONTROL

Oracle provides lock mechanism to a shared resource. Oracle locks data at the row level, but it uses locks at many different levels to provide concurrent access to various resources.

When we update a row, we do not have to lock it, since Oracle does it for us. There are, however, some situations when explicit locks are necessary. Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking.

Keep in mind that Oracle locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements so that database users never need to lock any resource explicitly. Oracle’s default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency.

Modes of Locking

Oracle uses two modes of locking in a multiuser database:

• Exclusive lock mode prevents the associate resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
• Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

Deadlocks

A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Figure 13-3 is a hypothetical illustration of two transactions in a deadlock. In Figure 13-3, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds without being terminated. However, each tries next to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held. Figure 13-3 Two Transactions in a Deadlock

Description of “Figure 13-3 Two Transactions in a Deadlock”

Deadlock Detection

Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes statement-level rollback.

Multi-versioning and Read Consistency

Oracle implements a multi-version read consistent concurrency model. What that means is that:

1. Queries always produce correct results in a point of time.
2. Queries are never blocked by writers of data.

To illustrate this important concept we follow a transaction example in which potentially long query will get data changed underneath by some other transaction. However, the query returns the snapshot of database at the time the query begin, regardless of the changes in database that happen during the query process. This happens without reader being blocked and without loss of data consistency.

Example

Suppose you have a table called ACCOUNTS, with three columns named ACCOUNT ID, ACCOUNT TYPE, and BALANCE. It can be created at follows:

Create cable accounts
        account-id      number,
        account-type   varchar2(20),
        balance        number
);

Our job is to report the sum of BALANCE accurately and quickly upon demand. We will report the time of day and the total of all account balances. This sounds easy, and the query we need to perform is simply:

select current_timestamp, sum(balance) total_balance from accounts;

Note that CURRENT_TIMESTAMP is a built in column in Oracle 10g that returns the current date and time.

However, the problem is somewhat complicated by the fact that while we are doing this many hundreds or even thousands of transactions will be applied to this database table. People will transfer money from their savings to checking accounts, they’ll be making withdrawals, deposits, and so on. So, as we are running this query the data is changing in the database. We have already seen how we could use READ ONLY or SERIALIZABLE transaction to solve this problem. In this particular case though, we don’t even need to do that, since we have but one SQL query and Oracle processes all statements in a read-consistent fashion. What we are more interested in right now is how this actually takes place.

Let’s say that the ACCOUNTS table looks like this:

Table 3. Example of account in demonstrating read concurrency

ACCOUNTED	ACCOUNT TYPE	BALANCE
1234	Savings	100
5678	Checking	4321
2542	Savings	6232
7653	Savings	234
<hundreds	of thousands	of rows>
1234	Checking	100

Our query will process, row by row, each of the rows in this table when summing up the BALANCE
column. At the same time, the owner of ACCOUNT_ID 1234 is going to transfer $50 from their savings account to their checking account. Lets walk through what happens as these two events take place. In this case our story will have two endings, with one to demonstrate what happens when a query detects locked data, and the other to demonstrate what happens when it detects data that has changed and that it should not see:

Time	Event	Comments
T1	We begin the query in Session 1	It will start reading the table. This will take a couple of minutes, as the ACCOUNTS table is very large. This query has already read the “first row” for the savings account for ACCOUNT_ID 1234 but hasn’t gotten to the checking account row yet.
T2	The owner of account 1234 starts a transaction at an ATM
T3	The owner of account 1234 selects TRANSFER FUNDS and chooses to move $50 from their checking to savings account	The data in the database is updated so the checking account now has $50 and the savings account has $150. The work is net yet committed (but UNDO information is stored).
T4	Our query finally gets to the checking account row for ACCOUNT_ID 1234,	What should happen here? In most every other popular database, the answer would be “The query will wait of course’”. In Oracle it will not.
T5	Detecting that the data is locked by the work performed at T3, our query retrieves the UNDO information (the ‘before’ image of the data) and uses the image of the data that was current as of time T1	Oracle reads around the lock, it does not wait. Our query will read $100 for the checking account.
T6	Our report is done
T7	The ATM commits and completes	x

The interesting part of this is what occurs at time T5 in the above timeline. Oracle reads around the locked data, never stepping the processing of our query (giving us non-blocking reads), and getting the accurate answer as of time T1.

So, having seen what happens when Oracle detects locked data, let’s look at what happens if the data is not locked. We’ll pick up our story at time T4 again:

Time	Event	Comments
T4	The ATM session commits, finishes the transfer	The funds are moved. Other sessions will now have the ability to see $150 in savings and $50 in checking for ACCOUNT_ID 1234
T5	Our query finally gets to the checking account row for ACCOUNT_ID 1234	It is not locked anymore, no one is updating it
T6	Detecting that the data was modified after time T1, our query retrieves the UNDO information and uses the image of the data that was current as of time T1	Our query will again read $1OO for the checking account.
T7	Our report is done

The interesting thing to note here is that even though the data was committed, we did not read it. Instead, we read the data as it existed when our query began. If wee did read it as it was modified then you would report out a balance sum that was $50 less than existed in the bank ever. You would in effect be reporting a number that never existed. No money left the bank, none came to; we just moved some from one row to another. In Oracle you need not worry about that issue. It cannot happen, since the data will always be read consistent.

Oracle readers never block writers, and vice versa. Most of the other RDBMSs require that readers put a share lock
on the data while reading it to provide isolation.

Oracle Flashback Query

Oracle Flashback Query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system change number (SCN).  Flashback Query uses Oracle’s multiversion read-consistency capabilities to restore data by applying undo as needed. Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Using Flashback Query, you can query the database as it existed this morning, yesterday, or last week. The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.

Applications of Flashback Query

• Self-Service Repair

Perhaps you accidentally deleted some important rows from a table and wanted to recover the deleted rows. To      do the repair, you can move backward in time and see the missing rows and re-insert the deleted row into the current   table.

• E-mail or Voice Mail Applications

You might have deleted mail in the past. Using Flashback Query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box.

• Account Balances

You can view account prior account balances as of a certain day in the month.

• Packaged Applications

Packaged applications (like report generation tools) can make use of Flashback Query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN