SAS connection to Oracle hung up for 2 hours
In SAS we have a library which is actually ORACLE schema and today I faced with a strange event when trying to query a table in this library. A regular SAS SQL query:
proc sql; delete from table where id=123; quit;
Was hung up for two hours while it usually took some seconds:
NOTE: PROCEDURE SQL used (Total process time): real time 2:00:33.49 cpu time 0.03 seconds
While this operation was being performed I tried to delete a nearby row in ORACLE SQL DEVELOPER but it hung up processing delete request too. However deleting a row that was not nearby these rows did not cause any problems. Well how can I find out the possible reason? I guess that was a sort of deadlock.
It sounds like someone has locked a row that your session is trying to delete. You should be able to spot this by querying v$session:
select sid, schemaname, osuser, terminal, program, event from v$session where type != 'BACKGROUND';
and checking if your session has an event of "enq: TX - row lock contention" (or similar). If so, then you'll have to work out who has the blocking lock (if you have access to Toad's session browser, this is easy to do, but Google should throw up something that can help. Or, if your database is Oracle 11.2, there's a view: v$session_blockers that ought to pinpoint the blocking session), and then get them to either commit or rollback their transaction.