Subject Re: [firebird-support] foreing key locking
Author André Knappstein, Controlling
I think that whatever method you are using to access the data and
whatever programming language, you should be able to load the data
with a read-only transaction. Start a new r/w transaction if the user
really finally _is_ doing an update.

Keeping transactions open until whenever a user probabyl might decide
to do some changes never is a good idea.

If your tools are supporting the display of stateless data
(disconnected from the server) you can also commit or autocommit
transactions after each select.



~~~Ihre Nachricht~~~

s> Hi,

s> I'v searched this newsgroup cause of the follwoing issue:

s> I have a table BAS_USERS with all users in it. The primary key is
s> a field called ID. Then I have a second table MOV_CREDITS with a
s> field USERID. This field has a foreignkey constraint to
s> BAS_USER.ID. My problem comes in, when two or more users are
s> working with the same database. User A has opened a record from
s> BAS_USERS with the ID = 4. The dataset is in dsEdit cause he's
s> editing the lastname for example. Now when user B tries to post a
s> new record to MOV_CREDITS with MOV_CREDITS.USERID = 4 I get the
s> typical "lock conflict on no wait transaction". I know that the
s> dbms needs to do so, cause user A could change the value for
s> BAS_USERS.ID at the same time user B tries to store the old value to the foreign key field.

s> As this is already posted by others I could not find a good
s> workaround for this problem. My database has about 100 tables all
s> with frequently using of foreign keys. So before I can commit an
s> transaction I would have to check all foreign key fields of the
s> table I want to post a record to. That can be a bit cumbersome and
s> I can not make this behavior transparent for the user.

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

s> ------------------------------------

s> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

s> Visit and click the Resources item
s> on the main (top) menu. Try Knowledgebase and FAQ links !

s> Also search the knowledgebases at

s> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
s> Yahoo! Groups Links