autocommit, commit and rollback

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
ChrisBull
Forum Commoner
Posts: 42
Joined: Fri Aug 20, 2010 7:43 am

autocommit, commit and rollback

Post by ChrisBull »

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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: autocommit, commit and rollback

Post by alex.barylski »

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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: autocommit, commit and rollback

Post by Weirdan »

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.
PCSpectra wrote: - the problem is at the point I capture exceptions I really don't have the DB object.
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

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

Post by alex.barylski »

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
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.

Cheers,
Alex
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: autocommit, commit and rollback

Post by alex.barylski »

So my understanding is correct though, that DB errors will rollback transactions implicitly?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: autocommit, commit and rollback

Post by Weirdan »

PCSpectra wrote:So my understanding is correct though, that DB errors will rollback transactions implicitly?
They should, but it's not always the case (in MySQL anyway). Further reading: http://dev.mysql.com/doc/refman/5.1/en/ ... dling.html
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: autocommit, commit and rollback

Post by josh »

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.
Post Reply