Locking & Isolation

DML LOCKING AND ISOLATION LEVEL

Standards ::  ANSI-SQL92 and ISO/IEC 9075.

Its is very likely that same data is being read by multiple user as we to day work in multi user environment, there is a greater chance that two user are updating same data at a time can create problem, below listed are the issues faced in multi-user environment.

Lets have a quick look at a below table 

ISOLATION LEVEL	DIRTY READ	UN REPEATED READ	PHANTOM READ
READ COMMITTED	Possible	Possible	Possible
READ UNCOMMITTED	Not Possible	Possible	Possible
REPEATABLE READ	Not Possible	Not Possible	Possible
SERIALIZABLE	Not Possible	Not Possible	Not Possible

Concurrency (Multi User access) and Locking

Concurrency control and locking is the mechanism used by DBMSs for the sharing of data. ACID properties are achieved through concurrency control and locking.

One of the key functions of a database is to ensure that multiple users can read and write to the database without overwriting each other's changes. To handle simultaneous access of the same data by many users, Oracle utilises various types of locks

Modes of Locking

Oracle uses two modes of locking in a multiuser database:

Exclusive lock mode - Prevents the resource from being shared. This lock mode is obtained to modify data. Only one exclusive lock can be placed on a resource (such as a row or a table).Writers need an exclusive lock.

I updated a row. No one else can update it until I commit. I have

an X lock on that row and only one person at a time can have an X lock. an X lock

provides serialization to a resource.

Share lock mode - Allows the resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding a share lock to prevent concurrent access by a writer (who needs an exclusive lock). Many share locks can be placed on a single resource.

Lock Duration

The changes made by the SQL statements of one transaction become visible only to other transactions that start after the first transaction is committed.

Oracle releases locks acquired after a savepoint when rollingback to the savepoint.

Types of Locks

Oracle automatically uses different types of locks to control concurrent access to data and to prevent destructive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent exclusive access to the same resource and releases when the transaction no longer requires the resource.

Oracle locks fall into one of three general categories.

  

Lock	Description
DML locks (data locks)	DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows.
DDL locks (dictionary locks)	DDL locks protect the structure of schema objects—for example, the definitions of tables and views.
Internal locks and latches	Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.

The following sections discuss DML locks, DDL locks, and internal locks.

DML Locks

The purpose of a DML (data) lock is to guarantee the integrity of data being accessed concurrently by multiple users. For example, Oracle DML locks guarantee that a specific row in a table can be updated by only one transaction at a time and that a table cannot be dropped if an uncommitted transaction contains an insert into the table.

DML operations can acquire data locks at two different levels: for specific rows and for entire tables.

1.Row Locks (TX)

• The only DML locks Oracle acquires automatically are row-level.Readers of data do not wait for writers of the same data rows.
• Writers of data do not wait for readers of the same data rows unless SELECT ... FOR UPDATE is used, which specifically requests a lock for the reader.
• Writers only wait for other writers if they attempt to update the same rows at the same time.
• A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
• A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.

2.Table Locks (TM)

A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE.A able cannot be altered or dropped if an uncommitted transaction holds a table lock for it.

A table lock can be held in any of several modes

RS: row share

A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them

Eg: SELECT...FROM table...

SELECT ... FROM table FOR UPDATE OF ...

LOCK TABLE table IN ROW SHARE MODE

RX: row exclusive

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:

Eg: INSERT INTO table ...

UPDATE table ...

DELETE FROM table ...

LOCK TABLE table IN ROW EXCLUSIVE MODE

S: share

No updates are allowed by other transactions.It is used by readers.

Eg:LOCK TABLE table IN SHARE MODE

SRX: share row exclusive

Only one transaction at a time can acquire a share row exclusive table lock on a given table.

Eg:LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE

X: exclusive

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table.

Eg:LOCK TABLE table IN EXCLUSIVE MODE

Manual Locking
Oracle's automatic locking can be overridden at two levels:

Per Session
As described above, the ALTER SESSION statement can set the transaction isolation level.

Per Transaction
Transactions that include the following SQL statements override Oracle's default locking:
- The SET TRANSACTION ISOLATION LEVEL statement
- The SELECT ….. FOR UPDATE statement
- The LOCK TABLE statement (which locks either a table or, when used with a view, the underlying base table)
SQL> select * from dep for update;

SQL> select * from dep FOR UPDATE with NOWAIT

SQL> select * from dep FOR UPDATE with WAIT

SQL> select * from dep FOR UPDATE with SKIP LOCKED

The use of SELECT FOR UPDATE ensures that the data the end user changes while working in the form is identical to the corresponding data in the database.

<<HOME>>