Recently I came across an issue of locks in our application. We had an update statement say update schema.questions where question_id=123. We had 5 users invoke an action which in turn calls the above update statement and a read statement select * from schema.questions where question=123. Update call and read statement were not in an transaction and we had locks on the questions table for several hours. once we had the update call and select call in a spring managed transaction call the issue was resolved. I am wondering what caused the dead lock/locks for several hours which lead us to kill the application and restart it. We use oracle 11 as our data base.
Thanks
Venkat