MySQL transactions with Perl
In this chapter, we will work with transactions. First we provide some basic definitions. Then we present Perl scripts that show, how to work with transactions in Perl DBI. We will also talk about the autocommit mode, which is essential to understand when dealing with transactions.
Definitions
A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back. In the autocommit mode the changes are immediately effective. To work with transactions we either turn the autocommit mode off or start a transaction with the begin_work() method. The transaction is ended with either the commit() or rollback() methods.
The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.
The database connection is in the autocommit mode by default. The AutoCommit database handle attribute is used to set or read the autocommit mode.
When the AutoCommit is on, the call to the begin_work() turns the AutoCommit off. The commit() and rollback() methods turn the AutoCommit back on. If we turn the AutoCommit attribute off and then later call the begin_work() method, we receive an error message that we are already in a transaction.
Examples
Now we will have some scripts that work with transactions.
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect(
"dbi:mysql:dbname=mydb",
"user12",
"34klq*",
{ RaiseError => 1, AutoCommit => 0 },
) or die $DBI::errstr;
$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT,
Name TEXT) ENGINE=InnoDB");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");
$dbh->disconnect();
We create a Friends table and try to fill it with data. However, as we will see, the data will not be committed.
{ RaiseError => 1, AutoCommit => 0 },
We have set the AutoCommit parameter to 0. Changes are not automatically committed. And there is no commit statement. So the changes are not written to the database.
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT,
Name TEXT) ENGINE=InnoDB");
This is the SQL statement to create a Friends table. We have specified the InnoDB engine. Note that since MySQL 5.5 the default engine is InnoDB. In MyISAM tables, SQL statements are committed after they are executed. MyISAM tables do not support transactions.
$ ./noautocommit.pl mysql> SELECT * FROM Friends; Empty set (0.00 sec)
The table is created but the data is not inserted into the table.
In the second example we will write the data into the database with the commit() method.
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect(
"dbi:mysql:dbname=mydb",
"user12",
"34klq*",
{ RaiseError => 1, AutoCommit => 0 },
) or die $DBI::errstr;
$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT,
Name TEXT) ENGINE=InnoDB");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");
$dbh->commit();
$dbh->disconnect();
When the autocommit mode is turned off, every statement is within a transaction until we call the commit() method.
$dbh->commit();
All changes are written to the database.
mysql> SELECT * FROM Friends; +----+---------+ | Id | Name | +----+---------+ | 1 | Tom | | 2 | Rebecca | | 3 | Jim | | 4 | Robert | | 5 | Julian | +----+---------+ 5 rows in set (0.00 sec)
We verify with the sqlite3 command line tool that the changes were written.
When there is an error in the transaction, the transaction is rolled back an no changes are committed to the database.
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect(
"dbi:mysql:dbname=mydb",
"user12",
"34klq*",
{ RaiseError => 1, AutoCommit => 0},
) or die $DBI::errstr;
$dbh->do("UPDATE Friends SET Name='Thomas' WHERE Id=1");
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");
$dbh->commit();
$dbh->disconnect();
In the code example the autocommit is turned off. There are two statements which form a transaction. There is an error in the second SQL statement. Therefore the transaction is rolled back.
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");
The name of the table is incorrect. There is no Friend table in the database.
$ ./rollingback.pl DBD::mysql::db do failed: Table 'mydb.Friend' doesn't exist at ./rollingback.pl line 14. DBD::mysql::db do failed: Table 'mydb.Friend' doesn't exist at ./rollingback.pl line 14. Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle dbname=mydb at ./rollingback.pl line 14.
Running the example will display this error message. The transaction is rolled back.
mysql> SELECT * FROM Friends; +----+---------+ | Id | Name | +----+---------+ | 1 | Tom | | 2 | Rebecca | | 3 | Jim | | 4 | Robert | | 5 | Julian | +----+---------+ 5 rows in set (0.00 sec)
No changes took place in the Friends table, even if the first UPDATE statement was correct.
As we have already mentioned in the tutorial, the default mode is autocommit. In this mode we can start a new transaction with the begin_work() method and finish it with either commit() or rollback(). The begin_work() method will turn off the autocommit, the commit() and the rollback() methods will turn the autocommit back on.
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect(
"dbi:mysql:dbname=mydb",
"user12",
"34klq*",
{ RaiseError => 1, HandleError=>\&handle_error },
) or die $DBI::errstr;
$dbh->begin_work();
$dbh->do("UPDATE Friends SET Name='Thomas' WHERE Id=1");
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");
$dbh->commit();
$dbh->do("INSERT INTO Friends(Name) VALUES('Ronald')");
$dbh->disconnect();
sub handle_error {
my $error = shift;
print "An error occurred in the script\n";
print "Message: $error\n";
return 1;
}
Again we have an incorrect second SQL statement. This time we do not explicitly turn off the autocommit.
{ RaiseError => 1, HandleError=>\&handle_error },
We will delegate error handling to the handle_error() subroutine.
$dbh->begin_work();
With the begin_work() method, we start a new transaction. The autocommit is turned off.
$dbh->do("UPDATE Friends SET Name='Thomas' WHERE Id=1");
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");
These two statements form a transaction. The second one is incorrect.
sub handle_error {
my $error = shift;
print "An error occurred in the script\n";
print "Message: $error\n";
return 1;
}
This subroutine is called when we encounter an error. We print an error message. Note that the script is not exited.
$dbh->do("INSERT INTO Friends(Name) VALUES('Ronald')");
The transaction was rolled back and we did not exit the script. It continues. After the rollback, the autocommit was turned back on. A new row was added to the Friends table.
$ ./rollingback2.pl An error occurred in the script Message: DBD::mysql::db do failed: Table 'mydb.Friend' doesn't exist
We can see our custom error message from the handle_error() subroutine.
mysql> SELECT * FROM Friends; +----+---------+ | Id | Name | +----+---------+ | 1 | Thomas | | 2 | Rebecca | | 3 | Jim | | 4 | Robert | | 5 | Julian | | 6 | Ronald | +----+---------+ 6 rows in set (0.00 sec)
A new friend was inserted to the table.
In this part of the MySQL Perl tutorial, we have worked with transactions.