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:
- Commit – Developer task
- Rollback – Developer task
- 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.
