SQL prevent select same field at same time

Question

i want to get last balance and update some transaction of xxx user from backend.. unfortunately, at the same time, xxx also do the transaction from frontend, so when I processed my query, xxx is processing same query too, so it get same last balance.

here is my script.
assume : xxx last balance is 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 do transaction from 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 done my query first, then user xxx may processed the query?

Answer 1

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

Here's the logic flow :

• LOCK TABLES "TableA" WRITE;

• Execute your first query

Then

• UNLOCK TABLES;

See:

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

Answer 2

This is one of the available approaches.

You have to use InnoDB engine for your table. InnoDB supports row locks so you won't need to lock the whole table for UPDATEing just one ROW related to a given user. (Table lock will prevent other INSERT/UPDATE/DELETE operations from being executed resulting in that they will have to wait for this table LOCK to be released).

In InnoDB you can achieve ROW LOCK when you are executing SELECT query by using FOR UPDATE. (but in this you have to use transaction to achieve the LOCK). When you do SELECT ... FOR UPDATE in a transaction mysql locks the given row you are selecting until the transaction is committed. And lets say you make SELECT ... FOR UPDATE query in your backend for user entry XXX and at the same time frontend makes the same query for the same XXX. The first query (from backend) that was executed will lock the entry in the DB and the second query will wait for the first one to complete, which may result in some delay for the frontend request to complete.

But for this scenario to work you have to put both frontend and backend queries in transaction and both SELECT queries must have FOR UPDATE in 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 frontend code should look very similar - having begin/commit transaction + FOR UPDATE.

One of the best thing about FOR UPDATE is that if you need a query to LOCK some row and do some calculations with this data in a given scenario but at the same time you need other queries that are selecting the same row and they do NO need the most recent data in that row, than you can simply do this queries with no transaction and with no FOR UPDATE in the end. So you will have LOCKED row and other normal SELECTs that are reading from it (of course they will read the old info ... stored before the LOCK started).