PHP and Transactions

[Peter Black]

I have created a script which uses transactions with MySQL 5.

However, I just ran a test plan using JMeter which hit the script with 100 threads, 5 per second, and transactions are not working as expected.

MY script is doing the following:

start transaction

select from table1

rollback if no results are returned or error and throw exception

select from table 2

rollback if error and throw exception

if rows are returned
delete from table 2
rollback if error and throw exception
else
rollback and throw exception

select from table 1

rollback if no results are returned or error and throw exception

select from table 2

rollback if error and throw exception

if rows are returned
delete from table 2
rollback if error and throw exception
else
rollback and throw exception

insert into table 2

commit

Both tables are InnoDB. When I say start transaction I am referring to a function which sends the query START TRANSACTION, and rollback and commit send ROLLBACK and COMMIT respectively. And I am determining if rows were returned using mysql_num_rows.

When running the test plan I see in my logs that it is doing the insert in the second table a few times when it should only do the insert once. What am I doing wrong?

SEE MY LAST POST FOR THE SOLUTION



Edited 1 time(s). Last edit at 01/19/2006 05:44PM by Peter Black.