At 04:24 PM 8/01/2010, you wrote:

>Hi,
>
>I'v searched this newsgroup cause of the follwoing issue:
>
>I have a table BAS_USERS with all users in it. The primary key is a field called ID. Then I have a second table MOV_CREDITS with a field USERID. This field has a foreignkey constraint to BAS_USER.ID. My problem comes in, when two or more users are working with the same database. User A has opened a record from BAS_USERS with the ID = 4. The dataset is in dsEdit cause he's editing the lastname for example. Now when user B tries to post a new record to MOV_CREDITS with MOV_CREDITS.USERID = 4 I get the typical "lock conflict on no wait transaction". I know that the dbms needs to do so, cause user A could change the value for BAS_USERS.ID at the same time user B tries to store the old value to the foreign key field.
>
>As this is already posted by others I could not find a good workaround for this problem. My database has about 100 tables all with frequently using of foreign keys. So before I can commit an transaction I would have to check all foreign key fields of the table I want to post a record to. That can be a bit cumbersome and I can not make this behavior transparent for the user.

Certainly this is NOT a way to deal with it. The lock conflict is just that - a conflict. It will be resolved when the transaction that has the lock commits that work.

>So how do you handle such conflicts? Is there any smart way of handling such a case?

The usual way is to intercept the exception and inform the user that the operation cannot proceed at this time. You could give the user the option to abandon his changes; or give him a retry button if you are confident that the operation will succeed eventually; or place a retry loop in your code with a pause; or put the transaction in WAIT mode if you know that the competing transaction will end quickly.

Many developers use one or another form of pessimistic locking to enable a transaction to "grab a lock" and pre-empt any other transactions from updating inter-dependent records. The [[for update][with lock]] construction is available and is practicable as long as the application only wants to work with one record and its dependencies. It should *not* be used for multi-row sets.

Another technique to achieve a pessimistic lock on one record and its dependencies is the "dummy update". Once the user has selected the record of interest, the application immediately posts (but does not commit) an "update" that merely sets the primary key to its current value. If that succeeds, then no other transaction can perform that (or any other update) to that record and/or its dependencies. The user that has the lock can now proceed to do his edits and post and commit his work with no likelihood of conflict.

Normally, though, where the potential for conflict is not great, you won't want to use the pessimistic lock.

./heLen