Code Igniter database transactions

[eluser]stevefink[/eluser]
Hi all,

I'm using the database class provided by CI as well as its transaction support. I've run into a serious issue which is detrimental to my sites health until I can figure out how to avoid/remedy it in the future.

I have a simple insert query nested between:

$this->db->trans_start();

and

$this->db->trans_complete();

One seriously impeding issue is a problem with one of my algorithms which should be checking for duplicates on columns which do not allow for such data. With that said, the transaction hierarchy is not forgiving in the sense where if I try to insert a row with a duplicate key -- kind of defeating the purpose of atomic commits.

In the initial run, I make an HTTP request and see the following:

ERROR - 2008-04-19 17:44:45 --> Query error: Duplicate entry '8388607' for key 2

All subsequent requests from here forward fail, and the site is rendered useless until I manually restart MySQL as I have no reference to the transaction:

ERROR - 2008-04-19 17:46:46 --> Query error: Lock wait timeout exceeded; try restarting transaction

Have any of you folks experienced something similar?

[eluser]gtech[/eluser]
have you set db_debug to false in config/database.php? if not a php error will be raised and rollback wont happen.

[eluser]stevefink[/eluser]
I haven't set db_debug to false, no. I'm not sure how this flag is supposed to prevent transactions from either failing or passing? Is there somewhere in the documentation this is further elaborated on?

[eluser]gtech[/eluser]
With db_debug on it does stop the rest of the code in the request from working, thats why I tend to turn it off and log any errors manually.

I have tested a fail case with 1.6 with db_debug on and the rollback appears to work OK, so apologies even though the code halted after the commit subsequent requests still used the database ok.

[edit] this has later been proven just to be the case on my machine, on other os's it fails to complete the transaction when db_debug is set to TRUE[/edit]

[strike]
Are you using mysql tables that support rollback? *my tables use the innodb engine*

eg:

Code:

Create table sections(<whatever columns you have>) ENGINE = InnoDB;

have you tried inputting the sql statements manually into mysql within a transaction and see if the lock still happens?

Code:

START TRANSACTION;
insert ...
insert ... the duplicate key
ROLLBACK;

[/strike]

[eluser]stevefink[/eluser]
Yeah, these affected tables are certainly running InnoDB for transaction support. I wouldn't be prompted to even restart the transaction if that was the case. It's not gracefully rolling back the transaction when a duplicate key is inserted.

I'm going to have to manually run some transactions through mysql CLI to see if I can replicate the problem and then start singling out if it's something on the transaction API within CI or MySQL transaction code that's broken.

[eluser]gtech[/eluser]
[edit] ignore this reply [/edit]
[strike]
you can also try printing out trans_status();, as that should get set to FALSE as soon as you attempt to insert the duplicate entry, if is not set to FALSE then rollback will not happen.

you can put some echo debug in the file "database/DB_driver.php" as well, I assume you have looked at it but if not its straight forward to follow.
[/strike]

[eluser]gtech[/eluser]
[edit] and ignore this one too [/edit]
[strike]
Espcially put some logging in the trans_complete function, you can see if its getting there!. on line 303 of db_driver.php you can see where the error is getting thrown if db_debug is on... you can always test it with it set to false its a two second job, check the config/database.php file.
[/strike]

[eluser]kirilisa[/eluser]
I had this same problem (with CI 6.1) and I posted about it but no one ever got back. http://ellislab.com/forums/viewthread/76491/

I never finished testing it because I got distracted with other things, but after I discovered the database config file :o) I was also figuring (like gtech) that it was probably due to db_debug being set to TRUE since then the query() method in DB_driver.php will return the display_error() method which itself exits without returning (thus it never gets to the point of toggling the rollback due to the _trans_status flag being set).

Although obviously on a production server you'd have db_debug set to off, it doesn't seem too great to me that having them on during development breaks transactions...

[eluser]gtech[/eluser]
The db_driver code does look as though it chucks the error out before rollback happens the display_error function has an exit; at the end suggesting it terminates the current script. however I turned db_debug on with the below test and it works using CI 1.61

Code:

//mysql [edit]my db defaults to innodb[/edit]
create table test(t varchar(20) unique);

//controller
$this->db->trans_start();
$this->db->insert('test',array('t' => 2));
$this->db->insert('test',array('t' => 2));
$this->db->trans_complete();
echo"?";
return

it will run all day long after a refresh I even change the insert values on the fly it works, and transactions are getting rolled back!, however I am sure I used to have problems with db_debug being on... honest guv.

questions
what version of CI are you using so I can test on the same platform?
does it lock with db_debug turned off?
if putting logging info in trans_complete() does it get to that function?

let me know how you get on as this effects my work as well, I would love to be able to replicate your error. hummph I wonder if we are experiencing a race condition.

[eluser]kirilisa[/eluser]
I am using CI 1.6.1, PHP 5.2.3, mysql 5.0.45 on Ubuntu Gusty (sure thats more info than you wanted)

First thing, the table you defined would have to have ENGINE=INNODB added onto its definition to make transactions apply to it.

That being said, when I run your code, above (but making the test table be an INNODB table), like you said you can reload it all day long and it doesn't freeze, just displays the boxed error about duplicate entries.

BUT something very disturbing, is that if you load the page the first time, it crashes with an error, check the contents of the test table, there is nothing there. now reload the page, it doesn't freeze, and still crashes with the error, but now look in the test table and the entry has been made! This shouldn't be, since the entry is wrapped in the txn with the bad query, so it should always be rolled back.

It is worth noting that when I turn db_debug to FALSE, your code displays the question mark as it should, and the test table is never updated (which is good!)

So it would seem that your code is not freezing because the transaction IS actually being committed (and thus showing up in the test table) and thus every time you reload, the transaction started is a fresh one. But I don't know why that would be. Is trans_start() automatically committing unfinished transactions or something? That would be awful.

...or am I missing something obvious??

When I run my code, below (which is using trans_begin/trans_rollback/trans_commit rather than trans_start/trans_complete) the first time is crashes quickly with a boxed error about BAD QUERY, and the second time it hangs forever and I have to restart DB or kill processes. It never does update the table, though (which is good, because it shouldn't!!)

When I turn db_debug to FALSE, however, it works fine, saying 'rollback' as it should.

Code:

// controller
$this->db->trans_begin();
$this->db->query("update SystemUser set lastName = 'blah1' where userName = 'cmiuser'");
$this->db->query("BAD QUERY");

if ($this->db->trans_status() === FALSE) {
  echo "rollback";
  $this->db->trans_rollback();
} else {
  echo "committed";
  $this->db->trans_commit();
}

IF, however, I comment out the line which contains the valid SQL (the update SystemUser...) line, I can also reload my version ad nauseum with db_debug turned to TRUE, because they database won't have been touched with a good query and thus won't be locked.