Perl MySQL Transaction

Summary: in this tutorial, you will learn how to handle database transactions by using Perl DBI to ensure the integrity of the data.

By definition, a database transaction is a set of SQL statements that execute in an all-or-nothing manner. If all SQL statements are executed successfully, the transaction is considered to be successful. A failure of any SQL statement will cause the system to roll back to its original state to prevent data inconsistency.

A database transaction must be A.C.I.D, which is atomic, consistent, isolated, and durable:

• Atomic: the operations against the database must either all occur, or nothing occurs. This helps avoid data inconsistency by a partial update.
• Consistent: to make sure that the transaction does not violate the integrity constraints.
• Isolated: to make sure that the change made by one operation becomes visible to other concurrent operations.
• Durable: to ensure that the committed transactions will survive permanently.

Perl DBI provides a set of APIs that allows you to deal with transactions effectively. To handle transactions in Perl DBI, you do the following steps:

• Set AutoCommit attribute to false to enable the transaction.
• Execute operations in an eval block, at the end of the eval block, call the  commit() method of the database handle object to commit the changes.
• Check the variable  $@ for error and call the  rollback() method of the database to roll back the changes if an error occurred.

Perl MySQL Transaction example

In this example, we will:

• Insert a new link into the links table; get the last insert id of the inserted link.
• Insert a new tag into the tags table; get the last insert id of the inserted tag.
• Associate the inserted link and tag by adding a new row to the  link_tags table with the link id and tag id from the first and second operations.

We will wrap the three operations inside a transaction. The following script illustrates how to handle the transaction using Perl DBI:

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function

use DBI;

say "Perl MySQL Transaction Demo";

# MySQL database configurations
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';

# connect to MySQL database
my %attr = (RaiseError=>1,  # error handling enabled 
	    AutoCommit=>0); # transaction enabled

my $dbh = DBI->connect($dsn,$username,$password, \%attr);

eval{
	# insert a new link
	my $sql = "INSERT INTO links(title,url,target)
		   VALUES(?,?,?)";
	my $sth = $dbh->prepare($sql);
	$sth->execute("Comprehensive Perl Archive Network","http://www.cpan.org/","_blank");
	# get last insert id of the link
	my $link_id = $dbh->{q{mysql_insertid}};

	# insert a new tag
	$sql = "INSERT INTO tags(tag) VALUES(?)";
	$sth = $dbh->prepare($sql);
	$sth->execute('Perl');

	# get last insert id of the tag
	my $tag_id = $dbh->{q{mysql_insertid}};

	# insert a new link and tag relationship
	$sql = "INSERT INTO link_tags(link_id,tag_id)
		VALUES(?,?)";
	$sth = $dbh->prepare($sql);
	$sth->execute($link_id,$tag_id);

	# if everything is OK, commit to the database
	$dbh->commit();
	say "Link and tag have been inserted and associated successfully!";
};

if($@){
	say "Error inserting the link and tag: $@";
	$dbh->rollback();
}

# disconnect from the MySQL database
$dbh->disconnect();Code language: Perl (perl)

Notice that the  $dbh->{q{mysql_insertid}} expression returns the last insert id.

The following is the output of the script:

Perl MySQL Transaction Demo
Link and tag have been inserted and associated successfully!

We can verify the transaction by querying the links , tags and  link_tags tables:

SELECT * FROM links;Code language: SQL (Structured Query Language) (sql)

SELECT * FROM tags;Code language: SQL (Structured Query Language) (sql)

SELECT * FROM link_tags;Code language: SQL (Structured Query Language) (sql)

It works.

In this tutorial, we have shown you how to handle MySQL database transactions in Perl by using the  commit() and  rollback() methods of the database handle object.