• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Record-Level Locking In JDBC

 
Ranch Hand
Posts: 694
Mac OS X Eclipse IDE Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic