This week's book giveaway is in the OCMJEA forum. We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line! See this thread for details.
Our default transaction isolation level is Connection.TRANSACTION_READ_COMMITTED and one particular operation obtains a row level lock using "select * from table where id= ? WITH LOCK EXCLUSIVE"
There are two main operations causing problems, one is an update on another table which requires the row lock before hand, and the second is a read-only operation which joins both tables from the previous operation.
Under extreme load (typically a single 5 minute period per week) there a large numbers of lock/update operations, and the corresponding read-only operations can take anywhere from several seconds to over a minute. This is obviously unacceptable.
Modifying the read so that it only gets the second table and doesn't touch the locked table shows drastic improvement, but we still kinda need the information from the lock table.
My question is: will changing the isolation level to Connection.TRANSACTION_NONE allow the read-only operation to access the locked row (ie not honor the lock) or am I "ship out of luck"?
The initial solution of removing the lock row from the query may still be acceptable, I need to take a closer look.