Transaction won't end

[Jason Lu]

Hi guys,

I'm kinda newbie on transaction. I wrote a stored procedure to insert / update a bunch of tables.
The PS works good. But there're some problems about the transaction won't stop.

The PS was called from a PHP script. If there're multiple requests from PHP within a short period of time (ex: 2 requests in 0.5 seconds) The first request will be completed, but the second request will stuck.

Here's the result from SHOW INNODB status\G;
---TRANSACTION 0 117224630, ACTIVE 20 sec, process no 15099, OS thread id 140073456326400
16 lock struct(s), heap size 3024, 12 row lock(s)

There's a table with an unique key which is a combination with two columns:
nid, last_price

Every SP call will insert a new row with a last_price++
Because the truncation 2 are call very closed to transaction 1, the value was not yet changed by T1, therefore, T2 will try to insert a duplicate row as T1
Since it's a unique key, a sqlexception will trigger.

Then I use
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

To rollback the transaction. That's it. the T2 should meet a SQLEXCEPTION and exit with a rollback. close the transaction. But it did not.

The transaction 2 will not able to end. and all the other SP call will fail because of unable to make a lock.

The is the insert cmd:
INSERT IGNORE into bid_records(bid_user,nid,ip,last_price,bid_username,bid_point,br_type,use_tid,return_free_points, is_autobid, br_serial) values(varUid,varNid,ip,varLastPrice,username,'-1',varBr_type,varUse_tid,varReturn_free_points, is_autobid, varBRSerial);

Did I missed anything?