File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Row locks and transaction levels Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Row locks and transaction levels" Watch "Row locks and transaction levels" New topic

Row locks and transaction levels

David O'Meara

Joined: Mar 06, 2001
Posts: 13459

(For reference, we use MaxDB aka SapDB)

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.
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

Cool. It turns out that the problem disappears when the isolation level is Connection.TRANSACTION_NONE and Connection.TRANSACTION_READ_UNCOMMITTED
[ June 05, 2008: Message edited by: David O'Meara ]
I agree. Here's the link:
subject: Row locks and transaction levels
It's not a secret anymore!