Pessimistical Locking - Hold Connection

[semasaba]

Hi all,
i'm using pessimistical locking therefore it's necessary to hold my connection for my whole update process.

Here my scenario:
* Screen with some datasets
* User is choosing one dataset and press update
* Lock dataset and hold connection
* Update screen where the user can fill some datas
* User can press save during his data entry
-> Here is my problem:
The save method should stay under transaction.
If there is already a open connection, DataSourceTransactionManager is creating a new connection. For pessimistical locking reasons it's necessary that the TransactionManager is using the same connection for the commit because otherwise he gets a lock exception of couse. It's only possible to update a locked dataset with the same connection.
* After the save method when the TransactionManager has executed the commit the lock is released.
* Re-Lock the dataset


I hope I was able to explain my problem.
Has anybody an idea how to manage this scenario?

Hint:
I'm using:
org.apache.commons.dbcp.BasicDataSource
org.springframework.jdbc.datasource.DataSourceTran sactionManager
org.springframework.jdbc.datasource.TransactionAwa reDataSourceProxy


Greetings
Marco

[Alarmnummer]

The big question you need to ask is: do I really want that connection open for such a long period. It could influence the performance of your system (what if the user goes to lunch before pressing the submit button). And another issue is that the database eventually is going to roll back your transaction if it takes too long.

What you are doing is pessimistic 'online' locking, and what you want is pessimistic 'offline' locking. This can be done by adding a 'locked_by' column to the table and set the value to locked as soon as the data is checked out (this in itself is also a concurrency problem and there are big differences in concurrency control implementations between database, so we need to know which db you are using).

After the value is set, the transaction can be committed and 'discarded'. As soon as the user presses the submit button, the locked_by column needs to checked to make sure that the current user is the owner and then the changes can be written. This part in itself is a new concurrency problem.

Another think you need to watch out for with pessimistic offline locking is releasing the lock eventually. If the user never presses the submit button, the lockedby field is not updated, so nobody is able to use that record. This can be fixed by adding a background process that releases all locks if they have been held longer than a certain period.

Optimistic offline locking is easier to implement (hibernate provides out of the box support for this btw). But even if you can't make use of hibernate, it still is a lot easier to implement than the pessimistic offline alternative.

The problem with optimistic offline locking is: how are you going to deal with a situation where your optimism that no other user is going to change that data was violated. Error screen? Merge?

So the questions are:
which db are you using
is optimistic offline locking not a better alternative.

[Paul Newport]

Hi all,
i'm using pessimistical locking therefore it's necessary to hold my connection for my whole update process.

Just use optimistic locking. Pessimistic locking causes far more problems than it ever solves.

[semasaba]

Thank you for your hints.
I really agree with you but I have this requirements from my department.
Reason was that we have other programming languages and technologies in an existing environment. All the 'old' programs should work as usual, therefore I have to use pessimistical locking.

I solved the problem in the meantime.
I have set programmatically at the beginning a transaction
when I get the connection and lock the dataset.

DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.P ROPAGATION_REQUIRED);
status = txManager.getTransaction(def);

So I can ensure that I get everytime the same connection
if I also use Propagation.REQUIRED for my save methods.


@Alarmnummer
We are using DB2.

[Alarmnummer]

Thank you for your hints.
I really agree with you but I have this requirements from my department.

Ok.. assuming that pessimistic locking is needed, the question remains of you really need to online version (so using a long transaction) or if you can get away with multiple shorter ones (offline pessimistic locking). Using a long transaction with a lot of locking is a bad practice.

Reason was that we have other programming languages and technologies in an existing environment. All the 'old' programs should work as usual, therefore I have to use pessimistical locking.

The big problem with long running transactions that use 'online' pessimistic locking, is performance. If a record is locked, every other transaction that wants to access that record is going to block. So providing some kind of overview of all record is going to block because it needs to read a record that is blocked (unless you want to rely on a read uncommitted isolation level).

I solved the problem in the meantime.
I have set programmatically at the beginning a transaction
when I get the connection and lock the dataset.

DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.P ROPAGATION_REQUIRED);
status = txManager.getTransaction(def);

So I can ensure that I get everytime the same connection
if I also use Propagation.REQUIRED for my save methods.

I guess you are using the default isolation level of db2 (Cursor Stability aka Read_Committed). This is not going to fix you problem because data that has been read, is not locked. So if 2 users are going to update the same record, the one that did the last update is going to win (so you have a lost update problem). So this is not the behavior you want to have. You could upgrade the isolation level to Read Stability (aka repeatable read). This is going to lock every record the transaction touches. The problem with the repeatable read is that it could limit concurrency and this could influence performance and cause liveness problems (deadlocks).

So it would be better to only lock the record you really want to lock. So switch back to the repeatable read and use the 'select ... for update'. If you don't want to let the user wait for release of the record, you could set the current lock timeout using:

SET CURRENT LOCK TIMEOUT NULL

For more info see "Task 4: Converting Oracle SQL statements containing NOWAIT"
http://www.ibm.com/developerworks/db...avi/index.html

Oracle has out of the box support for this with the 'select ... for update no wait'. It is a shame the with other databases it is much more cumbersome (e.g. with sql server 2000 you need to fiddle around with table hints).

ps:
I'm not a db2 expert. I think AL0 knows more about this subject.

[semasaba]

Ok, it seems that there's only one problem pending.
I'm using:
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="datasource" />
<property name="defaultTimeout" value="200" />
</bean>

and

DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.P ROPAGATION_REQUIRES_NEW);
def.setIsolationLevel(TransactionDefinition.ISOLAT ION_REPEATABLE_READ);
def.setTimeout(200);

status = txManager.getTransaction(def);


It seems that the default timeout is ~10 seconds and I can't change this value.
Does anybody know why?

[Alarmnummer]

At the moment I'm helping a customer fixing similar issues under sql server 2000 and under db2.

The big problem is that there are a lot of different versions of db2 and that the features really depend on the version.

Some new versions support the 'skipped locked data'. If you combine this with 'select ... for update' I guess you are done. If your db2 version doesn't support this... well.. you have a nice 'challenge'.

Unluckily I have such a challenge. The 'SET CURRENT LOCK TIMEOUT ...' is dangerous because it sets the timeout for all transactions on the db, not just the current one.

ps:
SET CURRENT LOCK TIMEOUT NULL
doesn't set the timeout, but restores the 'locktimeout' database parameter instead.

http://publib.boulder.ibm.com/infoce...n/r0011874.htm

If anyone has a good solution for 'select for update NOWAIT' under db2 without using the 'skipped locked data' option, I would be forever grateful.