Guidelines for Locking Data in Oracle10g

In general, multi user databases use data locking mechanisms to resolve problems related with data concurrency, reliability and integrity. Data concurrency is the scenario when many user’s access data at the same instance. Data consistency means maintaining a consistent view of the data which is inclusive of the visible changes by a user’s own transactions and that of others.

To lock data in Oracle Database is to temporarily restrict the access to data for other users. This restriction placed on the data is referred to as lock. Oracle provides solutions to data concurrency and integrity issues between transactions by its locking mechanisms. Implicit locking occurs by default for all SQL statements.

The two modes of locking in a multi use database are Exclusive and Shared. Exclusive lock mode does not allow sharing of resource. The first transaction that locks a resource exclusively is the only one that can alter it until the lock is released. Shared lock allows the resource to be shared, based on the operations involved. Several transactions can lock the same resource in the SHARED mode. Throughout its operation, Oracle automatically uses different types of locks at different modes based on the resource and operation being performed. The locks generally are of the three categories namely, DML locks, DDL locks and internal locks and latches.

DML locks protect data and are called data locks. Example table locks protect entire tables and row locks protect selected rows. DML locks are used to prevent the destructive interference of the conflicting operations of multiple users. DML statements acquire locks at both table level and row level automatically.

Row level locks (TX) are used to prevent more than one transaction from modifying the same row. Oracle automatically locks data by a restricted row lock for each individual row on behalf of different DML operations like INSERT, UPDATE, DELETE, and SELECT … FOR UPDATE.

A transaction acquires a table lock by default when a table is modified by the DML statements using INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause and LOCK TABLE. Table locks primarily serve two purposes. They are used to reserve Data manipulation access on the transaction’s behalf and not to allow Data definition operations that conflict with the transaction, example, not to allow a table from being dropped in the midst of a transaction.

The table lock can be in several modes. They are ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE and EXCLUSIVE. Each of these locks permits certain operations and prohibits certain operations by other transactions. These locks can be automatically acquired by SQL statements (in some cases) or by explicit LOCK command. A ROW SHARE table lock denotes that the transaction holding the lock has locked the rows in table for update. It permits concurrent access, but prohibits users from locking entire table for exclusive access.

It is synonymous with SHARE UPDATE. A row exclusive table lock is same as ROW SHARE but prohibits locking in SHARE mode. A SHARE table lock permits concurrent queries but prohibits updates to the locked table. A SHARED ROW EXCLUSIVE lock is used to lock a whole table and allows others users view access, but prohibits others from locking in SHARE mode or updating. An EXCLUSIVE lock is the most restrictive of table locks. It permits queries on the locked table but prohibits any other activity. If a LOCK TABLE command cannot be completed, possibly because of another user acquiring a lock then the database waits until the table is available and before returning control to user. To avoid waiting the NOWAIT option can be specified.

DDL locks are dictionary locks and protect the structure of schema objects, for example table definition. A Data Dictionary Lock is used to take care that the definition of a schema object is not altered, while the object is in use by a DDL operation. Oracle acquires this automatically on behalf of the DDL transaction, users cannot explicitly request this. It has three categories namely; exclusive DDL locks, share DDL locks and breakable parse locks. Most DDL operations require restrictive DDL locks for a resource, to prevent other DDL operations that refer it from doing destructive action.

Shared DDL locks are used for a resource to prevent conflicting actions by other DDL operations and to allow at the same time data concurrency for similar DDL operations. Parse locks are used by a SQL statement in the shared pool, so that the shared SQL area associated with it can be invalidated if an object that is referred to by it, is dropped or altered. This can be broken to allow conflicting DDL operations; hence it is referred to as Breakable Parse Lock.

Internal locks and latches protect internal data structures such as data files and are entirely automatic. They help to protect internal database and memory structures. They are inaccessible to users and serve a variety of purpose. Latches help protect data objects in the system global area (SGA) and internal locks can be used for protecting files, tablespaces and rollback segments etc.

Explicit (Manual) Data locking can be used to override the default oracle locking mechanisms in certain cases. These cases are applicable for situations such as applications that require transaction level read consistency, that is queries on them must provide dependable data for the duration of the transaction or application that require for a transaction to have exclusive access to a resource. Manual locking overrides oracle’s automatic locking at the transaction or at session level.

At the session level, transaction that includes statements such as the SET TRANSACTION ISOLATION LEVEL, LOCK TABLE, SELECT … FOR UPDATE override Oracle’s default locking. These locks are released after the transaction is committed or rolled back. At the session level, ATLER SESSION statement is used to set the required transaction isolation level. Whenever Oracle’s default locking mechanism is overridden at any level care should be taken that data integrity is certain, data concurrency is satisfactory and deadlocks are handled.

Oracle also provides Lock management services, where an application developer can request for a lock of specific type, change the lock type, give locks a unique name to be referenced in another procedure in the same or different instance and release the lock by including the relevant statements in a PL/SQL block. These are accessible through procedures in the DBMS_LOCK package.

FREE Subscription

Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.

Note : We never rent, trade, or sell my email lists to
anyone. We assure that your privacy is respected
and protected.

_______________________________________