This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes row-level locking Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "row-level locking" Watch "row-level locking" New topic

row-level locking

Samuel Lugo

Joined: Feb 28, 2005
Posts: 24

I am fairly new to SQL. We are trying to write a program that invokes a transaction that at one point reads data from a row and locks it temporarily so that another transaction cannot access it at the same time. Once the transaction has completed, the row is then updated/unlocked and available for another transaction to read/use. We are using the select for update sql statement but for some reason we still see two transactions waiting to update the same row, which is causing an error. I read that there are three types of locks, but I'm not sure how to use the correct lock. Any help would be greatly appreciated. BTW, we are using DB2.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703

Use optimistic locking, which is keep an update timestamp or counter in the row and verify that it has not changed while performing an update.

Row-level locking isn't always enough because it doesn't prevent people from inserting new rows. For example, if you are looking for the most recent transaction or checking that a username is unique, row level locking will not help.

Oh, and for other types of locking such as table locking or pessamistic locking (the general form of row/table locking), most of those take big performance hits in modern systems unless in some cases updates are infequent.
[ December 14, 2005: Message edited by: Scott Selikoff ]

My Blog: Down Home Country Coding with Scott Selikoff
jQuery in Action, 2nd edition
subject: row-level locking
Similar Threads
concurrency and transaction isolation level
Deadlock problems with Hibernate/Spring/MS-SQL
View of SQL Server getting locked in JDBC
What is the name of hibernate 2 exception?
Why are dirty reads ok?