SQL prevents the selection of the same field at the same time

Question

I want to get the last balance and update the transaction of user xxx from the backend .. unfortunately, at the same time, xxx also performs a transaction with the interface, so when I process my request, xxx also processes the same request, so it receives the same last balance.

here is my script.
suppose: xxx last balance of 10000

 $transaction = 1000; $getData = mysqli_fetch_array(mysqli_query($conn,"select balance from tableA where user='xxx'")); $balance = $getData["balance"] - $transaction; //10000 - 1000 = 9000 mysqli_query($conn,"update tableA set balance='".$balance."' where user='xxx'"); 

at the same time, user xxx is making a transaction from the frontend.

 $transaction = 500; $getData = mysqli_fetch_array(mysqli_query($conn,"select balance from tableA where user='xxx'")); $balance = $getData["balance"] - $transaction; //10000-500 it should be 9000-500 mysqli_query($conn,"update tableA set balance='".$balance."' where user='xxx'"); 

how can I execute my request first, then user xxx can process the request?

Answer 1

This is one of the approaches available.

You need to use the InnoDB for your table. InnoDB supports row locking, so you don’t need to lock the entire table to UPDATE only one ROW associated with this user. (Locking the table will prevent other INSERT/UPDATE/DELETE operations from occurring, as a result of which they will have to wait until this LOCK table is released).

In InnoDB, you can achieve ROW LOCK when you execute a SELECT query using FOR UPDATE . (but in this you must use a transaction to achieve LOCK ). When you do SELECT ... FOR UPDATE in a transaction, mysql locks the selected row that you select until the transaction is completed. And suppose you make a SELECT ... FOR UPDATE query in your backend for user XXX, and at the same time, the interface makes the same query for the same XXX. The first request (from the backend) that was executed will block writing to the database, and the second request will wait for the completion of the first request, which may lead to some delay for the completion of the frontend request.

But for this script to work, you must specify both external and backend queries in the transaction, and both SELECT queries must have FOR UPDATE at the end.

So your code will look like this:

 $transaction = 1000; mysqli_begin_transaction($conn); $getData = mysqli_fetch_array(mysqli_query($conn,"SELECT balance FROM tableA WHERE user='xxx' FOR UPDATE")); $balance = $getData["balance"] - $transaction; //10000 - 1000 = 9000 mysqli_query($conn,"UPDATE tableA SET balance='".$balance."' WHERE user='xxx'"); mysqli_commit($conn); 

If this is your backend code, the external code should look very similar: start / complete transaction + FOR UPDATE .

One of the best FOR UPDATE is that if you need a query to LOCK certain row and do some calculations with this data in this scenario, but at the same time you need other queries that select the same row, and they DO NOT need the latest data on this line than you can just do these queries without a transaction and without FOR UPDATE at the end. That way, you will have the LOCKED line and other normal SELECTs that are read from it (of course, they will read the old information ... stored before the start of LOCK ).

Answer 2

You can lock the table " TableA " with the MySQL LOCK TABLES command.

Here's the logical thread:

• LOCK TABLES "TableA" WRITE;

• Fulfill your first request

Then

• UNLOCK TABLES;

Cm:

http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html

Answer 3

Using the InoBD engine and transactions to make it ACID ( https://en.wikipedia.org/wiki/ACID )

 mysqli_begin_transaction($conn); ... mysqli_commit($conn) 

In addition, why do not you use the request to increase balance

 mysqli_query($conn,"update tableA set balance= balance + '".$transaction."' where user='xxx'"); 

Answer 4

Basically you can use two ways:

• Table lock
• Using transactions.

The most common in this situation is the use of transactions to make sure that all the operations you perform are atomic. This means that if one step fails, everything goes back before the changes begin.

You can usually also perform an operation in a query, for something as simple as this. Because database engines are more than capable of performing simple calculations. In this situation, you can check whether the user really has enough credits in his account, which, in turn, indicates the need for verification. I would just transfer the check after you deducted the amount, just to be safe. (Protection against racing conditions, etc.)

A quick example you started with:

 $conn = new mysqli(); /** * Updates the user credit with the amount specified. * * Returns false if the resulting amount is less than 0. * Exceptions are thrown in case of SQL errors. * * @param mysqli $conn * @param int $userID * @param int $amount * @throws Exception * @return boolean */ function update_credit (mysqli $conn, $userID, $amount) { // Using transaction so that we can roll back in case of errors. $conn->query('BEGIN'); // Update the balance of the user with the amount specified. $stmt = $conn->prepare('UPDATE `table` SET `balance` = `balance` + ? WHERE `user` = ?'); $stmt->bind_param ('dd', $amount, $userID); // If query fails, then roll back and return/throw an error condition. if (!$stmt->execute ()) { $conn->query ('ROLLBACK'); throw new Exception ('Count not perform query!'); } // We need the updated balance to check if the user has a positive credit counter now. $stmt = $conn->prepare ('SELECT `balance` FROM `table` WHERE `user` = ?'); $stmt->bind_param ('d', $userID); // Same as last time. if (!$stmt->execute ()) { $conn->query ('ROLLBACK'); throw new Exception ('Count not perform query!'); } $stmt->bind_result($amount); $stmt->fetch(); // We need to inform the user if he doesn't have enough credits. if ($amount < 0) { $conn->query ('ROLLBACK'); return false; } // Everything is good at this point. $conn->query ('COMMIT'); return true; } 

Answer 5

Perhaps your problem is just a way to maintain balance. Why did you put it in the box? You are losing the entire history of transactions committing this.

Create table: transaction_history. then, for each transaction, execute the INSERT request, passing to the user the transaction value and the operation (deposit or withdrawal).

Then, to show your user your current balance, simply make a SELECT in your entire transaction history, performing the operations correctly, in the end he will see the actual correct balance. And you also prevent an error from executing two UPDATE queries at the same time (although "at the same time" it is not as common as we think).

Answer 6

You can use the transaction as follows. $ balance is the balance you want to deduct. If the request is executed correctly, the updated balance will be displayed, otherwise it will be rolled back to its original position, and the exception error will show you a failure error.

 try { $db->beginTransaction(); $db->query('update tableA set balance=balance-'".$balance."' where user='xxx'" '); $db->commit(); } catch (Exception $e) { $db->rollback(); }