Atomic Point Transfers: Fixing Your MySQL Update Logic
Handling user points or credits in a database requires more than just running two separate UPDATE statements. The specific scenario you describedâa user clicking a button to transfer one point from their own account to another userâis a classic atomicity problem. If the first update succeeds (deducting from the sender) but the second fails (adding to the receiver), your application ends up in an inconsistent state where points have simply vanished.
The code snippet provided demonstrates a common pitfall: executing queries sequentially without ensuring that both succeed or fail together. In your current implementation, if $result1 returns true but $result2 encounters a constraint violation (such as a foreign key error) or a deadlock, the transaction is rolled back in some environments but not others depending on how the connection is managed. If you are running this outside of an explicit transaction block, you risk data loss where points disappear without being assigned to anyone.
To solve this, you must wrap your logic in a database transaction. This ensures that if the second update fails for any reason, the first one is automatically undone, preserving the integrity of the total point count across all users.
Here is how you would refactor the PHP logic to handle this safely:
include ('../functions.php');
$userid = mysql_real_escape_string($_POST['user_id']);
$posterid = mysql_real_escape_string($_POST['poster_id']);
if (loggedin()) {
include ('../connection.php');
// Ensure we are working with a transactional connection
$conn = mysqli_set_transaction_isolation_level($mysqli, 'REPEATABLE READ');
// Start the transaction
$conn->begin_transaction();
try {
$query1 = "UPDATE `points` SET `points` = `points` - 1 WHERE `userID` = '$userid' AND `points` > 0";
$result1 = mysqli_query($conn, $query1);
if ($result1) {
$query2 = "UPDATE `points` SET `points` = `points` + 1 WHERE `userID` = '$posterid'";
$result2 = mysqli_query($conn, $query2);
// Only commit if both queries succeed
if ($result2) {
$conn->commit();
echo "Successful";
return 1;
} else {
throw new Exception("Failed to credit recipient.");
}
} else {
throw new Exception("Failed to debit sender.");
}
} catch (Exception $e) {
// Rollback on any error
$conn->rollback();
echo "Error: " . mysqli_error($conn);
return 0;
}
// Explicitly close the transaction if no exception was thrown
$conn->commit();
}
There is a critical logic error in your original query regarding the validation of the result variables. You wrote if ($result1 && result2). In PHP, result2 without the $ prefix evaluates to false if it hasn't been defined or assigned yet, and even if assigned, it does not check the return status of the query function. You must explicitly check $result2. Furthermore, notice the addition of a check (AND points > 0) in the first query. Without this, attempting to subtract from a user who already has zero points could result in a negative balance or an error depending on your schema constraints.
Another significant consideration is the use of mysql_real_escape_string. You are using functions from the deprecated mysql_* extension, which was removed in PHP 7.0. While your current code might run on older servers, modern development requires migration to mysqli or PDO. The example above uses mysqli, but you should also consider prepared statements to prevent SQL injection entirely. Directly interpolating user input into a string, even after escaping it, is an anti-pattern that introduces security risks if the escaping logic ever shifts or becomes incompatible with future PHP versions.
If you are building this on a modern stack, specifically within the Laravel ecosystem, handling these operations becomes significantly more robust through Eloquent models and database transactions managed by the framework itself. For instance, you could define a scope or method in your User model to handle the deduction and credit logic atomically using $model->getConnection()->beginTransaction(). This approach abstracts away the connection management and ensures that the transaction scope is correctly applied even if complex logic is involved.
Ultimately, the goal is to ensure that the sum of all points in the points table remains constant after every interaction. By enforcing atomicity through transactions and validating inputs against actual data constraints before executing updates, you eliminate the race conditions and silent failures that plague point systems.