By Joe Purcell
Expert Author
Article Date: 2011-08-19
Databases are becoming more complex and as this evolution is taking place there is much discussion about SQL and noSQL, ACID and BASE, and almost everything else related to databases. Central to this debate and commonly misunderstood are transactions.
A transaction is an atomic unit of SQL statements to be run together, and if one statement fails the entire transaction fails. This is called atomicity and is the first part of ACID. This can be handled in various ways, but to conceptualize a transaction let's look at an example from he SQL Team:
BEGIN TRAN
UPDATE authors
SET au_fname = 'John'
WHERE au_id = '172-32-1176'
UPDATE authors
SET au_fname = 'Marg'
WHERE au_id = '213-46-8915'
COMMIT TRAN
If the update statement for John fails, then Marg's update statement will not be run. Transactions themselves don't introduce any significant logic issues, and in fact, they solve a lot of issues. However, let's look at an example where complications arise. If you are running a commerce site and John buys a book at the same time Marg buys that exact same book the transaction that was committed last will win. Such inconsistencies get resolved by table locking.
As is clear in the example just mentioned, discussing transactions compels the discussion about locking. Locking involves preventing data from being read or written while a transaction is in progress. However, that is a completely other topic. These questions about how transactions are handled is at the core popular debates on databases.
Some database engines, such as MyISAM, do not support transactions. For MySQL only InnoDB and BerkeleyDB tables support ACID-compliant transactions by default. The problem with not supporting transactions is that it makes maintaining the other ACID properties much more difficult, even impossible. Both Webmonkey and the Dev Shed have great articles on the role that transactions play in relational databases and how they tie into the other ACID properties.
