Avoid duplicate records in a particular Mysql column

Question

I have an auction system where several users can bid, but I have a problem when bids occur at the same time, as they are entered in the database with the same value in the column bidding_price, ex:

0.02
0.02
0.02

I set up a select that returns all bids with equal values (duplicity) for a given auction:

SELECT bidding_price, count(*)
    FROM bid_account
    WHERE auction_id = 1335
    GROUP BY bidding_price
    Having Count(*) > 1

In this case it will return the duplicities to the auction with ID 1335.

The structure of the table bid_account this way:

id | int(255) - índice - primária - AUTO INCREMENT
auction_id | int(11) - id do leilão
bidding_price | float(20,2) - valor do lance, ex. 0.02

I came to create a DELETE that can erase the duplicities, however it would be unfair to have the bid deleted by a system error. Follows:

DELETE ba
    FROM bid_account ba JOIN
         (SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id
          FROM bid_account ba2
          WHERE ba2.auction_id = ?
          GROUP BY ba2.auction_id, ba2.bidding_price
         ) ba2
         ON ba2.auction_id = ba.auction_id AND
            ba2.bidding_price = ba.bidding_price AND
            ba2.max_id > ba.id
WHERE ba.auction_id = ?;

This with ? there why it receives the value by a parameter.

Does anyone have any idea how I can solve this problem by preventing the equal value from being inserted in the table bidding_price, but for a given auction identified by the column auction_id?

In the table there are several equal values in bidding_price, but what cannot occur is equal values for the same auction, that is, for the same value in auction_id.

I thought of creating an update, which checks before if there are duplicities, if there is it updates the values by inserting 1 cent more in each duplicity. But I think I’d go from trouble when there were three moves at the same time.

This SQL command will be executed in Node, and will run in a cron every second, that is, every moment it will be performing this check. If there is a duplicate bid now, in the next second it will identify and correct.

The bidding system works in conjunction with a 15-second countdown timer, whenever someone bids the stopwatch back to its initial score of 15s.

Answer 1

All you have to do is use the UNIQUE, such a form that:

ALTER TABLE tabela ADD UNIQUE INDEX(`bidding_price`, `auction_id`);

Thus the pair of bidding_price and auction_id are unique, so if it is {0, 0} there can be no other, but there can still be {0, 1} or {1, 0}. Then for each auction_id only one will exist bidding_price.

You can test this on Sqlfiddle.

Answer 2

You can check in SQL if there is a bid with the same value using CASE:

CASE  
    WHEN ? IN (SELECT bidding_price FROM bid_account WHERE auction_id = ?) 
    THEN INSERT ...

Answer 3

What matters is who gives the commit first. And that never happens at the same time, within milliseconds. What happens is that you need to increase the storage cache of mysql’s Insert to create a queue. After that, each new request should load the client page or at least play the updated data with the timestamp in a json file and modify the div (or any other tag) dynamically and only the part of the price. You can do this with a Rest or ajax api.