MySql concurrency with COUNT

I Have a table with user_id and product_id.

For my purpose, a particular user is allowed up to 5 rows only.

To enforce this, I created such a SQL statement:

INSERT INTO the_table (product_id, user_id) 
SELECT p, u FROM (SELECT 121 AS p, 40987 AS u)  
WHERE (SELECT COUNT(*) FROM the_table WHERE user_id = 40987) < 5;

Now My Question:

without explicit locking\transaction,
Is in the case of two simultaneous command run,
When the number of rows before their run is 4,
It is possible that the count will be calculated for both commands before making the insertion - Then there will be 6 rows for same user_id?

Just as SELECT ... FOR UPDATE is sometimes needed, I think you must implement your action in a transaction:

BEGIN;
SELECT COUNT(*) FROM the_table WHERE user_id = 40987 FOR UPDATE;
if the count >= 5, ROLLBACK and exit.
INSERT INTO the_table (product_id, user_id) VALUES (121, 40987);
COMMIT;

Or...

BEGIN;
INSERT INTO the_table (product_id, user_id) VALUES (121, 40987);
SELECT COUNT(*) FROM the_table WHERE user_id = 40987;
if the count > 5, ROLLBACK and exit.
COMMIT;

(Three diffs: order of statements; FOR UPDATE; >= vs >.)

In your original code, see if you can add FOR UPDATE to the subquery in the WHERE clause.

StackExchange Database Administrators Q#211278, answer score: 2