Question

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

In SQLite, any command other than the SELECT will start an implicit transaction. Also, within a transaction a command like CREATE TABLE ..., VACUUM , PRAGMA , will commit previous changes before executing. Manual transactions are started with the BEGIN TRANSACTION statement and finished with the COMMIT or ROLLBACK statements.

SQLite supports three non-standard transaction levels: DEFERRED , IMMEDIATE , and EXCLUSIVE .

Examples

Now we will have some scripts that work with transactions.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(     
  "dbi:SQLite:dbname=test.db",
  "",             
  "",             
  { RaiseError => 1, AutoCommit => 0 },     
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)");
$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.

$ ./noautocommit.pl
$ sqlite3 test.db 
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Cars  Images

There is no Friends table in our database.

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:SQLite:dbname=test.db",
  "",             
  "",             
  { RaiseError => 1, AutoCommit => 0},     
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)");
$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.

sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian

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:SQLite:dbname=test.db",
  "",             
  "",             
  { 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::SQLite::db do failed: no such table: Friend at ./rollingback.pl line 15.
DBD::SQLite::db do failed: no such table: Friend at ./rollingback.pl line 15.
Issuing rollback() due to DESTROY without explicit disconnect() 
of DBD::SQLite::db handle dbname=test.db at ./rollingback.pl line 15.

Running the example will display this error message. The transaction is rolled back.

$ sqlite3 test.db 
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
sqlite> 

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:SQLite:dbname=test.db",
  "",             
  "",             
  { 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::SQLite::db do failed: no such table: Friend

We can see our custom error message from the handle_error() subroutine.

sqlite> SELECT * FROM Friends;
1|Thomas
2|Rebecca
3|Jim
4|Robert
5|Julian
6|Ronald

A new friend was inserted to the table.

In this part of the SQLite Perl tutorial, we have worked with transactions.