MySQL / PHP transaction behavior

Question

Given:

mysql_query("START TRANSACTION");
mysql_query("UPDATE foo = 'bar'");
die();

die() stops the transaction before I can COMMIT or ROLLBACK. Looking at my tables, it appears that the update doesn't take place, but if I run queries later in another script, are they still part of that initial transaction? Is the transaction still 'open' until I COMMIT or ROLLBACK?

Answer 1

If die kills the connection to mysql then yes, the transaction is closed. If you're just leaving the transaction hanging though you're going to get in trouble.

I would recommend making sure that the transaction and the commit are in the same mysql_query if at all humanly possible to ensure that it actually happens.

Answer 2

Only if you continue working with the same resource ID. Otherwise it will be considered a new transaction. If the resource ID for the unfinished transaction is not used again it essentially is discarded.

Answer 3

It depends on your serialization level. If a transaction is neither committed or rolled back, it should time out after a while and rolled back in the DB. But until then it is an unfinished transaction which might cause inconsistent selects in other non serializable transactions. Dependent on it's level:

• read uncommitted: you can read the uncommitted, unfinished values until rollback
• repeatable read (mysql default): you won't read uncommitted values, but as ranges are not locked, a SELECT COUNT(*) FROM customers WHERE 18 < age AND age < 24 is not guaranteed to return consistent values until rollback