Oracle

Oracle Locking and isolation levels-28

Advertisements

Locking

DML locking: It acquire locks at both the table level and the row level.

Row_locking

Always – Low level locking – Row lock – Keyword: TX

Intent – High level locking – table lock – Keyword: TM

Row level lock- DML operations

Table level lock – when this a DDL & DML. Table locks do not affect concurrency of DML.

Table lock modes

Row share lock (RS) — ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access.

Row exclusive lock (RX) — ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode

Share table lock (S)

Share row exclusive table lock (SRX) — SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

Exclusive table lock (X) – EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

 

Identify the locking:

V$lock

Sid – Session id

ID1- Object id

Lmode – 3/6  – 3 –row & 6 – table

Type – TM/TX

 

Sql> select object_name from dba_objects where object_id in (select id1 from v$lock where type =’tm’);

 

 

Blocking – Which is locked and who is wating

Sql> select xid,object_id,session_id,oracle_username,locked_mode from v$locked_object;

In the result: XID 2 is a locking user and 0 is a waiting.

OR

Sql> select * from  DBA_BLOCKERS; – It will display the holding sessions.

Sql> select * from DBA_WAITERS; – It will display the waiting sessions.

 

Solution:

  1. Commit – Developer task
  2. Rollback – Developer task
  3. Kill session – DBA task

 

Kill Session:

Get sid and serial#

Sql> select serial# from v$session where  sid= 12; – 83435353 serial#

Sql> Alter system kill session ’12, 83435353’;

We can kill session on SQL or OS level. Best one SQL level.

Dead lock:

ORA 00060 dead lock detected

EX:

User1 User2
Update emp set salary = 2000 where eno =1; Update emp set salary = 3000 where eno =2;
Update emp set salary = 3000 where eno =2; Update emp set salary = 2000 where eno =1;

 

Sql> select spid from v$process where  addr=(select paddr from v$session where sid=12);

 

Kill -9 session id

Or

Sql> Alter system kill session ’12, session id;

 

Isolation Level

 

Isolation Level Dirty Read Nonrepeatable Read Phantom Read
READ UNCOMMITTED Permitted Permitted Permitted
READ COMMITTED No Permitted Permitted
REPEATABLE READ No No Permitted
SERIALIZABLE No No No

 

DISK I/O tuning – It’s a storage or OS team will tell us. We can distribute the DB files sometimes.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: