• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Record-Level Locking In JDBC

 
Ranch Hand
Posts: 694
Mac OS X Eclipse IDE Firefox Browser
  • Mark post as helpful
  • send pies
  • 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: 40035
809
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • 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.
 
The airline is called "Virgin"? Don't you want a plane to go all the way? This tiny ad will go all the way:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic