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?