遵循创建mysql的代码是多么安全 - 每年重置字段，安全性也考虑来自多个请求的输入

Question

I HAVE tables in this test code:

table WORKER -> name varchar(50) , spec varchar(50) UNIQYE

table KEYGEN- > id INT AUTO_INCREMENT , year varchar(50)

spec should be like = 2009/54.

CODE: for checking and reseting (truncating) keygen table.

$insertedid=""; //to put last inserted id from keygen 
$spec="";   //spec number generated from year + $insertedid in table keygen
$year=date("Y");  //current year

$query="SELECT * from keygen WHERE year='{$year}' ";

    if($noviRed=$mysqli->query($query))
    {
        if($noviRed->num_rows==0)
        {
            $truncate="truncate keygen"; 
                if($mysqli->query($truncate)) {echo"<h2> TRUNCATING  TABLE OK </h2>";}  else { exit("fatal error");}

            $query2="INSERT INTO keygen(year) values('{$year}') ";
                if($mysqli->query($query2)) {$insertedid=$mysqli->insert_id;} else {exit("fatal error");}
        }
        else
        {

            $query2="INSERT INTO keygen(year) values('{$year}') ";
                if($mysqli->query($query2)) {$insertedid=$mysqli->insert_id;} else {exit("fatal error");}
        }
    }
    else {exit("fatal error");}

CODE: for inserting data into worker table.

$spec=$year."/".$insertedid; //generating spec number

if($insertedid!="" AND $spec!="")   
{
    $query3="INSERT INTO worker(name,spec) values('{$name}','{$spec}') ";   

    if($noviRed=$mysqli->query($query3)){echo"<h1>SUCCES </h1>";}
    else {exit("fatal error");}
}

Answer 1

Not entirely safe.

If multiple requests come in at once near the beginning of a year, the TRUNCATE query may end up running twice, as the SELECT will return zero rows for both requests. This would probably result in a unique key collision on one of the subsequent inserts.