File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Locking with JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Locking with JDBC" Watch "Locking with JDBC" New topic
Author

Locking with JDBC

Reinhold Gruber
Greenhorn

Joined: Sep 20, 2002
Posts: 12
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
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mag Hoehme
Ranch Hand

Joined: Apr 07, 2002
Posts: 194
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.


Mag
Reinhold Gruber
Greenhorn

Joined: Sep 20, 2002
Posts: 12
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

Joined: Apr 07, 2002
Posts: 194
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

Joined: Sep 20, 2002
Posts: 12
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
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

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 ]

Regards, Rene Larsen
Dropbox Invite
Mag Hoehme
Ranch Hand

Joined: Apr 07, 2002
Posts: 194
Hi Reinhold,
I agree with you. I'd say here you need the same kind of LockManager as required for the SCJD.
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
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

Joined: Sep 20, 2002
Posts: 12
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

Joined: Nov 28, 2000
Posts: 83
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Locking with JDBC