Is it possible for a SQL update query to run partially?

Vote count: 0

I'm maintaining a website I developed a couple of months ago now and there is a query that sometimes runs partially (or something).

It updates an account level and deducts its cost from the account balance. The code which runs the query is the next:

$last = ( microtime(true) - 600) * 10000; // 10 minutes ago
$sql = "UPDATE users SET level = " . $new_level . ", last_claim = '$last', level_days_left = 30, balance = balance - $price WHERE id = $user_id LIMIT 1";
$result = $conn->query($sql);

It usually works well but sometimes it doesn't :D When it doesn't the balance remains equal and the rest of the fields are changed fine.

The first time I noticed this I thought the issue was that the amount contained in $price could be 0. As the price is calculated extracting it from the database I added some checks like stopping the execution if the price extraction is not successful or if the amount extracted is 0.

Some days after that it failed again so I added a function to debug all those queries when run. This is the last query which failed:

UPDATE users SET level = 2, last_claim = '14866845029652', level_days_left = 30, balance = balance - 1000000 WHERE id = 9706 LIMIT 1

All the fields were changed except the balance. Can this happen? Doesn't it return any error? In case of error, why doesn't it revert the changes? And finally... what is the best way to handle this situtations? Do I run another query right after this one to check if the changes where successful or is this inefficient?

Some extra info:

• The database is MySQL
• The site is written in PHP
• balance is an unsigned bigint
• yes, the code checks that the balance is enough to pay that price
• balances are not big enough to overflow the bigint max value

And yep, not much more :D

asked 40 secs ago

jaimehgb

Let's block ads! (Why?)

Is it possible for a SQL update query to run partially?