PurePerl MySQL Transaction

Using the PurePerl MySQL API– Part 10

Foreword: In this part of the series, I explain how to code a transaction in MySQL.

By: Chrysanthus Date Published: 31 Aug 2016

Introduction

This is part 10 of my series, Using the PurePerl MySQL API. In this part of the series, I explain how to code a transaction in MySQL. More than one client can connect to the MySQL server at one time (duration). It is possible for two or more users to attempt to edit the same tables (rows) at a particular time. To prevent conflict and inconsistent result, the tables of interest have to be locked from the rest of the clients, so that the very first client uses the tables. When he finishes, the tables are unlocked. That is transaction. The tables will be locked again for the next user, and released after his transaction.

START TRANSACTION and COMMIT
In the following transaction code, a value is read from the pet table and placed in another row of the pet table.

    START TRANSACTION;
    SELECT @species := species FROM pet WHERE owner='John';
    UPDATE pet SET species=@species WHERE owner='Diane';
    COMMIT;

The transaction begins with the statement “START TRANSACTION;” and ends with the statement “COMMIT;”. In between, the SQL statement results are saved temporarily and not permanently. They are saved to disk permanently with the “COMMIT;” statement.

Statements That Cause an Implicit Commit
There are some statements that are always committed whether or not they are in a transaction. Typical of these are the Data Definition Language (DDL) statements that define or modify database objects. These are: ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE, TRUNCATE TABLE.

The above transaction can be coded as follows (after selecting the database):

    my $transSt = "START TRANSACTION;
                 SELECT \@species := species FROM pet WHERE owner='John';
                 UPDATE pet SET species=\@species WHERE owner='Diane';
                 COMMIT;";

    if (Mysql::query($transSt) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            for (my $i=0; $i<@Mysql::Result; ++$i)
                {
                 for (my $j=0; $j<@{$Mysql::Result[$i]}; ++$j)
                     {
                         print %{${$Mysql::Result[$i]}[$j]}, "\n";
                     }
                }
        }

Thats is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

PurePerl MySQL Transaction

Using the PurePerl MySQL API– Part 10

Introduction

Comments