Subject Re: [IBO] A Transaction Problem - Lock, Deadlock!?!?
Author Helen Borrie
At 09:42 AM 3/06/2005 -0300, you wrote:
>Hi all..
>I need a little help... look this:
>I have two different programs. Each program have your own
>TIB_Transaction ( both isolation level is tiCommited) .

>In program "A",
>I leave the ibo control my transaction... but in program "B" I have a
>explict transaction and I start my transaction, commit or rollback
>mannually... the program "B" is locking records and I can't work with
>this records in program "A". Of course, this is normal thing if the B is
>change records, right? but not... B is only read records...
>Leave me explain better... in B, the same TABLE that I use in A is
>joined with other table... all this in a TIB_query. When a edit a a
>record in this IB_query (with my transaction started) the record is
>locking and i dont can use it in A... understood? my table is only
>joined in B... i edit another table in B and not the table that i need
>use in A... why this lock the register?

In Set A, you have some rows containing some columns from TableX.
In Set B, you have some rows containing some columns from TableX and some
columns from TableY.

There is a relationship between Table Y and Table X - a foreign key in
Table Y that points to the primary key of Table X?

If I guess right about the relationship, then the overlapping Set A rows
are locked because Set B has posted updates to rows that depend on them.
You can thank the database engine's referential integrity safeguards for this.

>How to made for not lock????

The lock is a *needed* part of the relational infrastructure. It protects
the state of the database globally, whilst ensuring that multiple
transactions each have a consistent view. The kind of exception you are
seeing is not a deadlock, it is a lock conflict - the db engine will not
permit you to write any change that will break a relationship. You can
catch this exception and send the user a friendly message. :-)

If you want Set A to WAIT for Set B's transaction to commit and then try to
do its update, then configure the transaction with LockWait true. If you
do this then you MUST make sure that the work in Set B is committed quickly.

LockWait is not very nice, since it risks deadlocks. It is hard to program
your way out of deadlock conditions when using datasets. In general, a
master-detail structure is logically a better model than an updatable
joined set for updating dependent relations. It is also more friendly for