Oracle DB - Lock on cancellation of update query

Question

if i have a very long run UPDATE query that takes hours and I happen to cancel in middle of when it's running.

I got this message below: "User requested cancel of current operation"

Will Oracle automatically roll back the transactions?
Will DB lock be still acquired if I cancel the query? If so, how to unlock?
How to check which Update query is locking the database?

Thanks.

Oracle only rolled back the statement you executed, the transaction itself was not rolled back (btw: it's very simple to check that for yourself) — user330315
– user330315, Commented Jan 17, 2014 at 7:52
simple? Why don't you tell me how as I'm quiet new to Oracle DB. — Nil Pun
– Nil Pun, Commented Jan 17, 2014 at 21:02

Justin Cave · Accepted Answer · 2014-01-17 07:23:56Z

2

It depends.

Assuming that whatever client application you're using properly implemented a query timeout and that the error indicates that the timeout was exceeded, then Oracle will begin rolling back the transaction when the error is thrown. Once the transaction finishes rolling back, the locks will be released. Be aware, though, that it can easily take at least as long to roll back the query as it took to run. So it will likely be a while before the locks are released.

If, on the other hand, the client application hasn't implemented the cancellation properly, the client may not have notified Oracle to cancel the transaction so it will continue. Depending on the Oracle configuration and exactly what the client does, the database may detect some time later that the application was not responding and terminate the connection (going through the same rollback process discussed above). Or Oracle may end up continuing to process the query.

You can see what sessions are holding locks and which are waiting on locks by querying dba_waiters and dba_blockers.

answered Jan 17, 2014 at 7:23

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nil Pun Over a year ago

I'm using oracle sql developer tool to run Query. Which one of above applies to this client?

Collectives™ on Stack Overflow

Oracle DB - Lock on cancellation of update query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related