如何使用SQL禁止竞争条件1. SELECT，2。<change value>，3。UPDATE？

I want to create a function like the below:

function addValue($id, $new_value) {

    // Get the value from the database.
    $value = `SELECT value FROM table WHERE id = $id`

    // Somehow transform the value.
    $value .= $new_value

    // Put it back in the database.
    `UPDATE value SET value = $new_value WHERE id = $id`
}

The problem is, if another thread called addValue() at the same time, it might SELECT the old value before the first thread UPDATEs and therefore lose the value when the second thread UPDATEs using the old value.

Should I lock the table as below?

function addValue($id, $new_value) {

    // lock()?

    // Get the value from the database.
    $value = `SELECT value FROM table WHERE id = $id`

    // Somehow transform the value.
    $value .= $new_value

    // Put it back in the database.
    `UPDATE value SET value = $new_value WHERE id = $id`

    // unlock()?
}

You need to use a transaction and to acquire a write lock on the rows you will update (SELECT ... FOR UPDATE).

SET AUTOCOMMIT=0

SELECT value FROM table WHERE id = $id FOR UPDATE

...

UPDATE table SET value = $new_value WHERE id = $id

COMMIT

SET AUTOCOMMIT=1 

...

Also this only works with a transactional storage engine (InnoDB).

Your question brings up two main points:

• Ensuring data integrity between database operations is done through transactions, which are available through the different DB layer drivers such as Mysqli and PDO.
• If you need to extract the value before you update it, then the update should probably be relative, so it should be applied relatively to the existing field, preventing the need for a transaction. For example, you could define that the value is to be increased by 10, you run UPDATE table SET value = $increase + value WHERE id = $id;.