CODEWITHSUNDEEP
oracle11gsql-update
Nil Pun
Nil Pun

Reputation: 17373

Oracle DB - Lock on cancellation of update query

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"

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

Thanks.

Upvotes: 1

Views: 1173

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 2

Related Questions