Bug: MySQL errno 1213 leaves transDepth set so the next write autocommits #10649

PHP Version

8.5

CodeIgniter4 Version

4.7.5 (measured in a lab). Still present on develop at df8b96bee (4.7.6-dev) by source read: MySQLi\Connection::execute() does not treat errno 1213 specially, and transCommit() returns true when mysqli::commit() returns true.

CodeIgniter4 Installation Method

Git

Which operating systems have you tested for this bug?

macOS

Which server did you use?

cli

Environment

testing

Database

MySQL 8.4.11, MySQLi, mysqlnd. InnoDB. No production failure has been observed. This is a lab measurement.

What happened?

Errno 1213 is ER_LOCK_DEADLOCK (Deadlock found when trying to get lock; try restarting transaction). InnoDB has already rolled the victim transaction back. CodeIgniter leaves transDepth at 1.

Measured on CodeIgniter 4.7.5:

1. The victim transaction is rolled back by InnoDB. The victim row is gone.
2. transDepth stays 1.
3. A later insert on that same connection autocommits.
4. transComplete() returns false and does not undo that insert.
5. transCommit() returns true while the victim row is absent.
6. mysqli::commit() returns true in the same situation. CodeIgniter treats that true as a successful commit of the transaction it still has open.

transComplete() sees transStatus === false (because query() called handleTransStatus()) and calls transRollback(). The later insert is already committed, so that rollback does not remove it. transCommit() does not look at transStatus. It calls _transCommit() → mysqli::commit(), which returns true, then decrements transDepth and returns true.

Errno 1205 is not this bug. It was measured separately. The message is try restarting transaction both when innodb_rollback_on_timeout is OFF and when it is ON, and those two settings leave the transaction in different states. This report is only errno 1213, where InnoDB has rolled the victim back.

Steps to Reproduce

1. Two connections, InnoDB tables, two rows that can be locked in opposite orders.
2. On the victim connection, transBegin() and insert a row that exists only inside that transaction.
3. Produce errno 1213 on that connection (Deadlock found when trying to get lock).
4. Observe transDepth === 1 and the victim row already absent.
5. Insert a second row on that same connection.
6. That second row is visible from another connection (autocommit).
7. transComplete() returns false and the second row is still present.
8. On a fresh run of steps 1–5, transCommit() returns true while the victim row is absent.

Expected Output

After InnoDB has rolled the victim back, a following write on that connection must not autocommit. transCommit() must not return true while the victim row is absent.

Anything else?

Searched issues and pull requests, open and closed, before opening this. Nothing already covers this defect.

Neighbors that do not:

• feat: classify retryable transaction exceptions #10162 (merged to 4.8, not develop): classifies MySQLi errno 1213 as RetryableTransactionException. Source read of 4.8 at e710fc3d9: isRetryableTransactionErrorCode() returns true for 1213 only. It does not start a new server transaction, and transCommit() still returns true when _transCommit() does. Runtime of the autocommit behavior was measured on 4.7.5, not re-run on 4.8.
• feat: add retry attempts to database transactions #10197 (merged to 4.8): retries the closure transaction() helper after RetryableTransactionException. The retry condition requires transDepth === 0 after rollback. It is not a fix for a manual transBegin() / transCommit() caller, and it is not on develop.
• refactor: Clean up mysqli transactions #9888 (merged): stops MySQLi from pairing autocommit(false) with begin_transaction(). After that, a connection with no server transaction is in autocommit mode, which is why the post-1213 insert commits.
• Bug: CURLrequest deadlock on cookie #4035 (closed): cURL cookie deadlock. Not InnoDB.
• Dev: DB transactions after v4.3 #6909 (open): follow-up list for DBDebug, resetTransStatus(), and model validation inside a transaction. It does not mention errno 1213.
• feat: add BaseConnection::resetTransStatus() #8767 (closed): resetTransStatus() after a strict-mode failure. It sets transStatus back to true. It does not detect a server-side rollback.
• Bug: updateBatch() silently ignores chained where() conditions, creating unsafe broad-update footgun #10234 / fix: prevent updateBatch with existing where conditions #10236: updateBatch() plus an existing Query Builder WHERE. Different defect. Please do not treat this report as a reopen of those.

Please do not fold errno 1205 into a fix for this issue.