CODEWITHSUNDEEP
phpmysqltransactionsinnodb
user2441938
user2441938

Reputation: 145

MySQL Make concurrent SELECT ... FOR UPDATE queries fail instead of waiting for lock to be released

In my PHP code I'm trying to make an innoDB database transaction be ignored if another thread is already performing the transaction on the row. Here's some code as an example:

$db = connect_db();
try{
    $db->beginTransaction();
    $query = "SELECT val FROM numbers WHERE id=1 FOR UPDATE"; //I want this to throw an exception if row is already selected for update
    make_query($db,$query); //function I use to make queries with PDO

    sleep(5); //Added to delay the transaction
    $num = rand(1,100);

    $query = "UPDATE numbers SET val='$num' WHERE id=1";
    make_query($db,$query);
    $db->commit();
    echo $num;
}
catch (PDOException $e){
    echo $e;
}

When it makes the SELECT val FROM numbers WHERE id=1 FOR UPDATE query I need some way of knowing through php if the thread is waiting for another thread to finish it's transaction and release the lock. What ends up happening is the first thread finishes the transaction and the second thread overwrites its changes immediately afterwards. Instead I want the first transaction to finish and the second transaction to rollback or commit without making any changes.

Upvotes: 0

Views: 1351

Answers (1)

Arth
Arth

Reputation: 13110

Consider simulating record locks with GET_LOCK()

Choose a name specific to the rows you want locking. e.g. 'numbers_1'.

Call SELECT GET_LOCK('numbers_1',0) to lock the name 'numbers_1'.. it will return 1 and set the lock if the name is available, or return 0 if the lock is set already. The second parameter is the timeout, 0 for immediate. On a return of 0 you can back out.

Use SELECT RELEASE_LOCK('numbers_1') when you are finished.

Be aware; calling GET_LOCK() again in a transaction will release the previously set lock.

Upvotes: 2

Related Questions