1
I have a script that receives requests from the free market when there is some update on a request.
When an order is placed, the free market sends 5 to 10 requests to that script at about the same time (don’t ask me why it sends so many requests at the same time, because they are all the same), and I enter in my bank only one of them.
The problem is basically in the lines below, which check whether the request has already been inserted in the table of the bank. ($obj_sql is a simple mysql handler, and I think the problem is not in it.)
$jatemped = $obj_sql->Get("select count(ped_id) from pedidos where mel_order='$id_order'");
if ($jatemped===0) {
$obj_sql->Query("insert into pedidos (mel_order) values ('".$id_order."')");
}
the mel_order field is not unique as it can contain records in the requested table with this blank field.
The problem is that sometimes Insert is duplicated by a second request of the script, with the same $id_order in the mel_order field.
Is there some sort of cache between the Insert and the select of the next script execution that it cannot see the Insert that has just been made?
How can I fix this?
how are these free market requests? can post?
– Diego Lela
What can help you is to use transactions, which are available in Mysql for the Innodb Engine. With a transaction it is possible to lock the table or rows while writing operations are taking place in the database. Documentation: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html
– Diego Marques
Probably a Race Condition, the two requests arrived at the same point at the same time, so for both the
$jatempedis0, this is something predictable and somewhat logical since there is nothing that blocks. Ideally you use UNIQUE, the rest is gambiarra. One of the other tricks is to useSELECT ... FOR UPDATEwithin aTRANSCATIONor execute aINSERT SELECT ... WHERE NOT EXISTS ....also within the transaction.– Inkeliz
Try changing
count(ped_id)for onlyped_idand in the IF comparison, change toif (mysql_fetch_array($jatemped) === false) {. I find it unnecessary to count the number of records withcount(), since you just want to know whether or not the registration exists with the$id_order. I think it’s better to get a boolean (maybe faster until).– Sam
@Diegolela the requests are post with a json in the body. I think this is not the problem, because I can identify the contents correctly.. the point is that the calls are at the same time, and first select with Count can not identify that another transaction almost at the same time already inserted.
– Rodrigo Vicentin
@Davidsamm will make this change to see if it helps too
– Rodrigo Vicentin
@Is it possible to have a single key field only when the field is not null or blank? because this field exists several records with the blank value for the field mel_order.
– Rodrigo Vicentin
@Rodrigovicentin, yes. By default inclusive, "For all Engines, a UNIQUE index Permits Multiple NULL values for Columns that can contain NULL.", see the documentation. As long as it is actually NULL (and table allows NULL) it can have multiple NULL even in UNIQUE.
– Inkeliz