Database Transaction - Isolation, Consistency, Concurrency, Locks, and more

 Database Transaction 

•  Set of one or more statements that are executed as a unit.
• Logical, atomic unit of work that contains one or more SQL statements.

When a connection is created, it is in auto-commit mode, individual SQL statements are treated as a transaction.

The default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved.

Transactions can help to preserve the integrity of the data in a table, providing some level of protection against conflicts that arise when two users access data at the same time.

Acid Properties of Transaction

ATOMICITY

All tasks of a transaction are performed or none of them are.

CONSISTENCY

The transaction takes the database from one consistent state to another consistent state. 

ISOLATION

The effect of a transaction is not visible to other transactions until the transaction is committed.

DURABILITY

Changes made by committed transactions are permanent.

Data Consistency and Data Concurrency

• Multiple transactions executing at the same time need to produce meaningful and consistent results, that’s why Data Consistency and Data Concurrency are important.

Data concurrency 

• Many users should be able to access data at the same time.

Data consistency

• Each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

The ANSI/ISO standard SQL 92 speaks of three different phenomena when a transaction reads data that another transaction might have changed:

• Dirty read
• Non-repeatable read
• Phantom read

DIRTY READ  

Occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

 

NON-REPEATABLE READ 

Occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

PHANTOM READ  

Occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row. This row is referred to as a phantom.

 

Isolation Levels

The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput.These isolation levels are defined in terms of three phenomena discussed above.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Read Uncommitted 

Each query can see only data last updated before the query, uncommitted data changes are also visible. 

Read Committed 

Each query can see only data committed before the query, not the transaction, began. 

Repeatable Read 

Retains locks on every row it touches until the end of the transaction. Even the rows that are not part of the query results are locked. Phantom Read can come because there is no range lock on table (Only insert with values that satisfy the criteria can introduce Phantom Rows).

Serializable

Serializable is similar to Repeatable Read but it also acquires a key range lock which prevents the insertion of any new rows anywhere within the range.

Please note Seriaizable are not usable in Distributed Transactions.

The default isolation level of different DBMS varies quite widely. Most databases that feature transactions allow the user to set any isolation level. Some DBMS's also require additional syntax when performing a SELECT statement to acquire locks (e.g. SELECT ... FOR UPDATE to acquire exclusive write locks on accessed rows).

According to SQL 92 (but not to Oracle), a transaction is always in exactly one of these isolation levels. Further: the isolation level cannot change within a transaction.

The Oracle database uses the Read Committed and Serializable isolation levels in addition to a read-only mode. By default, Oracle uses the read committed isolation level for database transactions.

Read-Only transaction isolation level - Only data that was committed at the start of the transaction can be accessed by a query.  No modification to the data is allowed.

Difference between Read Committed Transactions and Serializable Transactions:

• Serializable transactions acquires a range lock so that no new rows can be inserted that satisfy the query criteria of Selected rows.
  
• if the blocking transaction commits and releases its locks, a read committed transaction proceeds with its intended update. A serializable transaction, however, fails with the error "Cannot serialize access", because the other transaction has committed a change that was made since the serializable transaction began.

Setting Oracle Isolation Level

Setting at Transaction Level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;

Setting at Session Level:

ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ ONLY;

Next Topic - Oracle Locking