Simplify your code with MySQL transactions and prepared queries, and the Perl DBI

This article is also available as a TechRepublic download, which includes the code listings in a more manageable text file format.

The behind-the-scenes logic of many Web applications often involves repeatedly running the same database query with different arguments, or executing a set of related queries as an all-for-nothing block. To support these requirements, most database systems (including MySQL) support prepared queries and transactions, and most scripting languages (like PHP and Perl) come with built-in functions to access these database features. Both these features are, however, new to MySQL and therefore intimidating to developers who haven't come across them before.

That's where this article comes in. The Perl DBI functions supporting transactions and prepared queries in MySQL are explored with a view to explaining how they work and how you can use them in your own applications.

Prepared queries

When performing batch uploads into a database, it's quite common to see SQL queries that differ only in the parameters passed to the INSERT command. A common optimization trick in these situations involves using a so-called prepared query, which reduces database overhead by creating a template for the query and populating it with different values as needed. Intelligently used, this single feature can provide a significant performance boost to your application.

To see how this works, consider a simple two-field table, like the one below in Listing A.

Listing A

Now, let's suppose I need to enter a bunch of new records into this table via an INSERT query. Obviously, the format of the query will remain the same each time I run it; only the values will differ. This can best be accomplished by creating a template for the INSERT query with embedded DBI "placeholders", and populating these placeholders with actual data on each run. Listing B shows you how:

Listing B

There are four simple steps to follow when creating and executing a prepared SQL query with the Perl DBI.

1. Begin by initializing a database handle with a call to the connect() method. This method accepts connection parameters as a string containing the database type ("mysql"), the host name ("localhost") and the database name ("somedb"). The database user name ("user") and password ("pass") is also provided to the connect() method as the second and third argument
2. Create the SQL query template using theprepare() function call. The question marks used in the prepare() function call are placeholders for actual data.
3. Inject actual data values into the query prepared template by calling execute() and passing it arguments corresponding to the placeholders. Note that the sequence of arguments is important in this case, and that there must be one argument for every placeholder defined in the previous step. Every time you call execute() with a different set of arguments, a new INSERT query is executed with the corresponding values.
4. End the session with a call to the disconnect() method.

A typical application for this is a batch INSERT of data sourced from an external file. Here, you would first prepare() the query, then use a loop to read data values from the file and, on each iteration of the loop, call execute() to insert the set of values into the database.

Transactions

Another valuable new feature in MySQL is transactional support. A transaction is simply a block of SQL statements to be executed in all-or-nothing mode, usually because they are inter-dependent on each other. All statements in a transaction must be executed successfully for the transaction to be successful; a failure in any one should cause the system to be "rolled back" to its pre-existing state to avoid data linkage/corruption problems.

A good example of this is a money transfer between two bank accounts. At the database level, such a transfer involves two steps: first, subtracting the transfer sum from balance of the source account and then, adding it to the balance of the target account. If an error occurs in the second step, then the first step must be reversed to a previous "snapshot" to avoid a mismatch. Most databases (including MySQL) accomplish this through a mixture of commands:

• The START TRANSACTION command marks the beginning of a new transaction block. It's usually followed by a series of SQL commands.
• The COMMIT command marks the end of a transaction block and signals that all changes made during the transaction should be "committed" or made permanent.
• The ROLLBACK command marks the end of a transaction block and signals that all changes made during the transaction should be reversed.

To see how this might work in practice, let's consider a table storing an individual user's accounts, like Listing C:

Listing C

Now, let's suppose I need to transfer $400 from one account to another. The actual "transaction" will be performed using two UPDATE statements, one debiting the amount from the source account and the other crediting the target account. Given that all I'm doing is transferring money between accounts, the total available balance across all the accounts ($6000) should remain constant at all times. Here's the DBI code (Listing D) to accomplish this:

Listing D

There are four basic steps to implementing a transaction in Perl:

1.     The first step is always to turn off database "auto-commit", which essentially means that the system saves your changes as you make them. This is important because in a transactional environment, you should only save your changes after you're sure that all the transactional "units" have been successfully completed. In the example above, this step has been performed by setting the AutoCommmit option to 0 in the call to connect().

2.     Next, issue INSERT, UPDATE and/or DELETE queries in the usual way, but enclose these in an eval{} block. Doing this ensures that errors, if any, will cause the program to break out of the block and that the transaction is not committed. If no errors occur, the changes will be committed to the database via the call to commit() after all the queries in the transaction block have been executed.

3.     If an error occurs when executing any one of the statements in the transaction block, the program will break out of the eval{} block and go to the lines of code following it. These lines will first print an error message and then roll the database back to its pre-transaction state via the rollback() function. Note that the transaction cannot be reversed once this function is called.

4.     End the session with a call to the disconnect() method.

As you can see, implementing a transactional model with Perl and MySQL can make your MySQL databases more robust to query execution errors. However, before you go out and start rewriting all your code to use these new features, it's worth noting that they do impose a performance overhead on the system and so performing a cost-benefit analysis of the tradeoff before implementing them is always a good idea. Good luck!