Helen Borrie wrote:

>At 09:10 AM 18/02/2006, you wrote:
>
>
>I just love these postings with subjects like "possible bug"...
>
>
>

sorry...

>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.
>
>
>

of course. i am very aware of all of this.

>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;
>

I suppose this was very unclear.
let me try to explain in a more structured manner, yet simplified as
much as possible to the point in question.

CREATE TABLE TILL (
STORE_ID BIGINT NOT NULL ,
ID BIGINT NOT NULL ,
CASHIER_ID BIGINT NOT NULL ,
COMPUTEDBAL NUMERIC(18,2) ,
OUTTIME TIMESTAMP NOT NULL,
INTIME TIMESTAMP,
ENDINGBAL NUMERIC(18,2),
TAX_TOTAL NUMERIC(18,2)
);

ALTER TABLE TILL ADD CONSTRAINT PK_TILL PRIMARY KEY (STORE_ID, ID);
ALTER TABLE TILL ADD CONSTRAINT FK_TILL_1 FOREIGN KEY (STORE_ID)
REFERENCES STORE (ID);
ALTER TABLE TILL ADD CONSTRAINT FK_TILL_3 FOREIGN KEY (STORE_ID,
CASHIER_ID) REFERENCES PERSON2TYPE (STORE_ID, ID);

What I meant by assigning this row to a particular user... is that
application will assign a Till.ID to a user (Cashier_ID). the
application is designed such that only a single user will by creating
rows that reference the "Till" assigned to that user.

CREATE TABLE TRANSACT (
STORE_ID BIGINT NOT NULL,
ID BIGINT NOT NULL ,
TILL_ID BIGINT NOT NULL ,
AMOUNT_TENDERED NUMERIC(18,2) NOT NULL ,
ITEM_TOTAL NUMERIC(18,2) NOT NULL ,
TAX NUMERIC(18,2) NOT NULL
);

ALTER TABLE TRANSACT ADD CONSTRAINT PK_TRANSACT PRIMARY KEY (STORE_ID, ID);

ALTER TABLE TRANSACT ADD CONSTRAINT FK_TRANSACT_2 FOREIGN KEY (STORE_ID,
TILL_ID) REFERENCES TILL (STORE_ID, ID);
ALTER TABLE TRANSACT ADD CONSTRAINT FK_TRANSACT_3 FOREIGN KEY (STORE_ID)
REFERENCES STORE (ID);

>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.
>
>

not sure we are on the same wavelength. I think you misunderstood what
i was trying to convey.

The user simply inserts "transact" rows with Till_ID having a set value
for the day.

>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.
>
>
>

Here is the trigger that I made inactive in the table TRANSACT

CREATE TRIGGER T_TRANSACT_COMPUTEDBAL FOR TRANSACT
INACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
declare variable Atsum numeric(18,2);
declare variable Taxsum numeric(18,2);
begin
/* calculate computedBal for till */
Select Sum(Amount_Tendered), Sum(Tax)
from Transact
where store_ID=new.Store_ID
and Till_ID=new.Till_Id
into :ATSum, TaxSum;

Update Till
set ComputedBal=:AtSum+StartBal,
Tax_Total=:Taxsum
where Store_ID=new.Store_Id
and ID=new.Till_ID;

end

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

hope I have done this adequately. It isn't a mechansim so much as a
design of the system.

>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.
>
>
>

Yes. my point, is that even with the trigger enabled... unless I had
the primary row in an edit state, there should be no problem ( which
should never occur, as only a single user could be updating the till row
via the trigger ), yet, I still got lock conflicts. For this reason,
and the fact that it simply isn't a very good design for several
reasons, performance not withstanding, I made the trigger inactive.
Yet, I still get occasional lock conflicts.

So, I ran a test... Still with the trigger disabled (inactive as
evidenced by the DDL given above). I then purposefully locked a "till"
row by editing it but not committing the transaction. I then had a
client to insert a transaction record that points to that row, and it
failed with a lock conflict.

this just should not be... nothing is trying to update the till row
(with the trigger disabled) by a simple insert on Transact.

>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.
>
>
>

This isn't exactly what s happenning. BTW, my transaction isolation is..

read
write
read_committed
rec_version
nowait

>Amongst other things, I think you might be getting confused between
>"Edit" (a client-side concept) and UPDATE (a request to the
>server).
>

No, despite my idiotic attempts at explanation. I am much farther along
then it seems you are imagining... I have been successfully working with
IB/FB since ver 4.something or maybe even ver 3. something... suffice
to say, longer then I want to admit.

Actually, there are no client side "edits" nor server side updates, the
client only inserts. Period. except, historically, via the now defunct
trigger. But that was weeks ago that I abandoned that dumb idea.

In my test. I actually did run an update statement on the Till row...
update till, set ID=ID where ID=<someID> to purposefully lock the till
row.

My point is that an insert into Transact with Till_D=<SomeID> somehow
failed. It is here where I suspect some sort of bug ( with the trigger
still in place, yet, inactive. No, the sums are not accumulating, it
doesn't appear that the record is being updated, but something clearly
wants to lock the till record. ?/?

>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.
>

already have...

>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]
>
>
>

Again, this isn't the issue. The client needn't read any of its
previous transactions. Only at the outset does it need to read the till
record, and only to get the valid ID number for all subsequent inserts.

Which brings up another anomaly I have seen ( left for another post) I
promise to be very clear with it :)

Helen, I do appreciate you taking time to answer in such detail. And,
I'm sorry my first post was so unclear. Hope this is more
representative of what is going on.


[Non-text portions of this message have been removed]