transaction to misbehave

using PHP in a MySQL transaction generating an error

[dbarasuk]

Hi,
I have a script using PHP in a transaction manner to withdraw money from one account and credit it to another account.

The script is designed to withdraw € 6 from one account and credit the same amount to another person.

The first time the script executed, it executed properly. Thereon, instead of withdrawing and crediting € 6, it withdraws and credits € 12 (double) and i can't figure out where is the error.

For help, you may analyze the next script:

<?php
include('../connection.inc.php');

// function to make transactional-based statements

function do_queries($connection_id)
{
// move some money from one person to the other
//first statement of the transaction
$query = "BEGIN";
$result = mysql_query($query, $connection_id);
if(!$result)
{
return (0);
}
//another query to withdraw the money from Eve
$query = "UPDATE money SET amount = amount - 6 WHERE name = 'Eve'";
// execute this query
$result = mysql_query($query, $connection_id);
if(!$result)
{
return (0);
}
// another query to credit the withdrawn money to another person's account
$query = "UPDATE money SET amount = amount + 6 WHERE name = 'Ida'";
// execute the previous query

$result = mysql_query($query, $connection_id);
if(!$result)
{
return (0);
}
// query to commit the transaction if everything is Ok.
if($result)
{
$query = "COMMIT";
$result = mysql_query($query, $connection_id);
}

if(!$result)
{
return (0);
}
return (1);
}

if(!do_queries($connection_id))
{
echo "<pre>Transaction failed, rolling back. Error was:</pre><br>".mysql_error($connection_id).'<br>';
mysql_query("ROLL BACK");
}
if(do_queries($connection_id))
{
echo "Money transfer operation successful";
}
?>

[phparion]

hmm apparently there is no problem with your code you might be calling the function twice... print your update queries and run the script you will which query with what values and how many times is executed...

you are doing two bad-programming-practices that I'd like to point to,

1 - do not use user name in your query, there could be more than one user with the same name, if not now in the long run then, instead use unique key may be the userID i.e the primary key of users.

2 - you are using same variable names for both queries... that makes no affect as current variable overwrite previous variable values but still make your code more logical by using proper variable names.

[dbarasuk]

Yes you're right because indeed the program execute twice while it's not intended to.

I echoed the output ($result) after execution of every query. This is supposed to print 1 three times because there are 3 times mysql_query() occurences in that script. However I got 6 times 1 instead of 3 times which means that indeed the script executes 2 times. Is this due to an error in the browser or something beyond PHP?

Any help is appreciable.

dbarasuk

[cameraman]

It's because of this:

if(!do_queries($connection_id))
{
echo "<pre>Transaction failed, rolling back. Error was:</pre><br>".mysql_error($connection_id).'<br>';
mysql_query("ROLL BACK");
}
if(do_queries($connection_id))
{
echo "Money transfer operation successful";
}

Each time you check in the if statements, it's going to execute the function. You could either change that second if to a simple else or do something like this:

$response = do_queries($connection_id);

if(!$response) {
}
if($response){
}

[dbarasuk]

Cameraman,
Thanks it works as expected.

BUT:
The VERY first time i ran the program it executed with no error, i.e, it withdrew and credited the expected amounts and NOT the double of the amount. How can I explain that?

Thank you