Home arrow MySQL arrow Page 9 - Using Transactions In MySQL (Part 2)

Perl of Wisdom - MySQL

mysqlThis concluding segment looks at the MySQL transactional model in a multi-user scenario, illustrating some of the data corruption problems that are likely to arise and explaining how to control them using MySQL's various isolation levels. It also includes a sample Perl application demonstrating transaction usage at the application level, and shows you how to emulate transactions with non-transactional MyISAM tables.

  1. Using Transactions In MySQL (Part 2)
  2. Isolating Yourself
  3. The Three Rís
  4. Peeping Tom
  5. Locks and Keys
  6. Nothing Like the Real Thing
  7. Holding Pattern
  8. Timberrrrrrrrrr!
  9. Perl of Wisdom
  10. End Work
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 41
December 22, 2003

print this article


You should now know a little bit about how transactions work at the SQL command prompt. This is great for learning purposes; however, in the real world, it's unlikely that your application will be interacting with MySQL through a command prompt. With that in mind, let's look at a simple application (written in Perl) that implements a transaction using Perl DBI methods.

# load module
use DBI();
# variables for the transaction
# assume these have come from standard input
$name = "ian";
$pass = "storm";
@groups = ("hr", "admin");
$mhost = "my.pop.server";
$muser = "my.name";
$mpass = "my.pass";
# connect
my $dbh = DBI->connect
"root", "secret", {RaiseError => 1, AutoCommit => 0});
# place the transaction in an eval{} block
# so that errors can be trapped for rollback
# insert user record
$dbh->do("INSERT INTO users (name, pass) 
VALUES ('$name', '$pass')");
# get ID
$id = $dbh->{'mysql_insertid'};
# insert group memberships
foreach $g (@groups)
$dbh->do("INSERT INTO groups 
(uid, grp) VALUES ('$id', '$g')");
# insert mailbox data
$dbh->do("INSERT INTO mailboxes (uid, host, 
mboxname, mboxpass) VALUES ('$id', 
'$mhost', '$muser', '$mpass')");
# got this far means no errors
# commit
# if any errors
# rollback
if ($@)
print "Transaction aborted: $@";
# clean up

The transaction in question here is the same as before - adding a user to the system - only the method differs. The first step is to connect to the database using the connect() method. Note the addition of the AutoCommit parameter to connect() - as explained in the previous segment of this tutorial, this tells MySQL to turn off automatic commits, and turns every query into a transaction that must be explicitly committed in order for it to be saved.

Once a connection has been opened, standard INSERT statements are used to insert the new user's data into the system. These statements are enclosed in an eval{} exception handling block, so that errors, if any, are trapped and escalated upwards to the main program. An error in the eval{} block will be stored in the special $@ variable, and caught by the following "if" block, which rolls the entire transaction back with rollback(). If there are no errors, the transaction will be committed with commit().

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: