At work today we had a trouble ticket generated by BMC Patrol that complained of a ‘Locking Conflict’. Having no idea what this might mean, one of my colleagues (who is learning Oracle) asked how we’d detect a locking problem.<\/p>\n
Because we were in a hurry, I fell back on a very old, clunky, but reasonably reliable method to see if there really was a locking problem. I was a bit surprised that no-one seemed to know how to do this, because it’s pretty basic stuff, so there must be people out there who don’t and this is for them.<\/p>\n
Session 1<\/span><\/p>\n <\/p> <\/p> <\/p> <\/p> <\/p> This row is now locked by session 1.<\/p>\n Session 2<\/span><\/p>\n This session hangs because it’s waiting for the row locked by session 1.<\/p>\n NOTE – If I’d added the NOWAIT keyword, I would have got this response instead<\/p>\n So we have a blocking lock problem – session 1 is blocking session 2 which is trying to lock the same row. The only way this situation will clear is if session 1 releases the lock (or a DBA kills the session ;-), which will achieve the same end).<\/p>\n How would we know about this? There are a number of dictionary views that can help here – this paper<\/a> is much more detailed than a mere blog and is worth a look – but the way I learnt to do this is<\/p>\n Run the next script once, to create the additional dictionary views in this database if they don’t exist already.<\/p>\n Now you can run utllockt.sql any time to see which sessions are blocking other sessions in a tree structure.<\/p>\n Here we can see that the session that isn’t indented at all – session id 147, is blocking session 141 (which is indented). If I add a third session trying to lock the same row, you’ll see the difference<\/p>\n Because sessions 141 and 158 are both waiting on session 147, they are indented to the same level. If I commit session 147, I’ll get this new output<\/p>\n So now session 147 has released the lock, 141 has grabbed it and session 158 is waiting for it. If I commit in session 141, this is what I’ll get when I run utllockt<\/p>\n There are no blocked sessions now. Session 158 has the lock, but I’m not really interested in that if it’s not blocking anything<\/p>\n What I like about this approach is that it’s available on every version of Oracle you’ll work with, on every platform and so when I needed to think quickly today, I knew I could rely on (and remember!) this. It has it’s problems, as discussed in the paper, but it will work well enough in most situations.SQL> connect testuser\/testuser
SQL> select object_id, object_name from test_tab
SQL> select object_id, object_name from test_tab
SQL> connect \/ as sysdba
SQL> @?\/rdbms\/admin\/catblock<\/pre>\n
SQL> @?\/rdbms\/admin\/utllockt
SQL> @?\/rdbms\/admin\/utllockt
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD
no rows selected<\/pre>\n
Updated – and as Nuno pointed out in the comments, there’s DBA_WAITERS<\/a> too but I’m not 100% sure what version that first appeared.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"