TRANSACTION PROCESIING, CONCURRENCY AND LOCKING

TRANSACTION PROCESSING

A transaction is an operation against the database, which comprises a series of changes to one or more tables. There are two classes of transactions, DML transactions, Which can consist of any number of DML statements and which Oracle treats as a Single entity or Logical unit of work and DDL transactions, which can only consist of one DDL statement.

There can be no halfway situation during the execution of the transaction, where some changes specified within the transaction are made to the database and others are not made. For every transaction either all the changes made to the database are made permanent or none of the changes are carried out.

A transaction begins when the first executable DML or DDL command is encountered and ends when one of the following occurs.
1. Commit or Rollback
2. DDL Command is issued
3. Certain Errors
4. Log Off
5. Machine Failure.
A DDL statement is automatically committed and therefore implicitly ends a transaction.

Making Changes Permanent

In order for changes to become permanent they must be committed to the database. The “ COMMIT ” command makes database changes permanent. The change or changes made to the database between two COMMIT commands therefore makeup a transaction. Until a transaction is committed, none of its changes are visible to other users.

COMMIT
Commit makes changes in the current transaction permanent.
Commit erases all Save Points in the transaction.
Commit ends the transaction.
Commit releases the transaction locks.
You should explicitly end the transaction in application programs, using COMMIT statement. If you do not explicitly COMMIT the transaction and the program terminates abnormally, the last committed transaction will be rolled back.
Implicit commits occur in the following situations.
Before a DDL command.
After a DDL statement.
At normal disconnect from database.

Removing Unwanted Changes :

Uncommitted changes may be abandoned by typing “ROLLBACK“. Rollback will return the data to the state it was in immediately after your last Commit by discarding all changes made since the last commit.

Syntax: RollBack to Savepoint Savepointname;

The Rollback statement is used to UNDO work.
Rollback to a savepoint is optional.
If you use rollback without a “ to Savepoint ” clause, it
Ends the transaction.
Undoes all changes in current transaction.
Erases all Savepoints in that transaction.
Releases the transaction locks.

Save Point :

Syntax : Savepoint SavepointName;

Savepoint can be used to divide a transaction into smaller portions.
Savepoints allow you to arbitrarily hold your work at any point in time, with the option of later committing that work or undoing all or a portion of it.
If you create a second savepoint with the same name as an earlier Savepoint, the earlier Savepoint is deleted.
The maximum number of Savepoints per user process is defaults to 5.

System Failures :

When a transaction is interrupted by a serious error, for example, a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to your data and returns your tables to their status at the time of the last COMMIT.
An automatic roll back is most often caused by a system failure, such as an unintentional system reset or power blackout. Errors in entering commands such as misspelling a column name or trying to perform an unauthorized operation on another user’s table do not interrupt a transaction or cause an automatic rollback.
Auto Commit :
Commit or Rollback may be issued manually or automatically by using the auto commit option of the set command. Syntax: Set AutoCommit On/Off.

CONCURRENCY AND LOCKING

Oracle locks are used to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing the same data. Locks are used to achieve two important goals, data concurrency and read consistency. Locks in Oracle fall into two categories.

Data Locks or DML Locks : These locks protect data. Table locks lock the entire table while Row locks just locks selected rows.

Dictionary Locks : These locks protect the structure of Database objects. Dictionary locks are of two types.
Parse Locks and
DDL Locks.
In this chapter we will discuss only data locks.

Summary Of Table Locks :

Tables can be locked in Exclusive(X), Share(S), RowExclusive(RX), RowShare(RS) and ShareRowExclusive(SRX) modes. Following table shows the posiility of operations against a table when that table is locked in a particular mode. The user who locks the table can perform any operation against the table. The following table describes the posibilities for other users on a table, which is locked by a user. For example, if a table is locked in exclusive mode, then locking that table in any mode and performing DML operations is not posible for other users. In the same way if a table is locked in share mode, then locking that table in share mode and row share mode is possible for other users.


Lock Table Command :

Syntax : Lock Table Tabname1, Tabname2,…, Tabnamen In Lockmode [Nowait];

Here Tabname1, Tabname2, …. , Tabnamen are the names of tables to be locked. Lock mode is one of the modes in which a table can be locked i.e. exclusive, share, row exclusive, row share and share row exclusive. Nowait will allow the user to do other work when another user locks a table, which he accesses. If nowait is not specified, then the system will hang if another user locks the table, which he accesses, until that user releases the lock.

Implicit Locking :

Implicit locking is the locking performed by Oracle automatically without Locking by the user manually. This implicit lock is generally done when updation or deletion is performed against a table and it locks only the rows that are affected by the update or delete statement.

Select…For Update : The select for update statement will overwrite the default locking mechanism. It is used in anticipation of performing an update. The difference between select for update and Update is that select for update locks the rows earlier in a transaction.

Syntax : Select col1, col2,…, coln from tablename For Update [ NOWAIT ]

Dead Lock :

It is quite possible in a multi user environment, that two users will lock each other out of resources. It is also possible that two users will end up locking each other out of different resources. This situation is called a Dead Lock, Because each user is waiting for resources held by the other user.

When Oracle detects a dead lock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. This resolves the Dead Lock, although other users may still wait until their resource is available. The signaled user should explicitly rollback their transaction.

Dead locks can be avoided if users accessing the same tables lock those tables in the same order as each other. You should pre-define order of access for all tables in your applications and then have all applications follow the same order. If this order is followed in all applications, deadlocks will be kept to a minimum.

Duration Of Locks :

All locks acquired during a transaction are released when the transaction is committed or rolled back.