Single SQL command to get a value, then to update that value

Main question

Is there a SQL command where I can select a value from a table row, and within that same SQL command update that value?

Question in more detail

I’m working on an e-commerce site where I use MYSQL Database which has a products table that has a list of, wait for it, products.

Included in this table is a quantity field and a sold field.

The quantity indicates the total amount of that product that was made, and the sold is the current amount of that product that is sold/users purchased.

So if the sold field ever matches the quantity I want to update the front end HTML to indicate that it is OUT OF STOCK.

So when a user purchases ‘x’ amount of a product, I know I can do the following two SQL commands to update the sold field:

SELECT sold FROM products WHERE productID = ?

I’d back fill that ? with the id of the product that was just purchased.

After getting that sold value I’d add to that the amount the user just purchased, then do an UPDATE command such as:

UPDATE products SET sold = ? WHERE productID = ?

Is doing two different SQL Commands the best way to do this? or is it better to have this combined in a single SQL command?

Also, how could I best lock this specific product in the table using SQL statements, so that if a user A purchases product ‘xyz’ at the same time user B purchases the same product there isn’t a race condition for the sold field that I want to update?

UPDATE: I’m using java to communicate with the MYSQL database.

Answer

It’s better to do this as two separate SQL statements. In fact, it’s necessary.

You do it like this:

START TRANSACTION; 

SELECT sold FROM products WHERE productID = ? FOR UPDATE;

UPDATE products SET sold = ? WHERE productID = ?;

COMMIT;

(Those are the SQL statements, but adapting it to Java or whatever other programming language is up to you. There may also be Java methods to use for the start transaction and commit, those do the same thing, so it’s fine to use them.)

The FOR UPDATE acquires a lock on the rows examined, the same type of lock that would be acquired by an UPDATE. The lock is held until the transaction commits. So you can update it without concern about race conditions with concurrent sessions.

But make sure to do the SELECT, UPDATE, and COMMIT promptly. I see many developers do the SELECT… FOR UPDATE and then in between that and the UPDATE, their application does a bunch of other code, including things that might take a while, like calling the APIs of other services. You want to get to the COMMIT without delay, because the lock will make other concurrent sessions wait.