Database transactions are really no mystery. For the uninitiated, the idea is that we want to make changes to the database inside of a transaction that can be committed or thrown away. Why is this a good thing? Without transactions, changes to the database have to be manually undone if an error occurs along the way. In the case of very complex transactions, this may be nearly impossible, and mistakes are often unacceptable.

AutoCommit Off, Fully Transactional

The minimalist approach to creating a database handle via Perl and DBI is

$dbh = DBI->connect($dsn, $user, $password);

By default, this turns on AutoCommit which means all your SQL statements will be committed to the database immediately. In order to use transactions, we're going to have to turn that off. We'll do that by passing an option to the DBI connect() method.

$dbh = DBI->connect($dsn, $user, $password, { AutoCommit => 0 });

With the AutoCommit option turned off, we'll have to commit all our transactions, or they will be automatically rolled back when our program finishes. After doing our SQL work, we can commit to the database with

$dbh->commit();

Or, if we had some error, we might choose to throw away our changes with a rollback:

$dbh->rollback();

AutoCommit On, Single Transaction

There's one more DBI method related to transactions that you should be aware of — begin_work(). This method is used when the AutoCommit option is enabled, but you want to temporarily disable it for a single transaction. The begin_work() method will temporarily set the AutoCommit option to 0 and then renable it once you commit(). This is nice if you have code that works well with AutoCommit but needs an occasional transaction.

So, that's a quick and dirty introduction to Perl, DBI, and transactions. If you have any questions or comments, leave them below.