MySql concurrency with COUNT

Question

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?

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
– Akina, Commented Jul 4, 2018 at 5:01
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
– Rick James, Commented Jul 6, 2018 at 13:48

Rick James · Accepted Answer · 2018-07-06 13:46:56Z

2

+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.

answered Jul 6, 2018 at 13:46

Rick James

80.8k5 gold badges52 silver badges120 bronze badges

Thank! but without for update and transaction, what's happening?

dovid
– dovid

2018-07-09 08:15:16 +00:00
Commented Jul 9, 2018 at 8:15
and, with for update without begin...commit?

dovid
– dovid

2018-07-09 16:04:15 +00:00
Commented Jul 9, 2018 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
– Rick James

2018-07-09 16:13:08 +00:00
Commented Jul 9, 2018 at 16:13

Add a comment |

Russell Fox · Accepted Answer · 2018-07-03 23:20:34Z

1

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

answered Jul 3, 2018 at 23:20

Russell Fox

1991 silver badge5 bronze badges

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.

John K. N.
– John K. N.

2018-07-04 06:04:28 +00:00
Commented Jul 4, 2018 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
– Akina

2018-07-04 08:22:50 +00:00
Commented Jul 4, 2018 at 8:22

Add a comment |

Stack Exchange Network

MySql concurrency with COUNT

Now My Question:

2 Answers 2

Your Answer

Hot Network Questions

MySql concurrency with COUNT

Now My Question:

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions