Same thought, slightly modified.

Rather than a timestamp, you could use a generator and triggers similar
to (I'm lousy at writing triggers, so there will be errors):

CREATE TRIGGER MyTrigger1 ON MyBidTable BEFORE INSERT AS
BEGIN
if (NEW.ID is NULL) then
NEW.ID = GEN_ID(MyGenerator, 1);
END;

CREATE TRIGGER MyTrigger2 ON MyBidTable AFTER INSERT AS
BEGIN
UPDATE MyItems MI
SET MI.SoldTo = NEW.ID
WHERE MI.SoldTo IS NULL
AND MI.ITEM = NEW.ITEM
END;

Generators assigned using GEN_ID(<generatorname>, 1) guarantees that the
number will only be used once, and in case of things actually happening
simultaneously from different transactions (a rare case if your
transactions are short), one of them should get an error since Firebird
do block one writer if two transactions tries to update the same thing
simultaneously (I think you through transaction settings can make one
transaction wait for the other one to finish, but I don't think it will
be possible to update anything without being able to see the most
current version of a record, and hence, MI.SoldTo should no longer be NULL).

With a tiny gap, the second transaction shouldn't find a record at all
and do no update to the MyItems table.

All that would be left for you to do, would be to check that the record
you inserted into MyBidTable had the same ID as MyItems.SoldTo. You
should of course take advantage of IBO's GeneratorLinks rather than rely
on the trigger to assign the MyBidTable.ID.

HTH,
Set

olgreyfox15 wrote:

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