CODEWITHSUNDEEP
phpmysqlcodeignitertransactions
Chris Muench
Chris Muench

Reputation: 18318

php/mysql transaction not rolling back on failure (Codeigniter framework)

below I have the following code. If the transaction fails, it is NOT being rolled back. If I remove the lock table statements it rolls back. Is there anything special I need to do in order to use locks and transactions?

function save ($items,$customer_id,$employee_id,$comment,$show_comment_on_receipt,$payments,$sale_id=false, $suspended = 0, $cc_ref_no = '', $auth_code = '', $change_sale_date=false,$balance=0, $store_account_payment = 0)
{
    if(count($items)==0)
        return -1;

    $sales_data = array(
        'customer_id'=> $this->Customer->exists($customer_id) ? $customer_id : null,
        'employee_id'=>$employee_id,
        'payment_type'=>$payment_types,
        'comment'=>$comment,
        'show_comment_on_receipt'=> $show_comment_on_receipt ?  $show_comment_on_receipt : 0,
        'suspended'=>$suspended,
        'deleted' => 0,
        'deleted_by' => NULL,
        'cc_ref_no' => $cc_ref_no,
        'auth_code' => $auth_code,
        'location_id' => $this->Employee->get_logged_in_employee_current_location_id(),
        'store_account_payment' => $store_account_payment,
    );

    $this->db->trans_start();

    //Lock tables invovled in sale transaction so we don't have deadlock
    $this->db->query('LOCK TABLES '.$this->db->dbprefix('customers').' WRITE, '.$this->db->dbprefix('sales').' WRITE, 
    '.$this->db->dbprefix('store_accounts').' WRITE, '.$this->db->dbprefix('sales_payments').' WRITE, '.$this->db->dbprefix('sales_items').' WRITE, 
    '.$this->db->dbprefix('giftcards').' WRITE, '.$this->db->dbprefix('location_items').' WRITE, 
    '.$this->db->dbprefix('inventory').' WRITE, '.$this->db->dbprefix('sales_items_taxes').' WRITE,
    '.$this->db->dbprefix('sales_item_kits').' WRITE, '.$this->db->dbprefix('sales_item_kits_taxes').' WRITE,'.$this->db->dbprefix('people').' READ,'.$this->db->dbprefix('items').' READ
    ,'.$this->db->dbprefix('employees_locations').' READ,'.$this->db->dbprefix('locations').' READ, '.$this->db->dbprefix('items_tier_prices').' READ
    , '.$this->db->dbprefix('location_items_tier_prices').' READ, '.$this->db->dbprefix('items_taxes').' READ, '.$this->db->dbprefix('item_kits').' READ
    , '.$this->db->dbprefix('location_item_kits').' READ, '.$this->db->dbprefix('item_kit_items').' READ, '.$this->db->dbprefix('employees').' READ , '.$this->db->dbprefix('item_kits_tier_prices').' READ
    , '.$this->db->dbprefix('location_item_kits_tier_prices').' READ, '.$this->db->dbprefix('location_items_taxes').' READ
    , '.$this->db->dbprefix('location_item_kits_taxes'). ' READ, '.$this->db->dbprefix('item_kits_taxes'). ' READ');


    $this->db->insert('sales',$sales_data);
    $sale_id = $this->db->insert_id();

    //A bunch of mysql other queries to save a sale


    $this->db->query('UNLOCK TABLES');

    $this->db->trans_complete();

    if ($this->db->trans_status() === FALSE)
    {
        return -1;
    }

    return $sale_id;

}

Upvotes: 0

Views: 991

Answers (1)

Damien Pirsy
Damien Pirsy

Reputation: 25435

I believe it's mainly beacuse of the fact that MySQL "LOCK TABLES" commits any active transaction before attempting to lock the tables.

Actually, the interaction between the two actions (LOCKING and TRANSACTIONS) looks quite tricky in MySQL, as it's very clearly and completely outlined in the MySQL manual page.

The proposed solution is to turn OFF the autocommit flag (which is ON by default, so usually every query issued is automatically committed after execution) by issueing the SET autocommit = 0 command:

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables [..] is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:

Your code could look like this:

$this->db->query("SET autocommit=0");
$this->db->query("LOCK TABLES...."); // your query here
$this->db->query("COMMIT");
$this->db->query("UNLOCK TABLES');

Upvotes: 2

Related Questions