Code Igniter database transactions

[eluser]kirilisa[/eluser]
Yeek!

When I change my test example to use trans_start/trans_complete instead of trans_being/trans_rollback/trans_commit, the DB freezing stops but it updates the table inappropriately on the second reload, just like yours does!!

And I still get intermittent freezing problems, for instance when I switch back and forth between your method and mine in my controller, combined with changing my valid SQL statement with every reload so that the update will happen with every reload.

This *can't* be by design. What am I missing?

EDIT: I can't waste any more time trying to figure this out. I've just trying dumping some echo statements in mysql_driver.php to see if/when it's rolling back, committing, etc. - I can't work it out. I don't understand why it is committing on bad transactions when using trans_start and trans_complete. For now I'm just going to have to trust that everything will work ou when db_debug is FALSE...

I really wish there were some way of asking Derek this stuff, as transactions are fundamental and just seem scarily buggy.

[eluser]gtech[/eluser]
OK,

my db defaults to innodb so excuse the table create example.

I have to say that running my test on windoze XP I do not see the same behavior, with db_debug set to TRUE my db table continually rolls back after every refresh.. but reading the code I am expecting it to fail..

line 303 of DB_driver.php will call display_error if db_debug is turned on
line 1143 of DB_driver.php is exit; which will terminate the script if db_debug is on and that is maybe why we are seeing different behaviours on different OS's.

but from what you are saying kirilisa is that when db_debug is FALSE it works as expected.

I think then the documentation should say that transactions only work if db_debug is set to FALSE
OR
this is a bug/feature request: don't display the error if its in a transaction.

If it makes you feel better kirilisa I have 25 db tables with about 10 different functions for each table, and about 50% of those are in transactions (the ones with multiple updates/inserts). I have written automated tests for all of these functions and they test rollback by inserting rouge values I then select the data back out of the db to test it.. with db_debug set to FALSE all my test run passed. with db_debug set to TRUE it will obviously halt at the first error. I have tested this with WINDOWSXP, SUSE & UBUNTU LINUX and have had no problems with db_debug set to FALSE, and using 1.4 - 1.6 of CI, and each model is tested within one request (about 100 tests within each request).

stevefink - can you let us know if setting db_debug to FALSE fixes your problem, if so then I can/you can/anyone can open a thread in the bugs forum linking to this thread

[eluser]kirilisa[/eluser]
Ah yeah after scrolling up and seeing your other comment I kinda figured you were using INNODB by default.

Well it's good to hear that your tests have all indicated things work well when db_debug is set to FALSE. I'm developing a rather enormous app which is going to be fraught with transactions so the bugginess has me quite unhappy!

The freezing is definitely annoying, but what really flips me out is the second thing, how when using trans_start and trans_complete it will in fact update the table if you reload, even though it has crashed with an error. Do you observe this behavior as well?

When I first ran into this problem I did a bunch of hunting on the forums and found a fair number of transaction related posts, eg:
http://ellislab.com/forums/viewthread/68368/
http://codeigniter.com/bug_tracker/bug/4339/
http://codeigniter.com/bug_tracker/bug/4299/ (which I see you posted on)

So it is clear that the whole transaction thing really does need some looking at. I hope that they will get around to it in the next version of CI. I posted this same error in the bug Report forums some weeks ago but unfortunately got no response so I'm not sure how else to submit it.

[eluser]gtech[/eluser]
[quote author="kirilisa" date="1208763923"]

The freezing is definitely annoying, but what really flips me out is the second thing, how when using trans_start and trans_complete it will in fact update the table if you reload, even though it has crashed with an error. Do you observe this behavior as well?

is that with db_debug ON or OFF? I DO NOT SEE THIS BEHAVIOUR, however with db_debug on you may be nesting transactions (as the first ones not complete) and that is not supported in MYSQL hence the odd behavior (that is a pure guess)

When I first ran into this problem I did a bunch of hunting on the forums and found a fair number of transaction related posts, eg:
http://ellislab.com/forums/viewthread/68368/
http://codeigniter.com/bug_tracker/bug/4339/
http://codeigniter.com/bug_tracker/bug/4299/ (which I see you posted on)

The first one looks like the guy was using the mysqli driver which I have never used but when he used the more common mysql driver it worked (maybe he/she was using the wrong driver for the database).
the other two bugs look the same, and maybe its an oversite, but if you have two transactions in the same request and the first transaction rollsback so will the second.. not a disaster, but annoying when you doing automated testing and have many transactions that rollback within one page request


So it is clear that the whole transaction thing really does need some looking at. I hope that they will get around to it in the next version of CI. I posted this same error in the bug Report forums some weeks ago but unfortunately got no response so I'm not sure how else to submit it.

I must admit I been through the same process and sometimes nobody replies it can be frustrating and is my only real grip with CI, however to stick up for CI a bit as I do like it, db_debug needs to be set to FALSE before you even notice bug 4299 which in my oppinion is not a critical bug/feture request.. just annoying. And apart from that bug (which is v easy to fix) I have never had any other problems with transactions.

I do agree however it does not inspire confidence to someone who has not used the CI transactions code before and it would be nice if at least this problem was documented
[/quote]

[eluser]kirilisa[/eluser]
The table updating on reload happens when db_debug is set to TRUE. When it's FALSE, everything works as it should.

It happens with the code you posted (or with mine, which I tested changing the insert values on the fly)

1) set db_debug = TRUE
2) create test table: it is empty
3) load the method of the controller with your code in it
4) it crashes with an error (thus never finishing the txn)
5) query the test table, it is empty
6) reload the page (thus starting a new transaction)
7) it crashes as before
8) query the test table, it has been written from the previous transaction.

But it only happens when using trans_start/trans_complete, not with trans_begin/trans_commit/trans_rollback. And I put in a couple debug statements and I never see any indication that it gets to the trans_complete() method [or indeed trans_rollback() or trans-commit()] so why is it being committed?

You're right, that process does result in nested transactions due to the first one's not completing, but does mysql by default if having a nested transaction, automatically make the first (unfinished) one commit? That doesn't seem to make sense.

Yep I also saw the solution to bug 4299 and threw a $this->_trans_status = TRUE; in my trans_start (actually in my trans_begin too) to get around it.

I've been psyched about CI and I still am, it's great and it's making life a lot easier. It's only this txn thing that has made me a bit uneasy, so I hope something more definitive will be done or at least documented sometime in the next couple months.

[eluser]gtech[/eluser]
I guess you could test the behaviour of nested txn's in the cli, see if the same happens.. Thanks for the feedback its been useful.

[eluser]kirilisa[/eluser]
I just added into my DB_driver.php, at line 307 [where the display_error() was in the query() method], and commented out the existing call to display_error(). That seems to take care of it.

Code:

//@@@ added by kirilisa
$errno = $this->_error_number();
$errmsg = $this->_error_message();
$this->trans_rollback();
return $this->display_error(array("Error Number: $errno",$errmsg,$sql));

Ugh, it's already 2.30pm. Must... move... on... :-P

[eluser]gtech[/eluser]
Just comment out the call to display error.. don't rollback in the query function as you will rollback before your transaction is completed... eg you have two queries in a transaction- the 1st one fails and your 'fix' will rollback before the second query is called.

what the code does is set _trans_Status TRUE when you first load your page and then it is set to FALSE if an error happens on a query.. If the flag is FALSE when the trans_complete function is called it will rollback, if it TRUE it will be commited

so just comment out

Code:

//return $this->display_error(array("Error Number: $errno",$errmsg,$sql));

and I reckon that will fix transactions if db_debug is set to TRUE.. however I just recommend setting the flag to FALSE in the config file.. it just saves messing around, I just don't think its designed to run with debug on.

[eluser]kirilisa[/eluser]
I wanted to keep the display error since I'm doing a lot of DB changes right now so it is sometimes useful to me to see that error right out. Then again, I can comment out the display_error and just log it instead!

[eluser]gtech[/eluser]
bug report

[url="http://codeigniter.com/bug_tracker/bug/4451/"]http://codeigniter.com/bug_tracker/bug/4451/[/url]