Transactions and MySQL (locking up)

[eluser]stevefink[/eluser]
Hey folks,

I'm dealing with a block of code that deals heavily with transactions. However, it seems that there is a locked thread or some kind of locked status somewhere (even after gracefully restarted the database).

I keep seeing this in my system logs appear:

ERROR - 2008-03-23 17:45:45 --> Query error: Lock wait timeout exceeded; try restarting transaction

As far as I'm aware, you can only ROLLBACK within the current thread that's executing the transaction -- so I'm not entirely sure how to reset this lock. Better than resetting it, I would like to ultimately learn how the lock was not cleaned up as it's not affecting the rest of my issues.

Anyway, I'm almost certain this isn't a CI specific error. But, perhaps one of you also ran into it.

Thank you for your insight,

/sf

[eluser]nirbhab[/eluser]
I haven't faced such type of problems before, but found a article in mysql reference manual
Locking and Unlocking tables

Hope this might help a bit.
happy programming.

[eluser]samarone[/eluser]
Perhaps relates to:

http://codeigniter.com/bug_tracker/bug/4339/

See also:

http://ellislab.com/forums/viewthread/74497/

[eluser]gtech[/eluser]
when db_debug is set to FALSE transactions work fine, when db_debug is TRUE when the error is displayed before the commit or rollback takes place as after the display code "exit" is called which halts the php script. Although rollback/commit appears to work on my machine at home with debug on, it does not on other machines. Im guessing this could be a race condition between php exiting and the commit/rollback happening.

long and short set db_debug to FALSE when using transactions in the config/databse.php file.


see reasonings
[url="http://ellislab.com/forums/viewthread/77294/"]http://ellislab.com/forums/viewthread/77294/[/url]