I’m using pdo->rollback() for the first time. If i understand it correctly, after i begin a pdo transaction i should check the returned boolean of execute() and if it is successful run pdo->commit(), else call pdo->rollback(). In the below example i want to run pdo->execute() and then pdo->lastInsertId(). The way i have it currently setup what would happen if the pdo->execute() ran ok but the pdo->lastInsertId() had an error. I presume with this setup the pdo->commit() is going to run anyway? How would i set this up to check that the pso->execute and pdo->lastInsertId() both ran without errors before commiting and also rollback both of these if any error happens?
$this->pdo->beginTransaction();
// prepare the pdo query
$stmt = $this->pdo->prepare($sql);
// execute the query with its parameters
if ($stmt->execute($params)) {
// get the row id of the first query performed. This will be added as the foreign key of all child tables
$first_row_id = $this->pdo->lastInsertId();
// commit all the queries to the database
$this->pdo->commit();
} else {
$stmt->pdo->rollback();
}
try {
$this->pdo->beginTransaction();
// prepare the pdo query
$stmt = $this->pdo->prepare($sql);
// execute the query with its parameters
$stmt->execute($params);
// get the row id of the first query performed. This will be added as the foreign key of all child tables
$lastInsertId = $this->pdo->lastInsertId();
// commit all the queries to the database
$this->pdo->commit();
} catch (PDOException $e) {
$stmt->pdo->rollback();
print $e;
}
If the catch Exception is called before getting to the pdo->commit() is a rollback performed automatically even if you dont call pdo->rollback() inside the catch? I have read different answers on this. Is the rollback performed automatically but its better coding practice to call it anyway? I have tested it myself and it seems to be running the pdo->rollback() if i call it or not.
When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back. This is a safety measure to help avoid inconsistency in the cases where the script terminates unexpectedly–if you didn’t explicitly commit the transaction, then it is assumed that something went awry, so the rollback is performed for the safety of your data.
So only if your script terminates without explicitly calling rollback or commit, the transaction will be rolled back. Now think about that carefully. As the following scenario could (I haven’t tested this) produce unwanted results:
try {
$this->pdo->beginTransaction();
// prepare the pdo query
$stmt = $this->pdo->prepare($sql);
// execute the query with its parameters
$stmt->execute($params);
// get the row id of the first query performed. This will be added as the foreign key of all child tables
$lastInsertId = $this->pdo->lastInsertId();
// commit all the queries to the database
$this->pdo->commit();
} catch (PDOException $e) {
print $e;
}
try {
$this->pdo->beginTransaction();
// prepare the pdo query
$stmt = $this->pdo->prepare($sql);
// execute the query with its parameters
$stmt->execute($params);
// get the row id of the first query performed. This will be added as the foreign key of all child tables
$lastInsertId = $this->pdo->lastInsertId();
// commit all the queries to the database
$this->pdo->commit();
} catch (PDOException $e) {
$stmt->pdo->rollback();
print $e;
}
Assume the first try/catch fails at lastInsertId, so it hits the catch but doesn’t rollback. Now we start the next try/catch and that query succeeds and successfully calls commit. What will happen to your first failed transaction?
Or better yet
try {
$this->pdo->beginTransaction();
// prepare the pdo query
$stmt = $this->pdo->prepare($sql);
// execute the query with its parameters
$stmt->execute($params);
// get the row id of the first query performed. This will be added as the foreign key of all child tables
$lastInsertId = $this->pdo->lastInsertId();
// commit all the queries to the database
$this->pdo->commit();
} catch (PDOException $e) {
print $e;
}
try {
// prepare the pdo query
$stmt = $this->pdo->prepare($sql);
// execute the query with its parameters
$stmt->execute($params);
// get the row id of the first query performed. This will be added as the foreign key of all child tables
$lastInsertId = $this->pdo->lastInsertId();
} catch (PDOException $e) {
print $e;
}
For the first query you run into, a transaction is desired and necessary, but the second query doesn’t need it (as we really don’t care if a value is inserted or not). What happens if the first query fails on lastInsertId and the second query succeeds but we are now at the end of the script? Both will likely rollback.
Therefore it is of my own opinion, it is better to explicitly call it, then allow it to be automatically called at the termination of the script.
For the first bit, I may be wrong about this don’t think I am though, if you call PDO::beginTransaction() while another transaction is active - hasn’t been committed or rolled back- an exception will be thrown.
On the whole though, you are quite right. You don’t need to be relying on PHP/PDO to cleanup or rollback your transactions. The only way to properly ensure data integrity is to implicitly call rollback() or commit() as needed.
Yeah, I was thinking that too when I wrote it, which is why I added the second set of code which wouldn’t do that and would still perform unwanted results. Glad I wasn’t the only person who was thinking that (nested transactions just sound dangerous).
Glad I wasn’t the only person who was thinking that (nested transactions just sound dangerous).