php - Avoiding Double Booking / Double Spending

Solution:

This might work: change the rule for winning an auction.

Now, I guess, a user wins an auction if she's the highest bidder at the conclusion of the auction. You can change the rule so the user must BOTH be the high bidder AND have sufficient budget to cover the bid. If both criteria aren't met, the next highest bidder with enough budget wins.

This is easy to explain to users. "to win, you need enough in your budget. If you're losing auctions you can increase your budget."

Implementation wise, you can use a database transaction to handle the "win" operation. Inside a single database transaction, debit the winning buyer's budget and credit the seller's account.

You can use SQL sequences like this:

START TRANSACTION;
SELECT budget 
  FROM user 
 WHERE id = nnnn 
   AND budget >= bid
   FOR UPDATE;
/* if you got a result do this */
UPDATE user SET budget=budget-bid 
 WHERE id= nnnn;
UPDATE seller SET balance=balance+bid 
 WHERE ID = sssss;
UPDATE auction
   SET winner=nnnn, winning_bid=bid,
       closetime=NOW()
 WHERE auction = aaaa;
COMMIT;
/* if you got no result from the SELECT do this */
ROLLBACK;

Solution:

You can use distributed locks to solve the double-spending /double booking issue. You need to acquire at least three locks that would update

• User's budget i.e. bidder's account balance
• Seller's account balance
• Auction

You can also create a bid ledger to verify the user's spending, seller account balance, and user's wallet balance. You can run a cron job hourly or every 10 minutes to verify any error and notify support/dev to check for potential errors.

Your logic could be like this

Procedure BidProcessor
   input: user, bid, seller
   // this should get locks for auction, user wallet balance and seller balance
   success = acquireLocks( bid.auction_id, user.id, seller.id )
   if success then
      hasSufficientFund = user.hasSufficientFund( bid.amount )
      if hasSufficientFund then
          ExecuteBid( user, bid, seller )
          releaseLocks( bid.auction_id, user.id, seller.id )
      else 
          releaseLocks( bid.auction_id, user.id, seller.id )
      endif
   else
      releaseLocks( bid.auction_id, user.id, seller.id )  
   endif

Each executor can use their identity id as a lock value to avoid releasing someone else lock.

Procedure ExecuteBid
    input: user, bid, seller
    Description: Run a SQL transaction to update all related entities 
      START TRANSACTION;
        UPDATE user SET budget=budget-bidAmount WHERE id= user.id;
        UPDATE seller SET balance=balance+bidAmount WHERE ID = seller.id;
        UPDATE auction SET winner=bid.id, closed_at=NOW() WHERE id = bid.auction_id;
        UPDATE bid SET processed_at = NOW(), status='WON' WHERE id = bid.id;
      COMMIT;
      if commit fails then do a rollback  
        ROLLBACK;