Duplication in serial number while generating tickets

Question

I'm trying to generate barcode tickets after successful booking and it's working fine. But yesterday (06-Jan-2017) night at 11.59 pm, one customer booked 2 tickets but the ticket sl.no was duplicate. Tthat is both tickets sl.no was 1. Actually it should be 2. But the next customer's tickets sl.no was correct, it's 3,4,5. Is there any mistake in the code below where the tickets get generated?

for($j=1;$j<=$cat_array[$i];$j++) {
  $sel_max_dt =  mysql_query("select MAX(DT) AS maxdt, MAX(REC_NO) AS maxrec FROM  tkt_barcode");
  //to select max date and max rec no.
  $row_max_dt = mysql_fetch_array($sel_max_dt);
  $dt_max = $row_max_dt['maxdt'];
  //$no_max = $row_max_dt['maxno'];
  $recno = $row_max_dt['maxrec'];
  if($dt_max ==  date("Y-m-d") && $recno == $TxnID) {
    //if same date and same bkid  barcode slno continues.
    $sel_no = mysql_query("select MAX(BARCODE_SLNO) AS maxno FROM  tkt_barcode WHERE REC_NO=".$TxnID);
    $row_sel_no = mysql_fetch_array($sel_no);   
    $tkt_slno = $row_sel_no['maxno'];
    $tkt_slno = $tkt_slno + 1;
  }
  if($dt_max ==  date("Y-m-d")&& $recno != $TxnID) {
    //if same date and different bkid  barcode slno continues.
    $sel_no = mysql_query("select MAX(BARCODE_SLNO) AS maxno FROM  tkt_barcode WHERE DT='$dt_max' ");
    $row_sel_no = mysql_fetch_array($sel_no);   
    $tkt_slno = $row_sel_no['maxno'];
    $tkt_slno = $tkt_slno + 1;
  }
  if($dt_max != date("Y-m-d") && $recno != $TxnID) {
    //if not same date start barcode slno from 1.
    $tkt_slno = 1;
  }
  $sel_sl_no = mysql_query("select MAX(SL_NO) AS slno FROM  tkt_barcode WHERE REC_NO=".$TxnID);
  $row_slno = mysql_fetch_array($sel_sl_no);
  $slno = $row_slno['slno'];
  $bartkt_slno = $slno + 1;
  if($i==1 && $j>$count_promo_adult && $arr_edit["PROMO_CODE"]!=""){
    //adult cat and total adult count > total promo adult count
    $disc_adult = $adult_amt;
    $discamt=$adult_amt - $disc_adult ; //  discount amt
    $rate=$adult_amt;  //normal rate
    $net_amt=$disc_adult; //discount rate
    $pro_cd ="";
    //promo code is null when total adult count > total promo adult count
   } //end if
  //string pading digits 
  $len_txn =strlen($TxnID); // bookid length 
  $pad = 6; //str_pad digits
  if($len_txn > 5) {
    if($len_txn==6) { $pad = $pad-1;}// if 6 digit
    if($len_txn==7){ $pad = $pad-2;}// if 7 digit
  }
  $fullbar = $TxnID.str_pad($tkt_slno, $pad, 0, STR_PAD_LEFT).$clnd_date.$counter_no;
  //barcode no
  $fields  = "BARCODE_SLNO,DT,FIN_YEAR,Counter_Code,Branch_cd,REC_NO,BARCODE,TICKET_TYPE,CATG_CD,AMT,DISC_AMT,SL_NO,NET_AMT,BCODE_CATG_SLNO,BCODE_SLNO_GEN,BCODE_SLNO_CATG_TOT,DAY_TYPE,Check_in_date,PROMO_CODE,TAX,S_TAX,conv_base_rate,conv_tax_rate";
  $values = "'$tkt_slno','$booked_date','$fin_year','$counter_no','$branch','$TxnID','$fullbar','$cat_name_tkt','$i','$rate','$discamt','$bartkt_slno','$net_amt','$j','$count','$cat_tot','$type_day','$date_check','$pro_cd','$tax_each','$ser_tax_each','$conv_base','$conv_tax'";
  $sql_tkt = $objA->insert_fields(TABLEPREFIX.'tkt_barcode',$fields,$values);
}//end for loop

Sounds like a race condition with the db. But I don't know enough php to parse for where it might be. — Aaron, Jan 07 '17 at 15:38
@Aron, i don't understand what you actually mean by race condition with the db. Could you please give me an example or any link to check it? — Amiyar, Jan 09 '17 at 04:55
http://stackoverflow.com/questions/34510/what-is-a-race-condition. Two requests came into your server at the same time and were handled by two different threads. These two threads are racing each other for the db. — Aaron, Jan 09 '17 at 13:57
There are several mistakes. First one is generating something in PHP, while there are methods in MySQL that can generate non-clashing values. Second mistake is that you never placed unique constraints on your unique ticket codes. Since your code is really ugly to read and not maintainable, it's extremely difficult to offer a proper solution. — Mjh, Jan 09 '17 at 15:31

score 0 · Answer 1 · answered Jan 09 '17 at 14:01

0

You are selecting a count of existing records

  $sel_sl_no = mysql_query("select MAX(SL_NO) AS slno FROM  tkt_barcode WHERE REC_NO=".$TxnID);
  $row_slno = mysql_fetch_array($sel_sl_no);
  $slno = $row_slno['slno'];
  $bartkt_slno = $slno + 1;

and using that count to set your sl number. But time passes between reading the count and writing the record. When two threads read the count at the same time you get your problem.

answered Jan 09 '17 at 14:01

Aaron

874
3
17
34

thanks @ Aaron, is there any solution to overcome this issue. – Amiyar Jan 10 '17 at 05:02

score 0 · Accepted Answer · answered Jan 10 '17 at 10:33

Finally I found the solution by adding another condition given below.

if($dt_max !=  date("Y-m-d")&& $recno == $TxnID) {
//if different date and same bkid  barcode slno continues.
$sel_no = mysql_query("select MAX(BARCODE_SLNO) AS maxno FROM  tkt_barcode WHERE DT='$dt_max' ");
$row_sel_no = mysql_fetch_array($sel_no);   
$tkt_slno = $row_sel_no['maxno'];
$tkt_slno = $tkt_slno + 1;
}

Thanks guys for the help. I will try to make my code more readable next time. :)

Duplication in serial number while generating tickets

2 Answers2