Per-auction MySQL locks and idempotent bids: WooCommerce auctions under last-minute load

Most of a WooCommerce store is forgiving about timing. Two shoppers buying the last two mugs at the same second is rare, and WooCommerce's stock handling copes. Auctions are the opposite: the whole point is that people compete for the same item, and the competition peaks in the last minute, when bids arrive within milliseconds of each other and phones on patchy networks resend requests. When we built DEV3 AI Auctions for WooCommerce, we wanted no lost bids, no duplicate bids and no wrong winners, on ordinary WordPress hosting with no queue or Redis. This post explains how a MySQL named lock per auction, a pure bid engine and client request ids get there, and the 100-request test we run to check it.

The live bid panel on an automatic (proxy) auction. The shopper enters a maximum; the plugin bids for them one step at a time.

What it does and who it's for

DEV3 AI Auctions turns any WooCommerce product into an auction, with four types: standard, automatic (proxy) bidding, sealed bids and reverse auctions where the lowest bid wins. It suits:

• Collectibles and second-hand shops selling one-off items such as cameras, watches and guitars.
• Charity and fundraising sites running timed auctions with reserves and buy-now prices.
• B2B buyers running reverse auctions for supply contracts, where suppliers bid the price down.
• Sealed-bid sales where bids stay hidden until the end.
• Marketplaces on Dokan or WCFM where vendors list their own auctions.

Every auction can have a reserve, a buy-now price, fixed or tiered increments, a bidding deposit, a scheduled start and end, a soft close and relisting rules. Winners pay through the normal WooCommerce checkout. AI is optional: listing drafts, price and end-time advice, emails and a fraud second opinion need a Google Gemini API key; everything else works without one.

How to set it up

1. Install the plugin zip from the bundle download and activate it with WooCommerce active.
2. Open DEV3 AI Auctions → Settings and review defaults: auction type, anti-sniping window, bid increments, relisting and fraud scoring.
3. Create a product, choose Auction product as the product type and fill in the Auction tab: type, start price, reserve, buy now, start and end.
4. Add the Auctions block or [dev3au_auctions] to a page, and check WooCommerce → Settings → Emails for the outbid and win emails.
5. Optional: add a Google Gemini API key in DEV3 AI Auctions → API Config.

The Auctions block groups lots that end soon and lots that haven't started yet.

How to use it

Run an auction. Once published, the bid panel shows the current bid, the time left and quick-bid buttons. Bidders add items to a watchlist, and their My Account page lists auctions won (with Pay now), winning, outbid and not won.

Manage it live. In Auctions, each auction's page shows the leader and their maximum, the full bid history with real names and automatic bids, and actions to End now, Pause bidding, Cancel or extend the end time. Remove bidder voids a bidder's bids and recomputes the auction from the bids that remain.

The admin view of a proxy auction: maximums are visible to admins only.

Review suspicious bidding. When a bidder shares a network with the seller, is a brand-new account or keeps answering other bids within a minute, the dashboard shows a flag. Fraud review lists each signal with its points; the admin can dismiss it, pause the auction or remove the bids and block the bidder. Explain with DEV3 AI adds a second opinion based on anonymous signals.

Write listings faster. The AI Studio and the DEV3 AI tools on each auction draft a title, description and condition notes from the product photos, suggest start, reserve and buy-now prices from similar past auctions, and suggest an end time. Every result is a draft to accept or discard.

The race

Here is the naive bid handler, in pseudocode:

1. Read the current price and leader.
2. Check the new bid is at least price + increment.
3. Write the new price and leader.
4. Insert the bid row.

Two bids of $1,900 and $1,925 arrive together. Both requests read $1,875 in step 1, both pass step 2, and the one that writes last in step 3 wins, even if it was the lower bid. Proxy bidding makes it worse: step 2 is really "run the proxy algorithm against the current leader's maximum", which produces one or two rows depending on that maximum. If two requests compute against the same stale leader, the stored history stops making sense.

WordPress offers no transactions around post meta, and wrapping the whole thing in a database transaction with row locks is awkward when the state lives in wp_postmeta and goes through the object cache. We wanted mutual exclusion per auction, with different auctions still bidding in parallel.

A named lock per auction

MySQL and MariaDB have named advisory locks: GET_LOCK(name, timeout) blocks until the lock is free or the timeout passes, and RELEASE_LOCK(name) frees it. They aren't tied to any table, so we can lock "auction 66" without touching anything else:

private static function lock_name( $auction_id ) {
	global $wpdb;
	return substr( $wpdb->prefix . 'dev3au_' . (int) $auction_id, 0, 64 );
}

public static function lock( $auction_id ) {
	global $wpdb;
	$timeout = (int) apply_filters( 'dev3au_lock_timeout', 10 );
	return '1' === (string) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, %d)', self::lock_name( $auction_id ), $timeout ) );
}

(from Auction\Bid_Service)

The table prefix is part of the name, so two WordPress sites sharing one database server don't block each other, and the name is cut to MySQL's 64-character limit. If the lock can't be taken within 10 seconds (the timeout is filterable), the bid fails with a 409 and the message "Many bids are arriving at once. Please try again."; the storefront script retries once after 350 ms. Named locks are released automatically if the connection dies, so a crashed PHP process can't leave an auction locked.

Everything that matters happens under the lock

Bid_Service::place() does the cheap, state-independent checks first (logged in, not the seller, not blocked, not on hold, deposit paid) so that requests that will fail anyway don't queue for the lock. Then:

if ( ! self::lock( $auction->id ) ) {
	return new \WP_Error( 'dev3au_busy', __( 'Many bids are arriving at once. Please try again.', 'dev3-ai-auctions-for-woocommerce' ), array( 'status' => 409 ) );
}
try {
	$auction->refresh();
	if ( '' !== $request_key ) {
		$previous = Bids::by_request( $auction->id, $request_key );
		if ( $previous ) {
			$result              = self::result( $auction, $user_id, (int) $previous['user_id'] === $auction->leader(), false, $now );
			$result['duplicate'] = true;
			return $result;
		}
	}
	if ( ! $auction->is_live( $now ) ) {
		return new \WP_Error( 'dev3au_not_live', /* … */ );
	}
	$compute = Bid_Engine::compute( $auction->state(), $user_id, $amount, array( $auction, 'increment_for' ), $opts );
	if ( ! $compute['ok'] ) {
		return self::engine_error( $compute );
	}
	foreach ( $compute['rows'] as $row ) {
		Bids::insert( /* row + auction, round, request key, hashed IP and user agent */ );
	}
	$auction->save_state( $compute['state'] );
	// Soft close: extend the end if the bid landed inside the window.
} finally {
	self::unlock( $auction->id );
}

(condensed from Bid_Service::place)

Four details are easy to get wrong:

• refresh() first. The auction's state lives in product meta, which WordPress caches. refresh() deletes the auction's post_meta cache entry, so the state is read from the database after the lock is held, not from whatever this request loaded before it waited. With a persistent object cache that delete also goes to the shared cache.
• Liveness is checked under the lock. An auction that ended while this request waited must reject the bid, so is_live() runs after refresh(), not before.
• Soft close in the same critical section. The anti-sniping rule ("a bid in the last 5 minutes extends the end by 3") reads and writes the end time. Doing it under the same lock means two late bids can't both read the old end and extend it from the same point. The number of extensions is capped by a setting.
• finally releases the lock on every path, including early returns and exceptions.

Notifications (dev3au_outbid, dev3au_bid_placed) fire after the lock is released, so sending an email never holds up the next bidder.

The closer that ends auctions every minute (and lazily when someone views an ended auction) takes the same lock before it reads the state, picks the winner and relists. A bid and a close can't interleave.

Retries without double bids

The lock stops two different bids from corrupting each other. It doesn't stop the same bid from being stored twice when a phone resends a request because the first response was slow. For that, the storefront creates a random request id for each bid and sends it with the original and any retry:

const rid = requestId();
const send = ( attempt ) =>
	rest( `auctions/${ id }/bid`, { method: 'POST', nonce, data: { amount, request_id: rid } } ).then( ( res ) => {
		if ( ! res.ok && attempt === 0 && ( res.body.code === 'dev3au_busy' || res.body.code === 'rest_cookie_invalid_nonce' ) ) {
			// wait 350 ms (or refresh the nonce), then send( 1 ) with the same rid
		}
		return res;
	} );

(condensed from src/storefront/live.js)

On the server the id is stored on the bidder's own row, indexed together with the auction id. Because the lookup happens under the lock, a duplicate that arrives while the original is still being processed simply waits, then finds the stored row and returns the current state with "This bid was already received." It never runs the engine. Proxy bids that the engine adds for the other bidder carry no request id, so only the bidder's own row is matched.

A pure engine makes the rest testable

All bidding rules live in Bid_Engine::compute(), which takes a state array and returns a new one plus the rows to store. It knows nothing about the database, the clock or WordPress. That's what lets one function handle four auction types and be tested exhaustively. The proxy branch is the interesting one:

if ( $max > $leader_max ) {
	// Challenger takes the lead one increment above the old maximum.
	$price = min( $max, $leader_max + $inc( $leader_max ) );
	if ( $reserve > 0 && $max >= $reserve && $price < $reserve ) {
		$price = $reserve;
	}
	if ( $leader_max > (float) $s['current'] ) {
		$rows[] = self::row( $leader, $leader_max, $leader_max, 'auto' );
		++$s['bid_count'];
	}
	$rows[] = self::row( $user_id, Auction::round( $price ), $max, 'proxy' );
	// …new leader and maximum…
	return self::ok( $s, $rows, $leader, true, true );
}
// The leader's maximum covers the challenger: the engine answers for the leader.
$price = $max >= $leader_max ? $leader_max : min( $leader_max, $max + $inc( $max ) );

(condensed from Bid_Engine::proxy)

When a challenger beats the leader's maximum, the old leader's automatic bid at their maximum is recorded first, then the challenger's bid one increment above it. When the leader's maximum covers the challenger, the challenger's bid and the leader's automatic answer are both stored. Equal maximums go to the earlier bidder. A reserve price is jumped to as soon as a maximum reaches it.

The same purity powers Bid_Service::rebuild(). When an admin removes a bidder, the service takes the lock, deletes the derived automatic bids for the round, and replays the remaining bids through the engine from the start price, voiding any that are no longer valid. The auction ends up exactly where it would have been if the removed bidder had never taken part.

Removing a shill bidder triggers a rebuild of the auction from the remaining bids.

Proving it: 100 requests at once

Unit tests cover the engine and the service in one PHP process. test_fifty_bids_from_ten_bidders_lose_nothing_and_duplicate_nothing places 50 rising bids from 10 bidders, retries each with the same key, and asserts 50 stored rows, 50 unique request keys, a bid count of 50 and the right leader. But a single process can't produce real contention.

So there's a smoke test that does it over HTTP against a WordPress test site:

1. concurrency-setup.php creates a standard auction and 10 logged-in bidders, and prints their cookies and REST nonces.
2. concurrency.sh builds 50 bids of $2 to $51 in random order, assigns each to a bidder, and writes each job twice with the same request id.
3. All 100 jobs go out through xargs -P 100 curl, so they hit the server at once.
4. concurrency-verify.php reads every stored bid and reports the row count, the number of unique request keys, the bid count in the auction meta, the current price, whether the last row's bidder is the leader, and whether stored amounts are strictly rising.

Because the bids arrive in random order, many are legitimately rejected as too low by the time they get the lock; that's correct behaviour. The checks are about integrity, not how many bids were accepted. In our run, the 100 simultaneous requests produced no lost bids and no duplicate bids.

Illustration: many bids at once, accepted one at a time.

Trade-offs

• Serialised per auction. Bids on one auction are processed one at a time. The critical section is a handful of queries, so throughput per auction is high enough for real auctions, but it is a deliberate ceiling.
• Polling, not push. The bid panel polls every few seconds, every 4 seconds in the last five minutes and every 15 seconds before that (both configurable), and stops while the tab is hidden. Push would be quicker but needs infrastructure most WordPress hosts don't have.
• Named locks need MySQL or MariaDB. They're standard on WordPress hosting, but a database that doesn't support GET_LOCK would need another lock provider.
• Deposits depend on the gateway. Refunding a bidding deposit uses the payment gateway's refund support.
• AI and paid vendor plugins are tested with stubs. AI drafts were tested with mocked API responses, and the Dokan Pro and WCFM Ultimate parts against their documented APIs; live runs are still to come.

Conclusion

Correct bidding on WordPress doesn't need a message queue. It needs a lock scoped to the thing being contended, the state re-read inside it, everything that depends on that state done before it's released, and a request id so retries are harmless. DEV3 AI Auctions ships in the DEV3 AI WooCommerce Plugins Bundle on the DEV3 marketplace and is being prepared for CodeCanyon. The case study covers payments, fraud review and the AI tools. If you're building something where many users compete for the same record, our custom software team can help.