Record-Level Locking In JDBC

[Kaydell Leavitt]

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]

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.