Search code examples
mysqlpdoinnodb

InnoDB Select For Update

If I have a simple statement as such for an InnoDB Table

UPDATE <table> SET locked=1, col2=<Val2> WHERE locked=0

Is there any need to really do SELECT FOR UPDATE?

Since InnoDB supports row level locking is there any chance of a collision even thousands of clients are executing the same script simultaneously?

Update:

Something like this would prevent locks

$dbh = new PDO(DSN, DB_USER, DB_PASS);
$dbh->beginTransaction();
$selQuery = $dbh->prepare("SELECT <col> FROM <table> WHERE status=0 LIMIT 1 FOR UPDATE");
$selQuery->bindColumn(<col1>, $col1);
$selQuery->execute();

$selQuery->fetch(PDO::FETCH_BOUND);

$dbh->query("UPDATE <table> SET status=1 WHERE status=0 LIMIT 1");

$dbh->commit();
Solution

  • Yes, collisions can always occur. So using the design pattern:

    SELECT FOR UPDATE for all the resources in a given transaction first, can prevent or shorten deadlock situations.

    Suppose the following scenario:

    • Process A updates table 1 & 2 in the order 1,2
    • Process B updates table 1 & 2 in the order 2,1

    Now process A updates, table 1, with process B updating table 2 at the same time, causing a deadlock (assumption is that the same "records/pages" are hit by this update).

    If however the SELECT FOR UPDATE would be used in the start of the transaction, the transaction would block at the start since table 2 (or 1 whomever is faster) can not be locked (yet). The key part here is "At the start of the transaction", if you do it later, then just running the UPDATE is just as efficient.

    Key is always to keep your transactions atomic and fast: Group the SQL logic so it can execute with the least amount of other code in between, keep lock times as short as possible.