At 12:03 PM 18/02/2006, you wrote:

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

How do you enforce that? Are you instantiating a never-used-before
"Till" entity each time a new cashier logs into the POS terminal?

>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);
>
>not sure we are on the same wavelength. I think you misunderstood what
>i was trying to convey.

I'm sure you're right.

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

What does that mean? A unique, brand-new till record created for
each cashier log-in?

> >
>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's a Good Thing (TM) that you have abandoned this code.

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

Erm, see my question above, asked twice.

I see dragons in the way you have (unnecessarily) cross-related the
relationships between Store, Till and Transac. Is it a fair guess
that you inherited this database from Paradox? Get rid of the
redundancies in those downstream tables, and eliminate the
cross-relationships, viz.

Till.ID should be the PK of Till
Store_ID should be a FK to Store
Transac.ID should be the PK of Transac
Till_ID should be the FK to Till
Store_ID is redundant, since the relationship between Transac
and Store is already implemented in Till.

This lack of normalisation (a.k.a. Bird's Nest or Worm Farm) is also
the root cause of your later problem with FK violations.

I think there's also something you're not telling us about
Cashier. I have a horrible feeling there is a bird's nest in its
relationships, too.

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

Ah, we don't yet seem to have a distinction between "Edit state"
(which only the application knows about) and "pending update" (which
both the application and the server know about). The application
can't detect lock conflicts until the server knows what's
requested. That won't happen at all while the application has the
record in "edit state"....

UNLESS...

you are using a pessimistic locking strategy, as you can with some
Delphi components. Under the hood, this causes a dummy update to be
posted to the database and, of course, fires off all the update triggers.

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

Sure you do. Another till that's child to the same store could be
doing something. You have direct dependencies to Store all the way
down the chain.

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

No. But you are attempting to *Insert* a row in Read Committed
isolation and No Wait locking policy. This always going to cause a
conflict - it's one of the few places in Fb/IB where you have a
blocking situation. If an insert is pending that could affects a
relationship, any attempt to do another insert affecting the same
relationship will fail as a conflict.

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

It's not an engine bug - it's doing what it's supposed to do. An
inactive trigger is not active. If you fix up the relationships
between these records then each transaction will involve only the
till that it belongs to. One cashier at one till can then fire off
as many inserts as s/he needs to, without those redundant keys
getting in the way.

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

Can't quite see where this fits in (maybe a propos your conjecture
that somehow the inactive trigger was really active...? can't tell...)

I wrote:

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

I see that.

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

Fair enough. At the point where the Store_ID and the Till_ID have to
be known to the application, acquire those values and put them in a property.

./heLen