MySQL - Synchronizing Multiple Threads

Question

MySQL

There is a task: to insert N elements into the table, but before that, make sure that such elements have not already been added.

Those. first we do something like:

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

If the result is 0, then you can do the same massive INSERT.

But there is a problem - how to do it securely with multithreading?

That is, let's say how to avoid the situation when 2 streams are received at the same time and the order of actions is as follows:

P1: SELECT COUNT (*) - gets "0"
P2: SELECT COUNT (*) - gets "0"
P1: does INSERT
P2: because got "zero" in the previous select, also does an INSERT of duplicate records

. Is there a solution for this problem?

The idea of ​​setting some kind of global flag seems very crooked and stupid.

Do not offer storage boxes, because again - they will not save from simultaneity. How are things like this done?

Sometimes it is supposed to insert large data arrays of several tens of thousands, so it is likely that queries will not be executed too quickly and there is a chance of catching a bug with simultaneous insertion.

Answer 1

Transactions, innodb.

Answer 2

if x is a unique field, then add UNIQUE KEY. and specify what type of table you have - myisam, innodb?

Answer 3

A simple option is LOCK TABLES , but it's better not to abuse locks.

Answer 4

LOCK TABLES xxx WRITE;
SELECT COUNT(*) FROM xxx WHERE x IN (x1,x2,x3,x4,x5,x6……x1000);
...
INSERT INTO xxx ...;
UNLOCK TABLES;
Lock the table for other sessions. If in queries between locking and unlocking other tables will be used for reading, for example WHERE x IN (SELECT * FROM xxx1), then they also need to be blocked if I'm not mistaken: LOCK TABLES xxx WRITE, xxx1 READ

Answer 5

UNIQUE INDEX on the field and do INSERT IGNORE. Matching fields will fall off automatically, this option will work quickly enough.

Answer 6

INSERT ON DUPLICATE UPDATE and other insert triggers.
See also - LOCK tables. Also lock brains on the server and other mutexes.
Option 3 - work through an intermediate instance