4

I have two processes accessing the same table. Each of them runs a select for update query and the WAIT value is set.

My question is: if process one runs the query and locks n rows in the table, how can the rows get released if process one crashed before committing?

I tried to open up two sessions in sql developer and run the select for update in the 1st session, then close it. RUn the same query in the 2nd session, and found that the rows are still locked!

xcoder
  • 1,336
  • 2
  • 17
  • 44

1 Answers1

2

The locks held by a transaction last as long as the transaction lasts*. The only way to release them is to end the transaction.

What does "crashed" mean precisely? In a three-tier application, when the front-end fails, the middle tier generally cleans up by closing the logical session which closes any associated transactions. In a client-server application, if the holder of the lock doesn't shut down gracefully, Oracle may not be immediately aware. The DBA would normally configure dead-connection detection to periodically send a ping to the client to verify that they are still alive so that the database can identify that a dead session exists after a few minutes and roll it back. If the database isn't configured to detect the problem and the client/server application doesn't close the transaction, you may have a bit of a wait. There may be a profile that limits the time a session can be connected or a DBA script that kills sessions after a period of time or you might have to call up the DBA and ask them to kill the other session.

* To be pedantic, there are user-defined locks that you can acquire via dbms_lock that persist for the session rather than for the transaction. But that's outside the scope of this question.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Hi, thank you for your answer. For example, if one of the app servers (where the sql is called) crashed. Do I still need the dead-connection detection in this case? – xcoder Oct 27 '15 at 16:41
  • @xcoder - I'm hard-pressed to think of many cases that I wouldn't want dead-connection detection. Presumably, an application server failing is rare enough that the DBA could go in and look for any sessions that were created from that app server before the failure and kill them. But that requires that you involve the DBA in an app server specific failure which isn't generally something you want to do. My strong bias would be automate what can be automated. – Justin Cave Oct 27 '15 at 17:18