Thank you Doug for your response...

The idea of a time stamp is ok? But has problems based on real world
scenario. On the corner case? If the time clocks between multiple
computers are out of sync? Then you have the risk of the wrong bidder
being posted as the winner. That is a problem.

Let me see if I can clarify a couple of things. First? This is for
benefit auctions, not commercial. (Big difference) The auctioneer is
no where near the computer system. In fact we do not want them near
the computer! ;) Data entry is being done by multiple computers and
volunteers. All entries being done are from recording (bid) sheets.

The corner case needed to be handled is this. If two data entry people
for some reason have recording sheets that has the same catalog
number, different bid numbers, they enter the bid information and hit
the <ENTER> key at the same time, both bids get posted. This is what
we are trying to prevent. If this process is done, say a split second
apart, The second data entry person is going to be told that the item
is sold. (If the wrong person got the item then obviously they would
need to correct the entry error. That is expected in any data entry
environment.)

This is the puzzle we are attempting to solve, trying to take
advantage of the design specifications of Firebird. If data entry was
only being done by one person then this would not be of issue.

Any other thoughts?



--- In IBObjects@yahoogroups.com, Doug Chamberlin <dougchamberlin@...>
wrote:

>
> olgreyfox15 wrote:
> > You had questions as to my intent, and I admit, I didn't do the most
> > thorough job of explaining my problem. The scenario is two or more
> > users simultaneously entering item bids at a charity/benefit auction
> > (not an online auction). Normally, before posting the entry, the
> > application (in an "IsSold" function) checks AT_STATEMENT to see if
> > the given item is already in the table. In single user mode, or if
> > people are doing their entries with some time between, this is no
> > problem. But in the boundary case in which two users hit "Enter"
> > simultaneously, it has been possible to enter the same item twice.
> > This is what I'm trying to prevent. So, in this boundary case (which
> > admittedly should be rare but is still possible, given Murphy's Law),
> > if User A and User B both input the same item and then simultaneously
> > hit "Enter", someone (say User A) gets the Insert/ExecSQL first and
> > posts a record. User B's entry, if it doesn't (somehow) wait for User
> > A's transaction to complete and then do an "IsSold" query, can
> > blithely enter a second record, effectively "selling" the same item
> > twice.
>
> I think if you re-work the problem with different logic you can
> eliminate the need for locking the table.
>
> For instance, instead of checking if the item has been sold *before*
> posting a bid, try checking *after* the bid is posted whether it was
> successful and then returning that info to the bidder. Here's what I

mean:

>
> Allow posting of bids at any time (that is, inserting of record into

the

> bid table). With the bid record there should be a timestamp showing the
> time of the bid. Only bids that were posted before an item is sold will
> be honored. (I would have a trigger fill this value in on the server.)
>
> The for sale items are in a list (table) with another timestamp

field in

> that list that show the time the item was sold. If not yet sold it is
> NULL. The auctioneer is the only one allowed to update that field and
> when it gets updated, and the update transaction commits, that marks

the

> time the item was sold.
>
> The client program (run by the bidder) posts a bid to a bid table.

After

> that insert transaction commits, the program then IMMEDIATELY queries
> the item table for the timestamp showing when the item was sold. If it
> comes back NULL then the program tells the bidder their bid was
> accepted. If it comes back with a timestamp then the program compares
> that time with the timestamp in the bid record just posted. If the sold
> timestamp is earlier than the bid timestamp, the bid failed and the
> program tells the bidder that their bid did not get submitted in time.
> If the bid timestamp is earlier then it was accepted.
>
> No table locks. No record conflicts. No worries.
>