Why do some operations performing positioned update and delete fail when AutoCommit is on?
$sth = $dbh->prepare( “SELECT * FROM ORDERS WHERE user_id < 5 FOR UPDATE OF comment"); $sth->execute; while (@res = $sth->fetchrow_array) { $dbh->do(“UPDATE ORDERS SET comment = ‘Wonderful’ WHERE CURRENT OF $sth->{CursorName}”); } When AutoCommit is on, a transaction is started within prepare(), and committed automatically after the last fetch(), or within finish(). Within do(), a transaction is started right before the statement is executed, and gets committed right after the statement is executed. The transaction handle is stored within the database handle. The driver is smart enough not to override an active transaction handle with a new one. So, if you notice the snippet above, after the first fetchrow_array(), the do() is still using the same transaction context, but as soon as it has finished executing the statement, it commits the transaction, whereas the next fetchrow_array() still needs the transaction context! So the secret to make this work is to keep the transaction open. T
