Locking with JDBC

[Reinhold Gruber]

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?

java.sql.Connection con = DriverManager.getConnection(someDbURL);
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ );

Any thoughts appreciated!
Reinhold

[Mark Spritzler]

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

[Mag Hoehme]

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:

User 1:
SELECT * FROM my_table;
User 2:
UPDATE my_table SET ... WHERE ...

Hi Mark,
I think the topic belongs to the SCJD forum because it is about the nature of locking.

[Reinhold Gruber]

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]

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]

Hi Mag!
Ok here we go

String select = "SELECT * FROM tb_flight where id=" + someID;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(select);
rs.next();
int availableSeats = rs.getInt("seats");
//calculate the new number of seats
int seatsAfterBooking = availableSeats-seatsToBook;
String update = "UPDATE tb_flight SET seats=" +seatsAfterBooking+ " WHERE id=" +someID;
stmt.executeUpdate(update);

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]

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]

Hi Reinhold,
I agree with you. I'd say here you need the same kind of LockManager as required for the SCJD.

[Kevin Mukhar]

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:

UPDATE table
   SET col1=someNewValue1, col2='someNewValue2'
 WHERE tableId = id AND col1=oldValue1 AND
       col2=oldValue2

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]

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:

int updatedRecords = stmt.executeUpdate(updateSQL);
if(updatedRecords==0) {
//write code which justifies my optimism 
}

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]

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.