Database Locks

Locking is a mechanism to control concurrent access to data in order to maintain consistency. Lock requests are made to concurrency-control manager. Transaction can proceed only after the request for lock is approved. Data can be locked in eXclusive mode or Shared mode. When a transaction sets an exclusive mode, it can both read and alter it. Whereas a shared mode will only let the transaction to read the data.

A transaction may be granted a lock on an item if the requested lock is compatible (see the compatibility matrix) with locks already held on the item by other transactions. For example if a request for an exclusive lock is made on data that is already locked, the request will not be approved until the existing lock (shared or exclusive) is removed.

SX
STRUEFALSE
XFALSEFALSE

Table: Compatibility matrix

The concurrency control manager records all the locks granted in a system table called the Lock table.

Two transactions T1 and T2 enter a deadlock state when a transaction T1 that has locked data X waits for access to some other data Y which is locked by the transaction T2, which is waiting for access to X--a catch-22 situation! A deadlock can be removed by rolling back one of the transactions. The rollback can be partial or complete. The idea is to remove the locks held by one transaction to let the other transaction complete. If a transaction reaches starvation state if it is being rolled back too many times.

Database locks in Oracle can be of the following granularity:
  1. Field
  2. Row
  3. Table
  4. Tablespace
  5. Database
If a particular lock is acquired at a granularity then the same lock is applied for all the lower granularity level. Example: If a lock is set to a table, it applied to rows and fields in the table.

Comments

Popular posts from this blog

OS/DB Migration - CMD. STR, TOC, EXT, R3load, DDLDBS.TPL and more

Fixing Inconsistent Table - Table activation fails due to inconsistency between DD and DB

301 Redirect Using SAP Web Dispatcher