4

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?

Samuel Ludwig
  • 111
  • 1
  • 7
  • 1
    Please share more details. What's the scenario you want to cover? If your transaction contains no update or insert statements, what should be the problem? – Nico Haase Apr 19 '21 at 14:23
  • I want to know if the transaction would be left open on the DB side as it hadn't been explicitly committed or rolled back, and potentially block other queries. – Samuel Ludwig Apr 19 '21 at 14:26
  • 1
    Your code is weird. It only `begin_transaction()` once, but then does a `commit()` for each individual query. Why use transactions here at all? – KIKO Software Apr 19 '21 at 14:27
  • Code has been edited to be somewhat more straightfoward, but this is just an example to highlight the possible situation @KIKOSoftware, not something we actually would have in production. – Samuel Ludwig Apr 19 '21 at 14:31
  • If the connection gets closed, why should the transaction be kept open? – Nico Haase Apr 19 '21 at 14:32
  • That is precisely what I wanted to know, I suspected it would be closed, but found nothing explicitly saying so, hence me asking this question. If you would like to post an answer of "It would be automatically closed/commited upon the connection closing" @NicoHaase I would be happy to mark it as accepted. – Samuel Ludwig Apr 19 '21 at 14:35
  • What about https://stackoverflow.com/questions/16495989/mysql-and-php-pdo-what-happens-to-an-uncommited-transaction-if-connection-clos? – Nico Haase Apr 19 '21 at 14:36
  • Have a look in the [MySQL manual](https://dev.mysql.com/doc/refman/8.0/en/commit.html), at the bottom of this page: _"Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs)."_ and _"If a SELECT statement within a transaction calls a stored function, and a statement within the stored function fails, that statement rolls back. "_. Not a complete answer, I admit. It seems a rollback will be performed, in the end, when no commit was issued. – KIKO Software Apr 19 '21 at 14:36
  • Or this? https://stackoverflow.com/questions/2087377/mysql-php-transaction-behavior – Nico Haase Apr 19 '21 at 14:37
  • Otherwise, why not check for this on your own? According to https://stackoverflow.com/questions/7598373/display-open-transactions-in-mysql, you can list the open transactions – Nico Haase Apr 19 '21 at 14:38
  • So the consensus is that `mysqli` does not introduce any different behavior than a PDO-originating connection/query or an actual raw MySQL connection/query then. – Samuel Ludwig Apr 19 '21 at 14:40

2 Answers2

3

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
  • 538,548
  • 86
  • 673
  • 828
2

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135