CODEWITHSUNDEEP
sqloracle-databasetransactionsoracle11g
erni313
erni313

Reputation: 193

Oracle database is hanging infinitly in UPDATE queries

suddenly my update queries are not executing . i can make select queries but when i try to update records the database hangs infinitly. i tried even from sql plus and nothing happens.

Upvotes: 13

Views: 29026

Answers (2)

DCookie
DCookie

Reputation: 43533

This query will show you who is blocking your update. Execute the update that hangs, then in another session run this:

select s1.username || '@' || s1.machine ||
  ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1 join v$lock l2 on (l1.id1 = l2.id1 and l2.id2 = l2.id2) 
                 JOIN v$session s1 ON (s1.sid = l1.sid)
                 JOIN v$session s2 ON (s2.sid = l2.sid)
  WHERE l1.BLOCK=1 and l2.request > 0;

EDIT:

To properly attribute this, it looks like I cribbed this a while back from ORAFAQ.

Upvotes: 21

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Most likely you have another open uncommitted transaction for the same set of records, so they are locked for that transaction.

And, most likely, you locked them, running the same UPDATE in another transaction.

Just Commit/rollback your transactions, you should be fine.

Upvotes: 38

Related Questions