You could try this in your session
SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION
SELECT ...
INSERT ...
COMMIT;
SERIALIZABLE
causes SELECTs
to do the locking for you
Here is what the MySQL Documentation says on SERIALIZABLE
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommitis disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)
If you close your session, the next session you open will have autocommit back at 1. That means you will have to disable autocommit and set SERIALIZABLE
each time.
Perhaps READ COMMITTED
would be better
SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION
SELECT ...
INSERT ...
COMMIT;
Documentation Says
READ COMMITTED
A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 14.2.2.2, “Consistent Nonlocking Reads”.
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.
At any rate, try a different transaction isolation level.
NOTE: Try it on a Staging Server first.