aspose file tools*
The moose likes JDBC and the fly likes Record-Level Locking In JDBC 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 "Record-Level Locking In JDBC" Watch "Record-Level Locking In JDBC" New topic
Author

Record-Level Locking In JDBC

Kaydell Leavitt
Ranch Hand

Joined: Nov 18, 2006
Posts: 689

Thanks for reading my post.

I come from a background of using explicit locks in a proprietary database (before SQL).

I'm used to having explicit control over locking. For example, to lock records, I could call the following methods:

1. database.lockForUpdate(), database.lockForReading(), or database.lockForDelete()
2. dbTable.lockForUpdate(), dbTable.lockForReading(), or dbTable.lockForDelete()
or
3. dbRecord.lockForUpdate(), dbRecord.lockForReading(), or dbRecord.lockForDelete()

An update-lock precluded other processes from doing an update until the first update is finished. This prevents the legendary "lost update" anomoly from ocurring.

I think that in JDBC SQL that I can get an update record-level update locks for a single record, a set of records, or for a DB Table with a SELECT statement as follows:

SELECT * FROM Items WHERE itemID = '1' FOR UPDATE

Is there a way to get an updaate lock for all records in the entire DB?

Is there any such command as:

SELECT * FROM Items WHERE itemID = '1' FOR DELETE

In the legacy system that I have experience in a delete-lock would wait for update-locks to be released so that a process couldn't delete a record that a user was viewing in a window.

Read-locks were the complement of delete-locks. A read-lock would prevent a delete() from occuring.

With three different types of locks (an no such thing as isolation levels) if therre was a deadlock, a int was returned from the DB Operation that caused the dead-lock and you'd have to release all of the locks in the transaction and start the transaction over.

How can I used the JDBC dialect of SQL to do locking? Do I have to set an isolatiion level or can I have more explicit control over record-level locking?

Kaydell
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Kaydell,
"for update" is a database specific extension. It's ok to use it, just keep in mind this ties you to your database vendor.

> Is there a way to get an updaate lock for all records in the entire DB?
No.

> How can I used the JDBC dialect of SQL to do locking? Do I have to set an
> isolatiion level or can I have more explicit control over record-level
> locking?
You need to set an isolation level. The JDBC driver takes care of the locking for you. It is likely to do a more efficient job of it too. Note that JDBC is not a dialect of SQL; it is a way to pass SQL statements through Java.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Record-Level Locking In JDBC