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.