--- In firebird-support@yahoogroups.com, Mitch Peek <mitchp@h...>
wrote:

>
> Getting this on a new system just into beta.
>
> Here is the scenario...
>
> Retail type application... in only one location... small location,

4 POS

> clients, one back office app, 1 communication client sending

changed

> data back to through a middle tier app at a remote location.
>
> FB 1.5 Superserver
> plenty of hardware on XP in all cases at beta site.
>
> POS app collects information about items, pmt, etc in in-memory
> structures from information queried from the db . then, in a

separate

> transaction component and all at once, writes the transaction row,
> transaction detail rows for line items, payment rows, (all related

to

> the transaction table). The transaction detail rows have a trigger

on

> insert update and delete (although only inserts are done) that

update a

> total in the transaction record. This in turn, updates a record in

a

> table for the "Cash drawer (till)" via a trigger.

"Lock conflict on no wait transaction" is not an error. It is an
exception that is raised when a transaction tries to update or delete
a record that has been changed by another transaction since the
transaction was started.

Hmmm, are multiple transactions trying to update the same cash draw
record possibly?

>
> after 2 weeks of no problems, today, had to stations get repeated

Lock

> conflict errors.
>
> My concern, is that while there is no possible way for two stations

to

> be updating the same "row" at the same time, that the lock conflict
> occurs on a page level. Am I correct in this conclusion... that

even

> though no two records could be updated by two different users, that

the

> lock conflict is still possible if the two distinct rows happen to

be on

> the same page?

No, it is two records that cause this.

>
> If so, making page size smaller would reduce but not eliminate

this,

> correct? (not to mention the other down sides).
>

Nope

> The only clear solution would be to total things up at a set point

in

> time...
>
> I don't have a problem doing this with the transact to trigger

till, but

> the transdetail to transact seems extreme... I could total it up

outside

> of the database, I suppose, (in the app, not in triggers) but would
> prefer this to be in the database as a business rule to eliminate

the

> possibility of some other process breaking this business rule.
>
> Ideas?

Let me give you a simple example

item
====
ID, InStock
1, 50

orderItem
===========
ID, OrderID, ItemID, Qty


Lets assume that you have a trigger that reduces the instock count
against each item as it is places on an order.

Tr1 inserts the following record into OrderItem (1,1,1,10). The
trigger on OrderItem runs effectively the following query

update item set InStock = InStock - 10 where ID = 1;

Before Tr1 commits, Tr2 starts and inserts the following record into
OrderItem (2,2,1,10). The trigger attempts to run the following query

update item set InStock = InStock - 10 where ID = 1;

(Of course, Tr2 can not see that InStock is now at 40, it still sees
50. This is where your wait setting on your transaction applies. WAIT
means that Tr2 will pause until Tr1 commit or rollback. If Tr1
commit, then you get a similar exception "Lock conflict on wait
transaction". If Tr1 rollback, then Tr2 will not get an exception.
But you have said NOWAIT which means that Tr2 will immediately and
pessimistically assume Tr1 will commit.

You need to use another methodology for maintaining counts and totals
if they are not serialised. The following link explains how a count
can be made simultaneous transaction safe, it is not too hard to
adjust the concept to total cash draws or inventory counts.

http://groups.yahoo.com/group/firebird-support/message/56457

Adam