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

Question

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()?
}

Answer 1

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).