Correct handling of parallel insertion PHP/MySQL

Question

I have a PHP app which receives information from an external source, and saves data to MySQL.

The algorithm is very simple. The data being sent is a string, and it works this way:

Receive the data in $data. Ex: $data = "info"
Checks in database if "info" exists. Ex: select count(*) from table where data="info"
If "info" exists, then exit
Else insert "info" in database and exit.

When there are several parallel requests to the page, there are some duplicates. The data can be sent twice of more times, but we need to save it just once.

The reason is that there's a time lapse between the steps 2 and 3, where the data is still not inserted, so two instances can insert it at the same time.

What's the correct way to handle this in PHP?

(The idea is not to add a unique index to MySQL, I know it'll work but will throw an error. Also the data being sent is very long, and indexes have a limit.)

I think I'll use a stored procedure, because I also need to separate the data per user and count the times each user has received different information. Thanks for your help. — Christian Oviedo Gabarda
– Christian Oviedo Gabarda, Commented Jan 12, 2017 at 15:42

Barmar · Accepted Answer · 2017-01-11 23:10:15Z

1

You can do the test and INSERT in a single query:

INSERT INTO yourTable (columnName)
SELECT :data
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM yourTable WHERE columnName = :data)

answered Jan 11, 2017 at 23:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christian Oviedo Gabarda Over a year ago

I also need to count the times. I simplified the algorithm in the example, but it's a per user link, so I need to count each user once, even if they click more than once. In other table I have the amount of users that opened the link. I think the best way is a stored procedure.

Barmar Over a year ago

You could certainly expand the subquery into a join between the tables. The basic idea is that you can use this general approach to add a row only when some other query finds nothing. Just put that other query in the WHERE NOT EXISTS.

Collectives™ on Stack Overflow

Correct handling of parallel insertion PHP/MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related