Previous Page Next Page

17.6. Transactions

In the simple example of the teams table, if when the data is inserted for two teams, and the number of wins and losses for the two teams is accidentally swapped, an update would require both teams be modified, not just one. Suppose you are updating more than one table and the update statements in one table succeed and those in the other fail. For example, a classic example is that you take money out of a savings account in one table and put it in a checking account in another table. The deposit succeeds but the withdrawal fails. The tables are then in an inconsistent state. A transaction is a set of SQL statements that succeed or fail all as a unit. For example, INSERT, UPDATE, and DELETE statements may be executed as a group. If one fails, then none of the statements is executed.

By default, MySQL runs with autocommit mode enabled. DBI also runs with autocommit mode on by default. This means that as soon as you execute any statement that modifies a table, as long as no errors are returned, MySQL immediately commits the statement to the database, and any changes to the affected tables are made permanent.

To use transactions with MySql, autocommit mode must be disabled. We can do that in a Perl script when connecting to the database by setting the hash value of AutoCommit => 0 as shown in Example 17.43.

In the examples shown so far, when we connected to a database, the hash options available to the connect() method for error handling were used, PrintError and RaiseError. To use transactions, we need to turn off the AutoCommit attribute, turn RaiseErrors on, and optionally leave PrintError "on" or "off," "on" being the default.

Example 17.43. 

1 my $dbh = DBI->connect( 'dbi:mysql:sample_db','root','quigley1',
                          {
                          PrintError => 0,
                          RaiseError => 1,
2                         AutoCommit => 0
                          }

RaiseError tells DBI to die with the $DBI::errstr message if there are errors, and PrintError, by default turned on, tells DBI to send a warning with the $DBI::errstr and the program will continue to execute.

Commit and Rollback

Commit means in a transaction that a set of statements will be executed and sent to the database as a group. If all of the statements are successful, the group is committed and the database is modified. If, however, there is an error in any one of the statements in the group, a rollback command is issued, which returns all the tables back to their previous state.

Transactions are often handled in Perl by using an eval block to trap errors, then using the commit() or rollback() methods to finish the transaction.

In the following example, a group of records will be inserted into a table. If an error occurs in the process of adding these entries, the entire process will be rolled back. The error could be because an entry already exists.

Example 17.44.

Code View:
1  use DBI qw(:sql_types);

2  my $dbh = DBI->connect('dbi:mysql:sample_db','root','quigley1',
                        {
                          PrintError => 0,
3                         RaiseError => 1,
4                         AutoCommit => 0
                        }
         ) or die "Connection to sample_db failed: $DBI::errstr";
5  my @rows = ( # New rows to be inserted
               [ 'Tampa Terrors', 3, 5 ],
               [ 'Los Alamos Lizzards', 12, 3 ],
               [ 'Detroit Demons', 22, 0 ],
               [ 'Cheyenne Chargers',6, 0 ],
          );
6  my $sql = qq{ INSERT INTO teams VALUES ( ?, ?, ? ) };
7  my $sth = $dbh->prepare( $sql );
8  foreach $param (@rows) {
9    eval { # The eval block is used to catch errors
10   $sth->bind_param( 1, $param->[0], SQL_VARCHAR );
     $sth->bind_param( 2, $param->[1], SQL_INTEGER );
     $sth->bind_param( 3, $param->[2], SQL_INTEGER);
     $sth->execute() or die;
     };
   }
11 if( $@ ) { # If eval failed. $@ is set to the error that occurred
     warn "Database error: $DBI::errstr\n";
12   $dbh->rollback(); # Reverse all commit statements
   }
   else{
13   $dbh->commit();
     print "Success!\n";
   }
   $sth->finish();
   $dbh->disconnect();

Explanation

  1. Constants representing the values of the SQL standard types are included with the special DBI :sql_types tag. The constants are used by the bind_param method starting on line 10.

  2. Connection to the MySQL database is made.

  3. The RaiseError attribute is turned on to catch exceptions and die if there is one.

  4. The AutoCommit attribute is turned off, so that SQL statements are not automatically sent to the database but must be manually committed.

  5. A list of of anonymous arrays is created to represent the rows that will be inserted into the table.

  6. A SQL statement is created to insert new teams later; the values to be substituted for the ? placeholders.

  7. The SQL statement is prepared. A statement bundle is returned.

  8. The foreach loop is used to iterate through each of the rows that will be added.

  9. The eval block is entered. If an error occurred, it will be assigned to the special variable, $@. See line 11.

  10. The bind_param() method binds the first parameter to the first (?) placeholder in line 6. The first parameter, $param->[0] is 'Tampa Terrors', the first time in the loop. It is of type SQL_VARCHAR. Next, the second parameter, param->[1] is bound to the second placeholder (?); the first time through the loop, it represents the number of wins, i.e., three, etc.

  11. If one of the statements in the eval block failed, this variable will be set to the error.

  12. If there was an error in the execution of any of the statements in the eval block, all of them will be discarded. The database will be rolled back to its original state. In other words, if one transaction fails, nothing is done.

  13. If there were no errors, this block is executed, and all of the inserts will happen.

Previous Page Next Page