transaction doesn't work properly

I'm looking for a case how `transaction` do it's job. In first case I write a `sleep(10000)` between two queries, then turn of the wamp server:

PHP Code:

$this->db->trans_begin();
$q1 = $this->db->query("INSERT INTO `test`(`test_col`) VALUES ('aaaaaa')");
sleep(100000);
$q2 = $this->db->query("INSERT INTO `test`(`test_col`) VALUES ('bbbbbb')");
$this->db->trans_complete(); 


But first record is saved without second one. Then I tried with another case, that first query fails because of duplicated value:

PHP Code:

$this->db->trans_begin();
$q1 = $this->db->query("INSERT INTO `test`(`test_col`) VALUES ('duplicated')");
$q2 = $this->db->query("INSERT INTO `test`(`test_col`) VALUES ('new row')");
$this->db->trans_complete(); 


But again the new one exist in the database. Any idea? Is there anything I'm missing?


Also `trans_rollback`  doesn't work.

PHP Code:

if ($this->db->trans_status() === FALSE)
{
    // I get this line, but trans_rollback doesn't work
    $this->db->trans_rollback();
} 


I must use InnoDB in order to work with transactions!

The problem is that since you are using trans_begin instead of trans_start, you are running the transaction manually. When running transactions manually, you don't want to use trans_complete, but you are. Remove trans_complete, and then you should be able to rollback or commit.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

The problem is that since you are using trans_begin instead of trans_start, you are running the transaction manually. When running transactions manually, you don't want to use trans_complete, but you are. Remove trans_complete, and then you should be able to rollback or commit.

I don't want to use rollback or commit. In my case, the problem was with Storage Engine. It must be InnoDB.

MyISAM strikes again.

LOL! @Narf

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

The problem is that since you are using trans_begin instead of trans_start, you are running the transaction manually. When running transactions manually, you don't want to use trans_complete, but you are. Remove trans_complete, and then you should be able to rollback or commit.

I don't want to use rollback or commit. In my case, the problem was with Storage Engine. It must be InnoDB.

If you don't want to rollback or commit, then why are you using trans_begin instead of trans_start? You need to go back and read the docs related to transactions.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3