LEARN. PRACTICE. GROW.A transaction needs an owner
A repository function may be called alone or inside a larger operation. Calling beginTransaction when the connection already has a transaction does not automatically create a nested transaction. Define who starts, commits, and rolls back the work. A function participating in its caller’s transaction should not commit the entire operation without that contract’s authorization. inTransaction helps observe state but does not by itself identify ownership. In catch, trying rollBack without an active transaction can throw another exception and obscure the original failure. Record a possible cleanup failure separately and preserve the cause that initiated recovery.
Statement failure and batch failure
In the SQLite laboratory, a uniqueness violation using the usual ABORT behavior undoes the failed statement, not necessarily earlier statements in the transaction. If you catch the error and commit the rest, you may persist a partial batch. Start with the business rule: must a three-entry batch be accepted completely, or may individual entries be rejected? For the first rule, the owner rolls back the batch when an entry fails. For the second, define per-entry outcomes and suitable mechanisms, including savepoints where supported. Do not generalize SQLite behavior to PostgreSQL or MySQL without testing the engine, configuration, and error policy.
Savepoints do not commit outer work early
Imagine an operation inserting A, creating a savepoint, and attempting to insert B. ROLLBACK TO can return to that point without removing A. Releasing an inner savepoint does not commit the outer transaction; a later outer rollback can still remove both effects. Savepoint names and their lifecycle belong to the operation design and should not be interpolated from arbitrary input. Keep these local mechanisms short and understandable. An in-memory database per connection is also unsuitable for testing contention between workers: two independent sqlite::memory: connections see different databases. To exercise locking, the fixture uses two connections to the same temporary file.
Concurrency and repetition have different contracts
An UPDATE using an identifier and expected version can detect a decision calculated from stale state. If the first worker increments the version, the second may affect zero rows. Rereading and reassessing differs from removing the condition and forcing the write. Idempotency solves another problem: recognizing equivalent repetitions of one request. Store a key with normalized payload and outcome in the same transaction as the effect when that design applies. Reusing the key with incompatible payload needs a defined response. Losing the connection during commit can leave an unknown outcome; it does not automatically mean rollback or authorize another effect using a different key.
Laboratory: accept the whole batch
The example receives keys 1, 2, and 1. The third entry violates the primary key; the contract requires that no batch entry remains persisted. The function starting the transaction also decides rollback. Predict output zero and compare it with a variant that catches the exception inside the loop and commits the rest: that variant violates the exercise contract. Table creation occurs before the data transaction. This avoids teaching that DDL behaves identically across engines. The exercise demonstrates local SQLite rollback and does not reproduce network failures, replication, or distributed commit.
Summary: diagnose before retrying
To investigate a failed batch, identify transaction ownership, failure location, known durable state, and the acceptance rule. Distinguish permanent rejection, transient contention, version conflict, and unknown commit outcome. Each class needs a different decision. A bounded retry can help with transient contention; it does not repair invalid payload or resolve ambiguous acknowledgement by itself. Collect identifiers and states sufficient for reconciliation without exposing customer data. Connect this lesson to the outbox and production support: committing local data does not guarantee that an external system received a message. Integration tests must exercise the driver and engine actually used by the application.
<?php
declare(strict_types=1);
$db = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$db->exec('CREATE TABLE entries(id INTEGER PRIMARY KEY)');
$db->beginTransaction;
try {
$insert = $db->prepare('INSERT INTO entries(id) VALUES(:id)');
foreach ([1, 2, 1] as $id) {
$insert->execute(['id' => $id]);
}
$db->commit;
} catch (PDOException $failure) {
if ($db->inTransaction) {
$db->rollBack;
}
// The laboratory observes the rejected batch; a service must report failure.
}
echo $db->query('SELECT COUNT(*) FROM entries')->fetchColumnA batch containing a repeated key is rejected completely; catching its SQL error alone does not guarantee that policy.
Common pitfalls
Do not commit caller-owned transactions, assume whole-transaction rollback after every error, or test contention using separate in-memory databases.
Related topics: PDO transactions and partial failures · JSON, contracts, and explicit failures · Streams, CSV, and controlled imports
Ownership, batch acceptance, and recovery strategy must be explicit and tested on the engine being used.
Reference: SQLite: lang_transaction · PHP 8.5 reference; DR PHP 2026.3; new fixtures executed on PHP 8.4.4 / SQLite 3.51.2