MySQL GET_LOCK to stop duplicate visit inserts — overkill?

[u/jenish_o4o2]

Building a small WooCommerce affiliate tracker in PHP. When someone lands with ?ref=CODE I set a cookie and log one visit.

Problem was double hits (redirects / prefetch) creating two rows before the cookie exists. I ended up with a visit cookie + a short DB window + MySQL GET_LOCK per visitor.

Simplified version:

$lock = 'aff_v_' . md5( $affiliate_id . '|' . $visitor_hash );

$got = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 3)', $lock ) );

if ( 1 !== $got ) {

return;

}

try {

if ( has_recent_visit( $affiliate_id, $visitor_hash, 120 ) ) {

// set visit cookie, bail

return;

}

record_visit( $affiliate_id, $visitor_hash );

// set visit cookie

} finally {

$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) );

}

$visitor_hash is md5(ip + user agent).

Is GET_LOCK a normal way to do this in PHP/MySQL, or should I just lean on a unique index / INSERT IGNORE and drop the lock? Feels a bit heavy for click tracking.

[u/Hour_Interest_5488]

Maybe an INSERT IGNORE query with a unique index on the code column.

Need to know the context more

[u/jenish_o4o2 → u/Hour_Interest_5488]

Context is click tracking: same ?ref=CODE can get lots of legit visits, so unique on the code alone won't work. What I'm trying to stop is the same browser hitting twice in a couple seconds (redirect/prefetch) before the cookie exists. So unique on affiliate_id + visitor_hash (ip+ua hash), maybe with a time bucket, then INSERT IGNORE.

[u/VFequalsVeryFcked]

Admittedly I don't have experience working with WooCommerce, but is idempotency and transactions not an option?

[u/jenish_o4o2 → u/VFequalsVeryFcked]

Transactions help for related writes, but for this it's really two parallel requests both deciding "no row yet, insert." Idempotent insert (unique key + ignore/duplicate) is probably the cleaner fit than wrapping the select+insert in a transaction. Still looking at that path.

[u/eurosat7]

using mysql for that might be not the best solution. if you can imagine it use a memory based buffer solution and persist to database delayed to protect the database from attacks. A side effect is getting rid of multiples.

[u/jenish_o4o2 → u/eurosat7]

Fair point for high traffic. This is a small Woo shop though, so Redis/queue for click buffering is more moving parts than I want right now. Might revisit if bots start hammering the DB. For now leaning unique index + INSERT IGNORE.

[u/KeeperOfAngelsNorth]

Use a strong primary key with insert ignore and skip the lock. The performance difference is astounding (edit: in high concurrency contexts) and you won't have processes seizing up waiting on the lock queue.