36
I have a table with a column codigo
. I cannot set as primary key. In a system it receives many notifications before it checks with a SELECT
to see if that code already exists in the table. If it does not exist it gives the INSERT
and if there is he does the UPDATE
.
It turns out that this system receives many notifications via POST
where many of them happen at the same time to the same code.
Often the system, even giving the SELECT
before, ends up inserting duplicate codes in the table.
I don’t know how Mysql treats this, so I don’t know if it’s safe to make a SELECT
verifier before the INSERT
. I assume that there is a kind of queue of queries and that it is being processed one by one.
$rs = $db->query("SELECT COUNT(1) AS tem_codigo FROM tabela WHERE codigo = $codigo");
if ($rs['tem_codigo'] == 0){
// aqui daria o insert
} else {
// aqui daria o update
}
How could a code above allow entering two records with equal codes? Imagine a request with a notification happening at the same time, at exactly the same time.
What’s the best way to get around this without using a primary key?
You have, or can create, a UNIQUE key in the code column?
– bfavaretto
I can’t touch the seat structure =/
– rodrigoum
Have you ever tried to do this by using Procedure ? Sometimes this can solve, since SQL is faster to run SELECT than the same code.
– BetaSystems - Rodrigo Duarte
Wouldn’t be the case to check the code before giving the
insert
, because then the routine would check if it exists if it did not give theinsert
?– user3083
Yes there can be duplication of records. Two simultaneous selects will return the same code. Don’t think in terms of "processing queue" BD doesn’t work so well. I suggest studying competition, transactions and level of isolation. The best solution would be the code field to be auto increment so you ensure that the base will handle this.
– jean