• 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

HELP - JDBC Connection & locking

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,
I dont think i understand JDBC correctly. I want to lock a record so that if i issue a statement like (select * from customer where customerID='123') then it will lock this record so that if another user wanted to select the same customer ID record they would be prevented from doing so. Can anyone tell me how to do this OR if is even possible???
Cheers,
Dave
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't know if you should be trying to lock records on select - you are not changing the data in anyway so why lock it?
JDBC doesn't do anything to lock records directly, you need to check the docs of your DB to find out when and what it will lock.
If you are worried about data changing in the schema while a client is trying to update it, use a locking pattern such as Optimistic Locking and JTA transactions. (There's one explained here http://www.javaworld.com/javaworld/jw-07-2001/jw-0713-optimism.html)
 
Sheriff
Posts: 67634
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"cluck cluck",
We're pleased to have you here with us in the JDBC forum, but there are a few rules that need to be followed and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and change your display name to match it.
In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.
Thanks!
bear
JDBC Forum Bartender
 
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Transaction type SERIALIZABLE will lock records that have been read while a transaction is open. You specify it at the createStatement or executeQuery - I can't remember which. Or maybe it's at the connection level... Whatever.
Either way, that's what it's for - to lock read records while a transaction is open so that they don't change.
 
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Transaction type SERIALIZABLE will lock records that have been read while a transaction is open

.
Actually, what I believe it means is that the DBMS will not allow a value to be accessed until the transaction is committed.

I want to lock a record so that if i issue a statement like (select * from customer where customerID='123') then it will lock this record so that if another user wanted to select the same customer ID record they would be prevented from doing so


The Connection.TRANSACTION_SERIALIZABLE flag will not allow dirty reads while during a transaction. But this would be of no help during a simple query of the table. If the auto-commit is set to true, then each statement is a transaction, so locks are only held during one statement. Yes, you can manipulate the auto-commit during a series of update/delete statements until you commit the transaction or rollback the transaction.
But, I believe the type locking mechanism you are wanting, will have to be developed at the program level.
My $0.02
Craig
 
You would be much easier to understand if you took that bucket off of your head. And that goes for the tiny ad too!
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic