为不同的请求生成相同的序列号

Question

I am inserting a serial number in a table that is increment by one always but when multiple request is coming in same time it is inserting same serial number for different requests.I am using mysql database.

I know i am fetching the max serial number too early in the code and if request is come in same time so it will fetching same serial number for both. is it good idea to update serial number after all work done. what if inserting a record for new request and updating the serial number for previous one is in same time.

public function add(){
    $session          = $this->request->session();

    $company_id       = $session->read('Admin.company_id');
    $emp_id           = $session->read('Admin.emp_id');
    $user_email_id           = $session->read('Admin.email_id');
    $employee_name           = $session->read('Admin.employee_name');

    $conn = ConnectionManager::get('default');

    if ($this->request->is('post')) {

        try{

                $conn->begin();   
                $department = $this->request->data['department'];
                $data = $this->request->data;

                if(!array_key_exists('is_requisition_for_contractor', $data)){
                    $is_requisition_for_contractor = 0;
                } else {
                    $is_requisition_for_contractor = $data['is_requisition_for_contractor'];
                }

                if(!array_key_exists('is_requisition_for_employee', $data)){
                    $is_requisition_for_employee = 0;
                } else {
                    $is_requisition_for_employee = $data['is_requisition_for_employee'];
                }

                if(!array_key_exists('is_boulder_requisition', $data)){

                    $is_requisition_for_boulder = 0;
                } else {
                    if($data['is_boulder_requisition'] == ''){
                        $is_requisition_for_boulder = 0;
                    } else {
                        $is_requisition_for_boulder = $data['is_boulder_requisition'];
                    }
                }
                $is_requisition_for_plant = 0;
                if(!array_key_exists('is_plant_requisition', $data)){

                    $is_requisition_for_plant = 0;
                } else {
                    if($data['is_plant_requisition'] == ''){
                        $is_requisition_for_plant = 0;
                    } else {
                        $is_requisition_for_plant = $data['is_plant_requisition'];
                    }
                }

                if(array_key_exists("files",$this->request->data)) {
                    $files      = $this->request->data['files'];

                    if (count($files)) {
                        $files_uploading_response = $this->uploadMultipleFiles($files, 'files/requisitions/');
                    }
                }
                $last_material_insert_id = '';
                if($this->request->data('material_id')[0] == ''){
                    if($this->request->data('department') == 1){
                        $type = 1;
                    } elseif($this->request->data('department') == 3){
                        $type = 3;
                    } elseif($this->request->data('department') == 2){
                        $type = 2;
                    }
                    if($this->request->data('department') == 1 || $this->request->data('department') == 3){
                        $conn->execute("INSERT INTO material (material_name, material_type_id, company_id, status, is_approved_by_admin) VALUES (?,?,?,?,?)",[$this->request->data('material_name'), $type, $company_id, 1,0]);
                        $last_material_insert_id = $conn->execute("SELECT LAST_INSERT_ID() AS last_id")->fetchAll('assoc');
                    } elseif($this->request->data('department') == 2) {

                        //todo for unapproved material
                        $conn->execute("INSERT INTO material (part_no, material_type_id, company_id, status, is_approved_by_admin,unique_category_id) VALUES (?,?,?,?,?,?)",[$this->request->data('part_no')[0], $type, $company_id, 1,0,$this->request->data('unique_category_id')[0]]);
                        $last_material_insert_id = $conn->execute("SELECT LAST_INSERT_ID() AS last_id")->fetchAll('assoc');
                    }

                }
                // here i am fatching max serial number from table
                $requistion_number          = $conn->execute("SELECT IF(MAX(requisition_no) IS NULL, 0,MAX(requisition_no))  AS requisition_no FROM requisition WHERE site_id = ?",[$this->request->data('site_id')])->fetchAll('assoc');
                $Requisition                = TableRegistry::get('requisition');
                $requisition                = $Requisition->newEntity();
                $requisition->registered_on = $this->request->data['date'];
                $requisition->department_id = $this->request->data('department');
                $requisition->site_id = $this->request->data('site_id');
                $requisition->issues_to_id = $this->request->data['prepared_by_id'];
                $requisition->prepared_by_id = $this->request->data['prepared_by_id'];
                $requisition->approved_by_id = $this->request->data['hod_id'];
                $requisition->hod_id         = $this->request->data['hod_id'];
                $requisition->is_diesel_requisition_for_employee = $is_requisition_for_employee;
                $requisition->is_diesel_requisition_for_contractor = $is_requisition_for_contractor;
                $requisition->is_requisition_for_boulder         = $is_requisition_for_boulder;
                $requisition->is_requisition_for_plant = $is_requisition_for_plant;
                if(array_key_exists('for_tanker_stock', $this->request->data))    {
                    $requisition->for_tanker_stock         = 1;
                }
                if($last_material_insert_id != ''){
                    $requisition->is_material_approved_by_admin = 0;
                }
                $requisition->status         = 1;
                $site_id                     = $this->request->data['site_id'];
                $requisition->requisition_no = $requistion_number[0]['requisition_no'] + 1;
                $requistionnumber = $requistion_number[0]['requisition_no'] + 1;


                $saveRequsition = $Requisition->save($requisition);
                $conn->commit();
}

I am expecting the output different serial number for each request.any optimise way to do this. thanks in advance.

Answer 1

Ok, how about the same strategy, setting the $requisition_number after the row has been inserted (see my other answer), but using a single query with the same method you use to determine the new requisition id:

$conn->execute("UPDATE requisition 
                SET requisition_no = (SELECT IF(MAX(requisition_no) IS NULL, 0,MAX(requisition_no)) AS requisition_no FROM requisition WHERE site_id = ?) + 1",
               [$this->request->data('site_id')]);

The idea here is that a single query will be executed in one step, without another, similar query, being able to interfere.