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?

  • 1
    For my purpose, a particular user is allowed up to 5 rows only. If so, I recommend create 5 records (with NULLs in data fields) for each user when the user is created, and use only UPDATE .. WHERE somefield IS NULL, not INSERT. This method allows to update (fill with data) records 5 times only. And no interference because no subqueries. – Akina Jul 4 at 5:01
  • 1
    SELECT p, u FROM (SELECT 121 AS p, 40987 AS u) WHERE ... can be simplified (ie, sped up) to SELECT 121, 40987 FROM DUAL WHERE .... – Rick James Jul 6 at 13:48
up vote 1 down vote accepted
+50

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.

  • Thank! but without for update and transaction, what's happening? – dovid Jul 9 at 8:15
  • and, with for update without begin...commit? – dovid Jul 9 at 16:04
  • 1
    @dovid - I think that the SELECT ... FOR UPDATE is always in a transaction, then end of which releases the locks. autocommit=OFF --> end of the statement. autocommit=ON --> the transaction continues until COMMIT. – Rick James Jul 9 at 16:13

Possible, but unlikely without millisecond timing. If it CANNOT happen, put it in a transaction.

  • 2
    Could you add some facts and reference material to support your theory? Your current answer is a bit short and lacks some form of proof. – hot2use Jul 4 at 6:04
  • 2
    Possible I think that's not right. Query is an atom. Try parallel execution of a lot of queries like INSERT INTO table (field) SELECT value WHERE (SELECT COUNT(*) FROM table, (SELECT SLEEP(pause)) dummy ) < amount; – Akina Jul 4 at 8:22

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.