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, "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.