How do I lock tables using DBI and mySQL?

[filmo]

How do I lock tables using DBI and mySQL?

Originally posted as a Categorized Question.

[blakem]

See the Lock Tables syntax in the MySQL documentation. Then wrap each sql query inside of something like:

$dbh->do(" ... ");

[filmo]

Follow up question: Does the lock cover multiple statement handles? i.e.

$dbh->do("LOCK TABLES foo WRITE, bar READ");
$sth->prepare("SELECT x,y,z FROM bar");
$sth2->prepare("INSERT INTO foo SET a = ?");
while (@ary = $sth->fetchrow_array()) {
    $sth2->$execute($ary[0]);
    }
$sth2->finish();
$sth->finish();
$dbh->do("UNLOCK TABLES");

Is this correct or will $sth2 not be able to access the tables because of the lock?

[blakem]

Yes, I that should work... Table locking is done at the per-thread level, and I think both statement handles above exist within the same thread.