SQL Codeigniter：从控制器创建保存点并回滚到保存点/回滚多个事务

Question

I have processing db transaction from page using ajax request if user don't press submit button than roll-back all sql transaction which is done by ajax request (i will manage this but currently following logic not working if refresh current page).

I have try following code but not working,

function viewPage(){
   $needRollBack=$this->session->userdata('needRollBack');
   if($needRollBack){
      $this->db->trans_rollback();
   }
   $this->db->trans_begin();
   $this->MyModel1->insert(.....);
   $this->MyModel2->insert(.....);
.........................
}

function submitDetails(){
   $this->db->trans_complete();
   $this->session->set_userdata('needRollBack',false);
}

when again viewPage() function call or refresh page than if submitDetails() not called than rolleback all sql stransaction done via ajax request (starting point from trans_begin()) will be roll-baked? is this possible? Kindly guide me...

Answer 1

There are a number of things that need addressing here.

First would be that your database and schema type must support transactions (e.g. if you are using MyISAM table type in MySQL you will not be able to use transactions).

Second to test whether or not transactions are succeeding in CI you write error messages to the error log if $this->db->trans_status() === false.

Finally I would use an alternative approach to the one above (if possible) - one possible approach would be to store your data in a session (through the different stages) and make the call or multiple database calls at the final point (when user clicks submit). You can still use transactions this way and it simplifies the problem.