I've recently had the need to use transactions, but I have a question about them. Does a rollback in PHP help any? You see, my preliminary tests show that if I start a transaction ("BEGIN;") but don't commit anything, then the query is not executed. Whenever my script finishes, and I don't commit my query, then it's as if I never executed my query. And another thing, if I insert and rollback, the autoincremental is still incremented.
Is this a normal behavior?

    zbee wrote:

    Whenever my script finishes, and I don't commit my query, then it's as if I never executed my query.

    Yes; that's the idea. If you don't commit, the query isn't committed.

      So then how is a rollback used?

        To help maintain integrity. Many operations on a database might require more than one record to be changed. If something craps out in the middle (an insert fails because of a foreign key constraint, or the script has a fatal error or maybe just decides that it doesn't want to proceed with what it's doing, or the power goes out on the server), the state of the database can/will be rolled back to what it was at the moment the transaction began so that things aren't left half-finished.

        The conventional example used in articles and books that cover the subject is that of transferring money between bank accounts. One account has its balance increased, the other has it decreased, and both have to be successful. Or both have to fail. If you're part way through before failing, you have to undo what you've already done. That's the rollback.

        It also allows one script to see a consistent view of the data (as it existed when the transaction started) without it being fiddled with by other transactions. If one banking process is in the middle of a transfer, and a second process happens to look at the accounts, it would not be a good thing for the second process to see a half-completed transfer.

        Pretty much any resource on what database transactions are will explain what database transactions are (such as the documentation of your DBMS, which would also explain how to use them on your system).

          If something craps out in the middle (an insert fails because of a foreign key constraint, or the script has a fatal error or maybe just decides that it doesn't want to proceed with what it's doing

          Ok, I get the picture, but on the one hand, I don't see any need to use a Rollback in PHP using MySQL because if there's an error, you simply don't get to the code that commits the query, and if it's not committed, then the changes are not reflected on the DB. Also, I know it's just an example, but I doubt any bank would use MySQL as their DB. Ayway, thanks a lot for the input. I appreciate it.

            zbee wrote:

            I don't see any need to use a Rollback in PHP using MySQL because if there's an error, you simply don't get to the code that commits the query

            But the script won't be able to do anything with the database either because the database it sees is in a bad state: it can't commit because the data is untrustworthy, and it can't make any further changes because they'll end up getting rolled back before anyone else sees them.

            but I doubt any bank would use MySQL as their DB

            I doubt any bank would use a DBMS that doesn't have transaction support. Besides, that's the first time anyone in this thread mentioned MySQL 🙂. Here's what the documentation in the DBMS I use has to say on the subject. First - Legal Requirements:

            Legal Notice

            PostgreSQL is Copyright © 1996-2006 by the PostgreSQL Global Development Group and is distributed under the terms of the license of the University of California below.

            Postgres95 is Copyright © 1994-5 by the Regents of the University of California.

            Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

            IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

            THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS-IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

            Check.

            3.4. Transactions

            Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

            For example, consider a bank database that contains balances for various customer accounts, as well as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from Alice's account to Bob's account. Simplifying outrageously, the SQL commands for this might look like

            UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

            The details of these commands are not important here; the important point is that there are several separate updates involved to accomplish this rather simple operation. Our bank's officers will want to be assured that either all these updates happen, or none of them happen. It would certainly not do for a system failure to result in Bob receiving $100.00 that was not debited from Alice. Nor would Alice long remain a happy customer if she was debited without Bob being credited. We need a guarantee that if something goes wrong partway through the operation, none of the steps executed so far will take effect. Grouping the updates into a transaction gives us this guarantee. A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.

            We also want a guarantee that once a transaction is completed and acknowledged by the database system, it has indeed been permanently recorded and won't be lost even if a crash ensues shortly thereafter. For example, if we are recording a cash withdrawal by Bob, we do not want any chance that the debit to his account will disappear in a crash just after he walks out the bank door. A transactional database guarantees that all the updates made by a transaction are logged in permanent storage (i.e., on disk) before the transaction is reported complete.

            Another important property of transactional databases is closely related to the notion of atomic updates: when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others. For example, if one transaction is busy totalling all the branch balances, it would not do for it to include the debit from Alice's branch but not the credit to Bob's branch, nor vice versa. So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously.

            In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with [font=monospace]BEGIN[/font] and [font=monospace]COMMIT[/font] commands. So our banking transaction would actually look like

            BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

            If, partway through the transaction, we decide we do not want to commit (perhaps we just noticed that Alice's balance went negative), we can issue the command [font=monospace]ROLLBACK[/font] instead of [font=monospace]COMMIT[/font], and all our updates so far will be canceled.

            PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a [font=monospace]BEGIN[/font] command, then each individual statement has an implicit [font=monospace]BEGIN[/font] and (if successful) [font=monospace]COMMIT[/font] wrapped around it. A group of statements surrounded by [font=monospace]BEGIN[/font] and [font=monospace]COMMIT[/font] is sometimes called a transaction block.

            Note: Some client libraries issue [font=monospace]BEGIN[/font] and [font=monospace]COMMIT[/font] commands automatically, so that you may get the effect of transaction blocks without asking. Check the documentation for the interface you are using.

            It's possible to control the statements in a transaction in a more granular fashion through the use of savepoints. Savepoints allow you to selectively discard parts of the transaction, while committing the rest. After defining a savepoint with [font=monospace]SAVEPOINT[/font], you can if needed roll back to the savepoint with [font=monospace]ROLLBACK TO[/font]. All the transaction's database changes between defining the savepoint and rolling back to it are discarded, but changes earlier than the savepoint are kept.

            After rolling back to a savepoint, it continues to be defined, so you can roll back to it several times. Conversely, if you are sure you won't need to roll back to a particular savepoint again, it can be released, so the system can free some resources. Keep in mind that either releasing or rolling back to a savepoint will automatically release all savepoints that were defined after it.

            All this is happening within the transaction block, so none of it is visible to other database sessions. When and if you commit the transaction block, the committed actions become visible as a unit to other sessions, while the rolled-back actions never become visible at all.

            Remembering the bank database, suppose we debit $100.00 from Alice's account, and credit Bob's account, only to find later that we should have credited Wally's account. We could do it using savepoints like this:

            BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

            This example is, of course, oversimplified, but there's a lot of control to be had over a transaction block through the use of savepoints. Moreover, [font=monospace]ROLLBACK TO[/font] is the only way to regain control of a transaction block that was put in aborted state by the system due to an error, short of rolling it back completely and starting again.

              Nice insight. It's clear as mud now 🙂. It makes sense now.

              I doubt any bank would use a DBMS that doesn't have transaction support. Besides, that's the first time anyone in this thread mentioned MySQL

              I mean, I was talking specifically about MySQL and PHP. If I didn't mention it inside the thread it was because of the title MySQL Transactions Rollback. But it was nice to learn all about transactions. Right now I don't really need all the complexity of Transactions, but it's nice to know how they work for future reference. Thanks a lot, man.

                Write a Reply...