aspose file tools*
The moose likes JDBC 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


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Row locks and transaction levels" Watch "Row locks and transaction levels" New topic
Author

Row locks and transaction levels

David O'Meara
Rancher

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
Rancher

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: http://aspose.com/file-tools
 
subject: Row locks and transaction levels