2

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?

user2028200
  • 33
  • 2
  • 4
  • Are you sure your transaction is still open and not already closed by commit when you try and check on the console? Might be a problem with isolation levels, so that the defaults aren't the same for your first and your second example... Perhaps this can help to find out if they're different? http://stackoverflow.com/questions/5347567/view-isolation-level-for-a-query-in-mysql – stef77 Jun 24 '13 at 14:19
  • I just tested it again. I am absolutely positive I did not close the transaction through PHP because I do not have `$db->commit()` anywhere in my script. But why am I able to start other transactions before I close the current one in PHP? This problem does not occur when I do transactions in the console. Does PHP automatically close the transaction for me even without running the `commit()` command? – user2028200 Jun 24 '13 at 15:29

1 Answers1

3

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.

stef77
  • 1,000
  • 5
  • 19