Subject foreing key locking
Author s_lutti

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.

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