1. Computing
Transactions
 Related Resources
• SQL Fundamentals
• Starting a Career in Databases
• Certification Resources

If you've been around databases for any length of time, there's no doubt that you've heard of transactions.  Are you familiar with what they accomplish and how to implement them?  Here's a brief refresher course.

Transactions allow you to group SQL commands into atomic elements.  Essentially, they provide you with the capability of performing a series of commands in an "all or nothing" fashion.  The syntax for a simple transaction in SQL is rather basic:

BEGIN TRANSACTION
<SQL statements>
COMMIT

Using the syntax above, all of the statements between the BEGIN TRANSACTION and COMMIT statements will be queued for execution but will not actually execute until the COMMIT statement writes them all to the database.

If you've ever issued a database command using SQL Server Query Analyzer, mySQL's command line interface or any similar tool, you've actually already worked with transactions!  Modern relational databases implicitly bracket SQL commands with the BEGIN TRANSACTION...COMMIT syntax prior to execution.  That begs another question -- if the database implicitly assumes these statements are present, why do you need them at all?  Imagine a scenario where a bank is processing a transfer of $100 from Richard's account to Renee's account.  You might write this as a series of two SQL statements:

UPDATE Accounts
SET Balance = Balance - 100
WHERE Owner = 'Richard'

UPDATE Accounts
SET Balance = Balance + 100
WHERE Owner = 'Renee'

The database would then process these queries as two separate transactions.  Now suppose the program processing the transfers crashed at the moment the first transaction completed, right before the second transaction began.  In this case, Richard would be out $100 and Renee would never see the money!  Obviously, this is not a good situation.  Therefore, we use an explicit transaction to group these two statements into an atomic unit:

BEGIN TRANSACTION transfer

UPDATE Accounts
SET Balance = Balance - 100
WHERE Owner = 'Richard'

UPDATE Accounts
SET Balance = Balance + 100
WHERE Owner = 'Renee'

COMMIT

When the transaction is written in this manner, it's an all or nothing endeavor.  The database will process the debit from Richard's account and the credit to Renee's account at the same time.  If one part of the transaction fails, the entire transaction will be cancelled.  That's the power of transactions!

Discuss in my forum

©2014 About.com. All rights reserved.