What happens to a mysqli transaction when the connection is closed without a commit or rollback?

user_9682103 0
Viewed 301

Related questions, but not quite what I'd like to know, as I'm curious about the specific behavior of the PHP mysqli lib/functions:

Suppose I have some code like so (disregarding whether its good or bad practice):

# ... some code

$conn = new mysqli('localhost', 'widget-manager', 'secret-widgets', 'widgets');
$conn->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
if (false) {
    $sql = "UPDATE widgets SET widget_num = 1 WHERE widget_id = 5";
    $res = $conn->query($sql);
    if ($res) {
        $conn->commit();
    } else {
        $conn->rollback();
    }
}
$conn->close();

# ... some more code

The if which contains the commits and rollbacks would be skipped and the mysqli connection would be closed after the transaction was started, but before a commit or rollback was invoked.

Would the transaction also be immediately destroyed/ended/whatever-the-proper-term, or would it remain and possibly block other queries from other services?

mysqli mysql php
2 Answers

If the client disconnects, the MySQL Server cleans up the session. This rolls back any uncommitted transaction, releases row locks and table locks, removes temporary tables and session variables, etc.

It makes no difference which client interface is used (mysqli vs. PDO vs. Java vs. anything).

Bill_Karwin_208600

When you call begin_transaction() or set the autocommit value to 0 then you are effectively telling MySQL server "don't commit the data until I explicitly tell you to". When you write code that never calls commit then the data on the server will never be committed.

When you call mysqli::close() or if the PHP script ends, then mysqlnd (or libmysql) will send COM_QUIT command to the server. The server will then close the session and discard any data related to it, including open transactions, locks, or prepared statement handles. The MySQL server should also honour the wait_timeout setting in case the PHP script crashes and the command is never sent.

One thing that would be specific to mysqli is persistent connections. These connection are reused between PHP executions. They are commonly regarded as a good way to shoot yourself in the foot, which is why mysqli has some logic to help with that. You can use the INI setting called rollback_on_cached_plink to instruct mysqli to clean up the persistent connection whenever the script ends by issuing a rollback command.

user_18394390
Related