Perl Tutorial: MySQL Perl Transaction

By /
Perl Tutorial MySQL Perl Transaction

In SQL, transactions are the collection of database queries clubbed together as one single unit. This means that all the queries in a transaction will either all process or none will.

There are four standard properties of a transaction, they are popularly known as ACID properties:

  1. Atomicity: Atomicity ensures that all the queries within the transaction are completed successfully, if in case even one of the queries fails the operations are rolled back to the initial state.
  2. Consistency: Consistency ensures that once the transaction is successful the database has properly charged its state.
  3. Isolation: Isolation ensures that transactions are independent of each other and transparent to each other.
  4. Durability: Durability ensures that the committed transactions persist the effects on databases in case of system failure.

The transaction has two operations, Commit, and rollback. Each and every transaction either commits (means that the transaction pertains) or rollback (means that there might be inconsistency, and the transaction rolls back to the initial state, no changes occur).

How to use Perl for MySQL Transaction

We understood the concept of transaction in the introduction section, now let’s understand how the transaction works, and how we can create transactions using Perl programming language.

Let’s take an example, we have an ATM machine and we need to withdraw money from it, the process will be that we’ll first insert the card, then we’ll write the amount we want to withdraw, after that we’ll enter the pin, now the amount will be subtracted from both the bank account and the ATM machine, and at the end, I’ll receive the money. This whole process is called transaction as it involves several operations, these operations must all be in compliance with ACID property, if not the transaction will not be processed.

Now that we have understood transactions, let’s see how we can create a MySQL transaction using Perl. Perl DBI (Database Interface) provides an API that caters to the efficient performance of the transaction process.

The syntax for creating a transaction query using Perl:

$dbh->{AutoCommit} = 0; # enable transactions to occur 
$dbh->{RaiseError} = 1; # if error occurs roll back 
eval { 
  # write queries here 
  $dbh->commit( );
 };
 
if ($@) { 
  warn "Transaction aborted: $@"; 
  eval { 
    $dbh->rollback( ) }; # if in case, rollback fails 
    # application clean-up is done here
 } Code language: SQL (Structured Query Language) (sql)

An Example

In this section, we’ll see a simple example of how to create a transaction using the Perl programming language. Let’s take an example of a student table, that has the following columns- roll number, first name, last name, standard, section, and contact. We’ll create a simple transaction where we’ll insert two records, one for roll number 24 and the other for roll number 21.

use strict;
use DBI;
use warnings;

use v5.10;

say "Perl MySQL Trasaction Example";



#to create connection
my $db = "DBI:mySQL:db";
my $username = "root";
my $password = "pass@123";

#for rollback or commit 
my %attr = (RaiseError => 1, 
            AutoCommit => 0);


#creating connection with the database
my $dbh = DBI->connect($db,$username, $password, \%attr);

#creating transaction
eval{
    #first query
    $sql = "INSERT INTO student(rollno, fname, lname, standard, section, contact) VALUES(?, ?, ?, ?, ?, ?);
    $sth = $db->prepare($sql);
    $sth->execute(24, "Masha", "Bear", 4, 'D', 2323232323);

    #second query
    $sql = "INSERT INTO student(rollno, fname, lname, standard, section, contact) VALUES(?, ?, ?, ?, ?, ?);
    $sth = $db->prepare($sql);
    $sth->execute(21, "Princy", "Meash", 4, 'A', 1111122222);

    $dbh->commit();
say "Students have been inserted successfully";
};

#incase any of the query doesn't work
if($@){
    say "Error in insertion on student table: $@";
    $dbh->rollback();
}

#close connection with database
$dbh->disconnect();Code language: SQL (Structured Query Language) (sql)

Now, that we know how to write the code, let’s quickly understand how it will work.

Case 1: Both the queries were successfully executed. In that case, the if condition will not work and all the changes will commit successfully.

Case 2: One of the queries failed. In case of failure, let’s assume that roll no 21 already exists in the table and as roll number is a primary key this will turn out to be unsuccessful execution. In this case, both the insertion will not occur and there will be a rollback of this transaction. This means the database will not be affected.

Conclusion

In this tutorial, we learned about SQL transactions, ACID properties, and the need for creating transactions. We also learned the syntax for creating transactions using the Perl programming language and saw the implementation.

See Also: The official documentation for MySQL transactions and Perl MySQL transactions for more information.