I am new to using transactions.
Basically, I am able to successfully lock a single row in the MySQL console using START TRANSACTION followed by a SELECT ... FOR UPDATE statement. The row remains locked until I run the COMMIT command in the console
However, when I do the same in a PHP script using $db->beginTransaction() followed by $db->execute("SELECT ... FOR UPDATE"), the row does not get locked before I run a $db->commit() script.
What I mean is I can still successfully do a SELECT ... FOR UPDATE statement in the console even before I run a commit script in PHP to end the transaction. Does this mean my PHP script is not successfully locking the row during the transaction? How am I able to check?
PHP respectively PDO automatically closes and rolls back open transactions after the script has ended, and for very good reasons - generally, you do not want transactions staying open after the PHP parsing ends. See here: http://php.net/manual/en/pdo.transactions.php
When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back. This is a safety measure to help avoid inconsistency in the cases where the script terminates unexpectedly--if you didn't explicitly commit the transaction, then it is assumed that something went awry, so the rollback is performed for the safety of your data.
To test, you might add a sleep(120) after your execute, start the PHP script, switch to the MySQL console and issue statements there. I think you will experience the expected lock.