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?