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.