MySQL — Synchronize multiple threads


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
7 views
There is a task: to insert N elements into the table, but before that make sure that are not added if these items.

I.e. first do something like:

SELECT COUNT(*) FROM xxx WHERE x IN (x1,x2,x3,x4,x5,x6...... x1000);

If the result is 0, it means that you can do the same bulk INSERT statement.

But the problem is how to do it secure with multithreading?

That is, for example, how to avoid the situation that obtained at the same time the thread 2 and the procedure is the same:

P1: SELECT COUNT(*) — receives a "0"
P2: SELECT COUNT(*) — receives a "0"
P1: does the INSERT
P2: because I get "zero" in the previous select, INSERT, too, making duplicate records

Is there a solution to this problem?

The idea is to set some global flag seems to be very crooked and stupid.

Hranici do not offer, because again- they will not save you from the simultaneity. Generally such things are done?

Sometimes it is assumed to insert large amounts of data on tens of thousands, so it is likely that queries will run too quickly and is likely to catch a bug with simultaneous insert.
by | 7 views

6 Answers

0 like 0 dislike
Transactions innodb.
by
0 like 0 dislike
if x is unique then add the UNIQUE KEY. and verify which type you have tables myisam, innodb?
by
0 like 0 dislike
Simple variant — the LOCK TABLES, but it is better not to abuse lokas.
by
0 like 0 dislike
Xxx LOCK TABLES a WRITE;
SELECT COUNT(*) FROM xxx WHERE x IN (x1,x2,x3,x4,x5,x6...... x1000);
...
INSERT INTO xxx ...;
UNLOCK TABLES;
\r
Will lock table for other sessions. If the requests between the lock and unlock are used to read other tables such as the WHERE x IN (SELECT * FROM xxx1), they also need, if I'm not mistaken lock: LOCK TABLES WRITE xxx, xxx1 READ
by
0 like 0 dislike
A UNIQUE INDEX on the field and do INSERT IGNORE. The matching fields will fall off the machine, this option will work fast enough.
by
0 like 0 dislike
INSERT ON DUPLICATE UPDATE and other triggers on the insert.
also — LOCK tables. Also Lok brains on the server and other mutexes.
Option 3 — work through an intermediate install
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
110,608 questions
257,186 answers
0 comments
28,140 users