At 09:10 AM 18/02/2006, you wrote:

>I have a table with a trigger that updates a parent on insert/update.
>basically keeps a sum of values in the children.
>
>the parent row is assigned to a particular user. therefore, no 2 users
>are ever inserting details that cause the same parent row to be updated.
>
>I began getting lock conflicts on occasion. so, I made the triggers
>that update the parent rows inactive.
>
>Yet, today, I actually had on of the parent rows locked doing an update
>myself on the parent row. The problem is that I still got a lock
>conflict even though the triggers that update the parent row on insert
>of the child row was rendered inactive.
>
>I concluded the edit on the parent row and the transaction. I retried
>the child insert from the other client, and it went through fine.
>
>Is this possible?

I just love these postings with subjects like "possible bug"...

In sum, RI is a package of mechanisms that protect data from anyone
doing anything that will cause relationships to be broken. It's the
backbone of the "C" in "ACID". There are triggers there that you
didn't create and you can't disable them without dropping the
constraints that they were created to enforce.

As soon as you post an update to the primary record of a
relationship, that row is "locked" (denied to other transactions for
writes) and any dependent records become "locked" also.

You seem to have some mechanism of your own there which you refer to
as "assigning the parent row to a particular user". I don't know how
you do that; but you won't thereby override the restrictions that
are enforced by the foreign key relationship. It's not a "possible
bug" but a deliberate, SQL-standard-compliant implementation.

With regard to your custom trigger, if your "assignment" mechanism
really works (presumably a dummy update of the parent record that's
posted when your client goes into "edit mode"?) then you won't get
lock conflicts when updating the parent from the trigger on the
client record. So, the conclusion to draw here is that your
assignment mechanism is faulty.

It would help if you would describe this assignment mechanism...

What you should bear in mind is that, while a pending update on the
primary record will cause the dependent records to be locked, the
reverse is not true.

So, for example, if your application is operating on a set of child
records that is fetched outside the context of the parent, the RI
constraints on the child table will prevent updates that violate the
relationship, but the parent record is available to any other
transaction. As soon as that trigger tried to update the parent
record (NOT a good design strategy!!) it would either succeed and get
a lock, or fail and get a lock conflict (because the other
transaction already had a prior pending update on the parent
record). In the latter case, of course the other transaction would
try to lock the child records and you have a potential deadlock situation.

What actually happens under the hood will very much depend on the
transaction settings (isolation, wait/no wait, and (if isolation is
Read Committed) the [no] record version setting.

>FB 1.5
>
>What could be going on?

Amongst other things, I think you might be getting confused between
"Edit" (a client-side concept) and UPDATE (a request to the
server). While your application user is "editing", s/he is not doing
anything on the server, simply changing values in the local
buffer. The actual UPDATE request happens on "post" (whatever your
interface layer does to submit a DML request) and that's the point at
which the client will first know about a conflict.

Consider abandoning this design "feature" where children update
parents. If you need to deliver a set that contains a parent view of
the totals of currently committed child values, return it to your
application via a COMPUTED BY expression; or define a view that
returns that set; or define the computed column in the parent
structure itself.

AVOID writing application code that isolates child records from
parents and allows child records to be updated out of context. For
example, replace

select [parentID, childID, blah, blahh, ...] from ChildTable
where parentID = [whatever]

with

select [p. parentID, c.childID, c.blah, c.blahh, ...] from ParentTable p
join ChildTable c
on c.parentID = p.parentID
where p.parentID = [whatever]

./heLen