Blocking Locks

1. Blocking Locks Vil Roufchaie www.dbpulsegroup.com

2. What Will Be Covered In This Presentation? • In this presentation I will briefly point out what lock are and how they operate in an Oracle database • Several locking and blocking lock examples will be discussed • Several useful SQL scripts to demonstrate blocking locks and how to terminate all such locks will also be presented • The details of Oracle locks are beyond the scope of his presentation • This presentation will enable you to address your locking issues at work with efficacy and more confidence www.dbpulsegroup.com

3. Users Often Call & Scream: • Why is the database so slow! • My program is going nowhere • Don’t understand why my program is blocking other programs? • Please kill these blocking sessions, they are making my [life miserable] program slow… • Please kill my programs ASAP, they are being [unkind to me] blocked • DBAs/Managers must 1st investigate before taking any actions www.dbpulsegroup.com

4. Information Is Power! • You (DBA/Mgr) doesn’t know: • What database/Unix privileges the users may possess? • And what actions they may have already taken? • And whether they’ve already killed the SID they are calling you about – Oracle quickly re-assigns SIDs, and if you kill that SID without probing 1st? • What information they are not sharing with you? • Whether they know the consequences of what they are asking for? • Whether they knowwhat they are really asking you to do for them? • You (DBA/Mgr.) must probe into the situation and figure out what’s been happening… www.dbpulsegroup.com

5. Why Is Locking An Important Subject • From a user’s perspective, her/his application is the most important on the system • She may feel that her application will be the only program on the database, • and hence system resources may not get allocated and utilized wisely in a program’s development phase… • The truth of the matter is, there are almost always many other applications that are running in the database system concurrently that require system resources… • Lockingprovides synchronized access to Oracle resources www.dbpulsegroup.com

6. Why Is Locking An important Subject • Unless care is taken in programming these applications, particularly how locks get utilized, • and the granularity with which they are implemented, which affects concurrency of data access, • a database can come to a stand-still due to what’s commonly termed as: Blocking Locks • Example: A long-running transaction takes a shared mode lock on a frequently accessed table, and all subsequent updates will be enqueued waiting for the lock taken by the 1st transaction to become free www.dbpulsegroup.com

7. What Must A DBA know • Being knowledgeable of: • locks, • blocking locks, • Being able to determine: • locked objects (tables), • relevant data blocks, • locked rows is extremely important in a time-sensitive situation • The ability to identify: • the session blocking other sessions • the blocked sessions • Having the confidence to kill • The correct blocking/blocked sessions • And not the wrong sessions! www.dbpulsegroup.com

8. Oracle Locks • A database without a locking system is like a city with roads that are void of traffic signals! • Oracle uses locks to control concurrent access to shared resources • Oracle Holds lock records in the database inside block headers where locked rows resides in what’s called: “Interested Transaction List” - ITL www.dbpulsegroup.com

9. Lock Types • DML Locks:Select for update, Delete, Insert, Update • DDL Locks:Create , Drop, Alter, etc. • Internal Locks and Latches:Light-wait serialization mechanism • Distributed Locks: (OPS cache consistency) • PCM Locks (used for cached blocked in OPS) www.dbpulsegroup.com

10. DML Locks • TX (transaction) LocksRow-level locks are held automatically by oracle inside a transaction for DML (Select, Delete, Insert, Update) operations on a row, and other transactions will have to wait until this transaction either commits or rolls back • TM (Table/DML Enqueue) Locks Are used to lock a table during DML operations so table structure may not get changed from under while these operations are underway. No table “create”, “alter” or “drop” command will be allowed while this lock is being held www.dbpulsegroup.com

11. DDL Locks • DDL lock: Is placed during DDL operation and is released immediately upon completion • (DDL statements Are preceded by a “commit”) • DDL Lock Types: • Exclusive DDL Locks • Share DDL Locks • Breakable Parse Locks www.dbpulsegroup.com

12. What’s A Transaction? • To understand a database, it is essential to know how it manages it’s transactions • A transaction is made up of one or more SQL operations • A transaction starts with a read/write operation and ends with a commit/rollback statement www.dbpulsegroup.com

13. What’s A Transaction? • To manage a database is to manage it’s workload of: short and long transactions • These transaction hit the database at any time - serially or concurrently at will www.dbpulsegroup.com

14. Transaction Process • Transaction locks are maintained through the life of the transaction • All locks and resources will be released as a transaction ends www.dbpulsegroup.com

15. Transactions & Concurrency • Oracle maintains a high degree of transaction concurrency while keeping the database consistentvia locks(enqueued locks) and what is called multiversion concurrency control system • What’s mulitversion concurrency? • Oracle allows a row to be written and read at the same time • “Writers won’t block Readers, Readers won’t block Writers” Oracle’s Slogan – but writers wait for other writers if they attempt to update identical rows • In order to maintain read consistency, Oracle maintains multiple temporary versions of data for read consistency • A reader reads an older version of data that’s locked and is being modified by another process www.dbpulsegroup.com

16. Transactions & Read Consistency Example: While transaction #2 is modifying block, transaction #1 tries to read same block Current Version (as viewed by transaction #2, SCN=101) Old Version (as viewed by transaction #1, SCN = 100) Oracle Block Header SCN =101 ITLFree Space … Rows … Empname = Jones, Extension = 7777 Oracle Block Header SCN =100 ITLFree Space … Rows … Empname = Jones, Extension = 6666 www.dbpulsegroup.com

17. Transactions & Concurrency • The higher the concurrency, the higher thethroughput of a database • To enable a high level of concurrency, Oracle applies locks in a least restrictive way – row-level locking • Oracle supports row- as well as table-levellocking • These locks are implicitly applied by Oracle www.dbpulsegroup.com

18. Oracle Lock Granularity • Every time a user issues a lock, another process can be held back from using the locked resource • The lower lock granularity, the higher concurrency of access to Oracle database • A table-level lock prevents many users from modifying table data entirely • A row-level lock is the preferred lock and is the default lock used by Oracle www.dbpulsegroup.com

19. Row-level Locking Strategy • Each row within a table can be locked individually • The locking process has exclusive right to update the locked row • The remaining rows can be updated by other processes • All rows being updated can be read by other processes – they see the old version of the updated data via RBS blocks www.dbpulsegroup.com

20. What Happens When A Row-level Lock Is Placed On A Row? • 1st a DML lock is placed on the row • No other processes can lock or update this row • This lock will be released when the locking process commits or rolls back • 2nd a DDL lock is placed on the table containing the row to prevent anyone from altering or dropping the table while the row is undergoing the update www.dbpulsegroup.com

21. What Happens When A Table-level Lock Is Placed On A Table? • The Whole table is locked • Only the table-locking process can update rows in the table • (The above process, trying to update a row in the table, will obtain a Row Share Exclusive lock on the table as a whole) • No rows in the table can be updated by other processes – writers will block other writers www.dbpulsegroup.com

22. What Happens When A Table-level Lock Is Placed On A Table? • … The Whole table is locked • Other processes can query all rows, including the one being updated in the table – writers won’t block readers • These query-only processes are redirected to the locking process’s rollback data – writer’s rollback data used for read consistency • (The Row Share Exclusive lock is released upon transaction commit/rollback) www.dbpulsegroup.com

23. Oracle Lock Modes • There are two basic lock modes: • Exclusive (X) • A lock held in this mode is for update and the resource held can be changed only by the holding process (until the lock is released) • Share (S) • Allows resource to be shared among multiple concurrent users • Prevents other users to acquire an exclusive lock to modify the shared resource • Row exclusive (RX) • Share (S) table locks • Share row exclusive (SRX) table lock • Exclusive (X) table lock www.dbpulsegroup.com

24. Exclusive Locks Examples www.dbpulsegroup.com

25. Shared Locks Examples www.dbpulsegroup.com

26. Each Type of Lock Allows Other Locks To Be Granted On A Resource: Exclusive (X) = Allow SELECT queries only Share (S) =Allow ROW SHARE or SHARE locks to be granted to the locked rows. Share Row Exclusive (SRX) =Allow ROW SHARE locks to be granted to the locked rows. Row Exclusive (RX) =Allow ROW EXCLUSIVE or ROW SHARE locks to be granted to the locked rows. Row Share (RS) =Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks to be granted to the locked rows. www.dbpulsegroup.com

27. Latches • Latches provide exclusive access to data structures in the SGA • They generally only allow a single process to examine a data structure of interest – and hence are very restrictive and do not promote concurrency • Latches act as short-term locks on simpler data structures • There is no request queuing mechanism for latches – they keep trying until the resource is acquired www.dbpulsegroup.com

28. Lock Management By Oracle • Lock management is done implicitly on your behalf by Oracle • There is no lock management system in Oracle • Oracle resolves deadlocksautomatically with small impact on system performance Select name, value From v$sysstat Where name = ‘enqueue deadlocks’; NAME VALUE ------------------------------ ---------- enqueue deadlocks 0 www.dbpulsegroup.com

29. Lock Management By DBA • As DBA you will be mainly concerned about a lock being held by a process which may be blocking other processes’ ability to modify the locked resource • Processes serialize on a resource due to a blocking lock • Oracle provides a utility: utllockt.sql C:\oracle\ora92\rdbms\admin SQL> @utllockt WAITING_Session LOCK_TYP MODE_requested MODE_held LOCK_ID1 LOCK_ID2 -------------------------- ----------------- ------------------------ ----------------- --------------- -------------- 18 None 10 Transaction Excl Excl 524296 4376 SQL> desc dba_locks; Name Null? Type ----------------------------------------- -------- -------------- SESSION_ID NUMBER LOCK_TYPE VARCHAR2(26) MODE_HELD VARCHAR2(40) MODE_REQUESTED VARCHAR2(40) LOCK_ID1 VARCHAR2(40) LOCK_ID2 VARCHAR2(40) LAST_CONVERT NUMBER BLOCKING_OTHERS VARCHAR2(40) www.dbpulsegroup.com

30. Blocking Locks www.dbpulsegroup.com

31. Blocking Locks • (When processes requiring exclusive access to the same data are fired off at the same time,all will queue up behind the one process which grabs the lock 1st) • User actions may cause a process to be blocked example: a user kills a process with “kill -9” and submits the same process later, all the while the initial process is running and holding on to the blocking lock assuming that it had been “killed” www.dbpulsegroup.com

32. Blocking Locks • (Blocking locks can stop applications from processing) • Blocking locks are mainly: • TX (transaction) locks or • TM (table) locks • Example: session 1:SQL> Select * from scott.emp1 for update nowait; - (User goes to lunch) - Guarantees user the 1st right to updateemp table rows session 2:SQL> update scott.emp1 set empno=empno+1; - session 2 hangs -waiting for session 1 to commit or rollback www.dbpulsegroup.com

33. Blocking Locks • Identify Blocking and blocked sessions SQL> Select * from v$lock; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------------- ----------- ---- ---- ------ ------- ---------- -------------- ---------- ---------- 682BE310 682BE320 4 XR 4 0 1 0 5713 0 682BE48C 682BE49C 5 TS 2 1 3 0 5701 0 67AA4418 67AA442C 9 TM 30778 0 3 0 7 0 682BE3A8 682BE3B8 9 TX 131095 5539 0 6 7 0 67AF2914 67AF2A20 10 TX 131095 5539 6 0 100 1 67AA4394 67AA43A8 10 TM30778 0 2 0 100 0 SQL> select * from dba_blockers; HOLDING_SESSION ----------------------------- 10 SQL> select l1.sid "Blocking SID", l2.sid "Blocked SID" from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0, and l1.id1=l2.id1 and l1.id2=l2.id2; Blocking SIDBlocked SID ----------------- ------------------- 109 www.dbpulsegroup.com

34. Blocking Locks • Identify the Object: Select object_name From dba_objects Where object_id=&id Enter value for id: 30778 OBJECT_NAME ---------------------- EMP1 • Identify the obj#, file#, block#, and rowid of the blocked row: Select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# From v$session Where sid=&sid; Enter value for sid: 9 ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ------------------------- --------------------------- ------------------------------ ----------------------------- 30778 1 50554 0 www.dbpulsegroup.com

35. Avoid Blocking Locks In Your Apps • If pessimistic (early) locking implemented “Select * from emp for update nowait” • Reduced app scalability through Blocking other processes • Have some mechanism in your app to time-out idle sessions • If optimistic locking (late locking) implemented “Update emp set empno = empno -10” Where ename = ‘Johnson’ ” • Improved app scalability – there is no advance locking • But be wary of Lost Updates • Opt for fine-granularity locking in your applications – row-level is better than table-level, etc.? www.dbpulsegroup.com

36. (Blocking) LocksUseful Oracle Views & Scripts • v$session is a useful view: • ROW_WAIT_OBJ# Database object • ROW_WAIT_FILE# File number • ROW_WAIT_BLOCK# Block number • ROW_WAIT_ROW# Row number (for row-level locks) • v$lock • v$process • utllockt.sql – Oracle’s report showing blocking and waiting sessions in a tree-structured format • v$waiters (run catblock.sql 1st) Name ---------------- WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 • dba_blockers: Name Null? Type ----------------------------------------- -------- ---------- HOLDING_SESSION NUMBER www.dbpulsegroup.com

37. User-Provided Data • V$session: • osuser • process • module • v$session • sid • v$process • spid Client Box Oracle Server Box • Oracle SID • Shadow process id (dedicated connection) • User login name (os) • User process id (os) • User program module Name (user-submitted program name) www.dbpulsegroup.com

38. User-Provided Data A user may provide any or part of any of the following information to you, the DBA: Client Machine/where he launched his program: • OS User name (v$session.osuser)User id name on client machine: vr1994 • OS Process id (v$session.process) User program process id number on client machine • Program Module name (v$session.module) User program module name, iSQL*PLUS, SQL*PLUS Oracle Server Machine: • OS Shadow Process id (v$process.spid)Shadow process id number – dedicated connection • Oracle Session id (v$session.sid) User session’s sid www.dbpulsegroup.com

39. Identifying the Blocking Session • Identifying a session in an environment with numerous instances and databases can be a daunting experience • Users mostly know their application’s names and not more • You’ll need to make sense of the bits-and-pieces you may get from your users, • In order to pin-point the culprit session(s) www.dbpulsegroup.com

40. Identifying the Blocking Session • Ask user if s/he knows any of the following: • process id • Shadow process id • OS user id • Module name (iSQL*Plus, SQL*Plus, application name, etc.) • Session id • The SQL that was running when their process was blocked www.dbpulsegroup.com

41. Identifying the Blocking Session: User Supplies The DBA What He Deems To Be His Job’s OS Process id On the Client Box • User supplies the process id of the program he launched from his machine (client-server1) or some parent/child process he believes is the process id: 2021 • DBA must determine the target database user is connected to • DBA logs into remote-server1 and runs UNIX’s process status (ps)command • Traverse child process ids to the end • $ ps -ef |grep 2021|grep -v grep • vr7191 2050 2021 0 11:29:13 ? • $ ps -ef |grep 2050 |grep -v grep • vr7191 2059 2050 0 11:29:13 ? • $ ps -ef |grep 2059 |grep -v grep • vr71912231 2059 314 11:29:13 ? • $ ps -ef |grep 2231 |grep -v grep • vr7191 2231 2059 314 11:29:13 ? www.dbpulsegroup.com

42. Identifying the Blocking Session: Using Program OS ID, determine The SID On Server Machine select s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status from v$process p, v$session s where s.paddr = p.addr (+) and s.process = '&process' SQL> / Enter value for process: 2231 Module Shadow Remote Remote Session Session Session SID Serial OS User Username name Process id Process id Server name active_length Logon Time Status ----- ------- ----------- -------------- ------------- ------------- ------------- -------------------- ---------------- -------------------- ----------- 115 10366 vr2451 system iSQL*Plus 22671 2231 client-server1 17033 12/18 11:14:18 ACTIVE www.dbpulsegroup.com

43. Modify This SQL Where High-lighted In Green According To What’s On The Next two Slides select s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status from v$process p, v$session s where s.paddr = p.addr (+) and s.process = '&process' SQL> / Enter value for process: 2231 Module Shadow Remote Remote Session Session Session SID Serial OS User Username name Process id Process id Server name active_length Logon Time Status ----- ------- ----------- -------------- ------------- ------------- ------------- -------------------- ---------------- -------------------- ----------- 115 10366 vr2451 system iSQL*Plus 22671 2231 client-server1 17033 12/18 11:14:18 ACTIVE www.dbpulsegroup.com

44. User Provided Data(Client Machine) • OS User name (v$session.osuser)User id name on client machine: vr1994 where s.paddr = p.addr (+) and s.osuser = '&osuser‘ SQL>/ Enter value for osuser: vr1994 • OS Process id (v$session.process) User program process id number on client machine where s.paddr = p.addr (+) and s.process = '&process‘ SQL>/ Enter value for process: 2231 • Program Module name (v$session.module) User program module name, iSQL*Plus, SQL*Plus, etc. Enter value for module: iSQL*PLUS where s.paddr = p.addr (+) and s.module = '&module‘ www.dbpulsegroup.com

45. User Provided Data (Server Machine) • OS Shadow Process id (v$process.spid) - Shadow process id number (dedicated connection) ps –ef | grep oracleTEST [instance name]| grep LOCAL=NO| grep 10: oracle 2135 1327 10:33:31 ? 32:05 oracleTEST (LOCAL=NO) oracle 15651 1343 10:20:01 ? 49:12 oracleTEST (LOCAL=NO) oracle 15551 1359 10:20:12 ? 44:13 oracleTEST (LOCAL=NO) oracle 14321 1 351 10:20:14 ? 46:07 oracleTEST (LOCAL=NO) where s.paddr = p.addr (+) and p.spid = '&spid‘ SQL> / Enter value for shadow_pid:15551 • Oracle Session id (v$session.sid) - User session’s sid where s.paddr = p.addr (+) and s.sid = '&sid‘ SQL> / Enter value for sid:15 Client Box Server Box www.dbpulsegroup.com

46. Some v$session columns definitions select s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status from v$process p, v$session s • last_call_et: shows you how long the active session has been active • logon_time: Time when the user loged in • Status: Session status (active/inactice) www.dbpulsegroup.com

47. Use Top Command to Display Shadow Process id $ top CPU TTY PID U SERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 19 ? 1204 oracle 146 20 3501M 6410K run 30:07 15.32 30.20 oracleTEST 11 ? 1330 oracle 146 20 3501M 6410K sleep 30:40 14.15 23.42 oracleTEST 33 ? 1870 oracle 156 20 3501M 6410K sleep 4:09 10.80 22.34 oracleTEST www.dbpulsegroup.com

48. Identifying Blocking Locks select l.sid SID, decode(l.type,'TM','DML','TX', 'Trans','UL','User',l.type) Lock_Type, decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X', 4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In, decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X', 4,'Share',5,'S/Row-X',6,'Exclusive', l.request) Lock_Req_In, l.ctime Duration_Seconds, decode(l.block,0,'NO',1,'YES') Blocking from v$lock l where l.request != 0 or l.block != 0 order by l.id1, l.lmode desc, l.ctime desc / SID Lock_Type Lock_Held_In Lock_Req_In Duration_Seconds Blocking ------ --------------- --------------- ----------------- ------------------------- ----------- 115 Trans Exclusive None 129 YES 77 Trans None Exclusive 70 NO www.dbpulsegroup.com

49. Kill The Blocking Session select s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status from v$process p, v$session s where s.paddr = p.addr (+) and s.sid = '&sid' SQL> / Enter value for sid: 115 Module Shadow Remote Remote Session Session Session SID Serial OS User Username name Process id Process id Server name active_length Logon Time Status ----- -------- ----------- -------------- ------------- ------------- -------------- -------------------- ------------------ -------------------- ----------- 115 10366 vr2451 system iSQL*Plus 22671 2231 client-server1 17033 12/18 11:14:18 ACTIVE • Alter system kill session ‘115,10366’ immediate; www.dbpulsegroup.com

50. Identifying the Blocking Session: User Provides Locked Object’s Name • From a separate session we submitted the following query: SQL> lock table scott.emp in exclusive mode; select l.sid sid, serial# Serial#, decode(l.type,'TM','DML','TX','Trans','UL','User',l.type) Lock_Type, decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Excl') Lock_Mode, o.owner Owner, o.object_name Obj_Name, l.ctime Duration_Seconds, o.status Status from dba_objects o, v$lock l, v$session s where l.id1 = o.object_id and o.object_name = upper (‘&object_name’) and l.sid=s.sid order by o.owner Enter value for object_name: emp SID Serial# Lock_Type Lock_Mode Owner Obj_Name Duration_Seconds Status ----- ---------- ------------------ ----------------- ---------------- -------------- ------------------------- ---------- 13 4466 DML Excl SCOTT EMP 4431 VALID • Alter system kill session ‘13,4466’ immediate; www.dbpulsegroup.com