Row-Level Locking

Hello!

I'm new to DB2 and I have a locking problem: I am writing a Java client that uses a DB2 backend which is accessed via JDO.

What I want to do is to lock a row for read and write access. I.E. Client_1 locks a row, reads the row and updates some data. While Client_1 has the lock, no other client should be able to read or change the row. Since the JDO locking methods failed, I would like to do it using SQL statements directly.

The first thing I tried was a "select id from some_table where id=some_value for update" statement, but I got a JDO error message, saying that the clause "for update" was used for a read-only cursor.

Any ideas, what I have to do to acquire and release the lock?

I will address this from a Java perspective since that is what you are using. You need to start a Transaction in the java database Connection class. See the setAutoCommit() method. You can set the Transaction Isolation Level in your Connection object when you connect to DB2. Please read the setTransactionIsolation() method description.

Note that, I believe this will 'lock' the record of that table for the row you are reading, but only for other connections that have started a transaction. Regular connections started without a transaction, from how I understand it, will still happily read your locked row, but should not be able to update it. There is no way, from how I understand it, to lock a row in a table so that all other connections cannot read it if those connections are not in a transaction.


EDIT: if you are using something like Apache JDO, then you will have to see the documentation for that API to help you understand how to do the things I mentioned in my post.

Helllo Nemi!

Thank you for your reply. I have tried what you suggested but it did not work. As it turned out this is due to a bug in the JDO implementation in connection with a DB2 backend.

I solved the problem by implementing my own locking mechanism.