MySQL Forums
Forum List  »  InnoDB

Transaction won't end
Posted by: Jason Lu
Date: February 03, 2012 03:18PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Transaction won't end
1555
February 03, 2012 03:18PM
752
February 04, 2012 06:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.