• 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

Locking with JDBC

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all!
I am wondering how to achieve the same locking behaviour needed by FBN with a relational database and JDBC.
Would the following be sufficient?

Any thoughts appreciated!
Reinhold
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I wouldn't compare an actual database to the one in FBN.
And the question would be better answered in the JDBC Forum, but you will have to explain the locking schema you are trying to acheive. The JDBC Forum won't necessarily know what the locking schema is for FBN assignment.
I am moving this to the JDBC Forum
Mark
 
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Reinhold,
I don't think so. I think a multi-user database must always implement locking, so locking has nothing to do with transactions.
Transactions group a number of database modifications, and when one fails, the others can be rolled back.
Locking is about data integrity in a multi-user enviroment and should always be supported. However, most databases hide the locking from the user. When the following SQL statements are executed concurrently without locking, the result may be corrupt data:

Hi Mark,
I think the topic belongs to the SCJD forum because it is about the nature of locking.
 
Reinhold Gruber
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mag!
Thank you for your answer. I felt that my post may be off topic but I found the problem interesting. My reasoning was the following. In the FBN applicaton it is necessary to read a record and to update this record based on its data(available seats). The only possibility to make a save update is to prevent all other users to modify this record after I have read it. --> that means this record must be write-locked.
But how can I achieve this with JDBC? I read some topics about isolation levels. As I understand "REPEATABLE READ" it quarantees me that reading a record twice within a transaction will give me the same record. Maybe I am wrong but I can not see another way for the database to accomplish this without a record write lock.
Would to you think?
Reinhold
 
Mag Hoehme
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Reinhold,
as per SCJD instructions, it is not necessary to update the client when some bookings have taken place after the client has read the record. If updating was a requirement, I would have opted for an event notification. The instructions explicitely say that your app should be able to handle the case that a booking has taken place between reading and my own booking.
I implemented a write lock (locking was only required for writing). The basic reasoning for this was that the client did only booking. Maintenance functionality was not part of the assignment. I prefered to stick to the instructions as closely as possible.
I think you don't need anything in JDBC when you are connecting to a database (such as MySQL, Oracle, and so on). The databases do take care of this reading/writing locking - not the JDBC programmer. Unless you are writing your own database, it's not necessary.
A good article on read/write locks can be found at http://www.asingh.net/technical/rwlocks.html.
 
Reinhold Gruber
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mag!
Ok here we go

This is (partially) our well know problem in JDBC. Between the select- and update-calls some other client may have changed the relevant record and I am not able to detect this nor can I prevent this with JDBC.
Reinhold
 
Ranch Hand
Posts: 1179
Mac OS X Eclipse IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are using a Oracle database, you can use 'select xx from yy where xx=tt FOR UPDATE' for locking the selected rows. You release it again by calling commit() (or rollback() if error) on the connection.
Rene
[ November 16, 2002: Message edited by: Rene Larsen ]
 
Mag Hoehme
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Reinhold,
I agree with you. I'd say here you need the same kind of LockManager as required for the SCJD.
 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Rene Larsen:
If you are using a Oracle database, you can use 'select xx from yy where xx=tt FOR UPDATE' for locking the selected rows. You release it again by calling commit() (or rollback() if error) on the connection.


This is known as pessimistic locking. When you are updating a row, the database will perform locking behind the scenes that prevents other transactions from updating that row. As Rene posted, Oracle allows you to lock the row when you read it using the 'FOR UPDATE' syntax in the SELECT statement. In pessimistic locking, you lock the row when you perform the read.
Another type of locking you could use is called optimistic locking. In this case, you handle the conflict in the UPDATE statement, that is, when you perform the write. Suppose you have performed a query, saved a read-only local copy of the data, presented the data to a user, and allowed the user to make changes. Now it is time to update the row:

When you attempt this update, if some other transaction has changed some of the data you are trying to change, the UPDATE will fail and you know that you have to deal with the situation where another transaction has changed the data between your read and your write. If the update succeeds, then there has been no change between your read and your write. In optimistic locking, you lock the data when it is time for the write, and you check the data to ensure it has not changed.
KMukhar
http://home.earthlink.net/~kmukhar/
[ November 18, 2002: Message edited by: Kevin Mukhar ]
 
Reinhold Gruber
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Kevin!
Now I can figure out how to do optimistic locking with JDBC. Obviously there must be some kind of TimeStamp in each table which can be used for versioning. And you could detect a conflict with:

Could you please bring to light pessimistic locking with JDBC. How can I achieve the locking of a record before/after I have read it without using proprietary sql-extensions? I am still wondering wheter a database transaction with the appropriate isolation level would do the job.
Reinhold
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Reinhold Gruber:

Could you please bring to light pessimistic locking with JDBC. How can I achieve the locking of a record before/after I have read it without using proprietary sql-extensions? I am still wondering wheter a database transaction with the appropriate isolation level would do the job.


Despite my implication that FOR UPDATE is Oracle syntax, it appears to be part of the SQL92 standard. See http://www.contrib.andrew.cmu.edu/%7Eshadow/sql/sql2bnf.aug92.txt. Of course, if you are designing for a specific implementation, you can check this with the database documentation. I checked, and it is valid for Oracle, MySql, PostgreSQL, and Sybase.
Although transaction levels are related to locking, they are separate features. A database can use locking to provide a particular transaction isolation level, but having a given transaction level or using transactions in your code does not say anything about how the database, table, or row may be locked. A transaction simply guarantees that if part of the transaction fails, you can rollback the entire transaction. It will not guarantee that anyone else is prevented from updating the database at the same time.
reply
    Bookmark Topic Watch Topic
  • New Topic