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