如何在更新数据库时停止重复输入和丢失序列

Question

i am taking max invoice no. from a table and incrementing 1 in it and updating the same table with the incremented value, the problem is that multiple users are performing this operation, so some time its misses the sequence and some time is generating same sequence for different jobs. I can't make invoice no field unique as for other jobs its is 0. the code is like.

$res=mysql_query("select max(invoice_no) from complaints_master");
$result=mysql_fetch_array($res);
$code_id=$result[0]+1;
// update invoice details in complaint master
$usr="update complaints_master set invoice_no='$code_id', invoice_flag='1', invoice_date='$today', invoice_total_amt='$_POST[final_total]' where job_no='$_REQUEST[job_no]'";

Answer 1

You need to set the transaction level to SERIALIZABLE to ensure each MAX(invoice_no)+1 returns a unique value to the client. Not sure how you do that while using PHP, but here is the MySQL doc: MySQL SET TRANSACTION Syntax.