Locking Concepts and Different Ways to Resolve Lock in Oracle

Contents:- Locking Concepts ,Different Types of Lock, Different Ways to Resolve Lock 

Concepts

Lock Mechanism prevents multiple session from changing the same data at the same time.

Lock mechanism defaults to a fine-grained, Row-Level locking mode.

        Means different transactions can be updated different rows within the same table without interfering with one another.

Different Types of Lock

Row share:-

Permits concurrent access to the locked table, but prohibits sessions from locking the entire table for exclusive access.

Row Exclusive- 

Is the same as row share, but also prohibits locking in share mode. the Row Exclusive locks are automatically obtained when updating, inserting, deleting data.

SHARE:

Permits concurrent queries but prohibits updates to the locked table. A SHARE lock is required (and automatically requested) to create an index on a table.

Share locks allow multiple readers and no writers.

SHARE ROW EXCLUSIVE:

Is used to query a whole table and to allow others to query rows in the table, but prohibits others from locking the table in SHARE mode or updating rows

.

EXCLUSIVE:

Permits queries on the locked table but prohibits any other activity on it. An EXCLUSIVE lock is required to drop a table.

Note:- Each DML Transaction must acquire two locks.

i) Exclusive row lock for the rows or rows being updated.

ii) Row Exclusive table-level lock for the table containing the rows.

This is to prevent another session from locking the whole table (possibly to drop or truncate it) while the changes is being made.

Possible causes of lock conflicts

Uncommited changes

Long-running transactions.

Un-necessary high locking levels.

Steps to do Manual lock on table.

SQL> lock table inventory  in exclusive mode;

SQL> lock table hr.employees in share mode nowait;

Here nowait returns control to you immediately if the specified table is already locked by another session.

Note:- The Automatic Database Diagnostic Monitor (ADDM)  also automatically detects lock conflicts can advise you on inefficient locking trends.

Different Ways to Resolve Lock Conflicts

By using SQL Query

1st SQL Query  use to determine the blocking session.

SQL> select sid,serial#,username from v$session where sid in (select  blocking_session from v$session);

Kill locking session

SQL> Alter system kill session  'sid,serial' immediate;

By using OEM

Go to oem performance tab -> instance lock -> select session -> kill session -> ok

OS Command to Kill the session

Windows level     

SQL>sqlplus sys as sysdba

SQL>select a.username, b.spid from v$session a, v$process b where a.paddr = b.addr and a.username ='HR';

Syntax: orakill instance_name spid

ex:c:\> orakill orcl 1456

Unix Flavor

$ kill -9 1456