I have been browsing over at the php manual site on the documentation of the mysqli class and have found something that i might possible find useful but can't understand exactly what it does. Perhaps someone could enlighten me.
It seems to start of with setting autocommit to false bu doing this,
$mysqli->autocommit(FALSE);
But why are they doing this?
They then go and either use, $mysqli->commit() or $mysqli->rollback()
So what is the purpose of these functions?
Many Thanks
Chris
autocommit, commit and rollback
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: autocommit, commit and rollback
When you use a transactional table like InnoDB and an error occurs mid-way (or anyway for that matter) through a series of UPDATES, INSERTS, etc...everything is rolled back. This ensures data integrity in complex operations.
Now what confuses me or still evades me, is whether it's just SQL born errors, like invalid SQL (missing argument, no NULL, etc) or whether code errors as well, say I have several updates which are depdendent on third party services, such as a web service. When something fails and I capture that as an exception, do I manually have to invoke rollback() - I believe the answer is yes - the problem is at the point I capture exceptions I really don't have the DB object.
Cheers,
Alex
Now what confuses me or still evades me, is whether it's just SQL born errors, like invalid SQL (missing argument, no NULL, etc) or whether code errors as well, say I have several updates which are depdendent on third party services, such as a web service. When something fails and I capture that as an exception, do I manually have to invoke rollback() - I believe the answer is yes - the problem is at the point I capture exceptions I really don't have the DB object.
Cheers,
Alex
Re: autocommit, commit and rollback
PCSpectra wrote: Now what confuses me or still evades me, is whether it's just SQL born errors, like invalid SQL (missing argument, no NULL, etc) or whether code errors as well, say I have several updates which are depdendent
on third party services, such as a web service. When something fails and I capture that as an exception, do I manually have to invoke rollback() - I believe the answer is yes
Yes, when you detect error you have to rollback manually.
Then you're catching exceptions in the wrong place. The common pattern using exceptions and transactions is to catch exception at the level where you're performing your sql queries, rollback and rethrow the exceptionPCSpectra wrote: - the problem is at the point I capture exceptions I really don't have the DB object.
Code: Select all
<?php
/* ............ */
public function createAccount($data) {
$con = Registry::get('db');
$con->beginTransaction();
try {
$con->exec("
insert into users
set
username={$con->quote($data['username'])},
password=md5({$con->quote($data['password'] . Registry::get('passwordSalt'))})
");
$userId = $con->lastInsertId();
$con->exec("
insert into avatars
set
path={$con->quote($this->downloadAvatar($data['avatarUrl']))}, /* assuming it may throw an exception */
userid={$con->quote($userId)}
");
$con->commit();
} catch (Exception $e) {
$con->rollback();
throw $e;
}
}
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: autocommit, commit and rollback
I figured. I handle all exceptions in the bootstrap, however what I think I can probably do is throw the exception but pass along the DB object, then in the exception handling code, pull on the exception object and call rollback at that point.Then you're catching exceptions in the wrong place. The common pattern using exceptions and transactions is to catch exception at the level where you're performing your sql queries, rollback and rethrow the exception
Cheers,
Alex
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: autocommit, commit and rollback
So my understanding is correct though, that DB errors will rollback transactions implicitly?
Re: autocommit, commit and rollback
They should, but it's not always the case (in MySQL anyway). Further reading: http://dev.mysql.com/doc/refman/5.1/en/ ... dling.htmlPCSpectra wrote:So my understanding is correct though, that DB errors will rollback transactions implicitly?
Re: autocommit, commit and rollback
Yes, if your application issues a query it must be atomic. It will complete, or will not complete. If a query fails the whole transaction is tainted from a data quality standpoint, so the DB engine rolls it back (at least mine - innodb on mysql - tested by running 'foo' as a query after some valid inserts. An exception was thrown and the database was left empty).
On certain database engines running DDL like CREATE or DROP table will kick you back into auto commit mode, something to be aware of.
On certain database engines running DDL like CREATE or DROP table will kick you back into auto commit mode, something to be aware of.