$stmt = $pdo->prepare("SELECT somedata FROM mytable FOR UPDATE");
$pdo->beginTransaction();
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ( $row == FALSE ) {
$pdo->rollBack();
} else {
....run some code and then commit
Is the rollBack necessary because it is only called when there isn't a row locked due to no row being returned? Are any resources wasted by selecting for update and never rolling back or committing in the case when nothing is actually selected?
In a select for update, the rows selected get a rowlock (have to be innodb of course, but I'm assuming you know that) based on the way you have used beginTransaction which sets autocommit to 0.
To release the locks you can commit OR rollback.
Personally, I would reserve rollback for error conditions where a rollback would be appropriate, so I would not rollback, but just end the block with a commit with the entire thing inside a try -- catch block. Then you do not need to be concerned about the unlock mechanics or whether there were rows selected or not.
try {
$stmt = $pdo->prepare("SELECT somedata FROM mytable FOR UPDATE");
$pdo->beginTransaction();
$stmt->execute();
while ($row = ....) {
}
$pdo->commit();
} catch( PDOException $Exception ) {
$pdo->rollback();
}
A rollback is only necessary if you have an open transaction and the data in one or more tables have been altered. A SELECT statement by itself is not going to need to be rolled back.
According to documentation - yes. As you need a transaction to perform such a lock.
However, if you just drop your connection soon, explicit rollback is not required
