More on Transactions

Why Are Transactions Needed?

An interesting question is, are transactions really needed? Transactions can operate on single records or objects in a database or on multiple of them. Consider you are a day trader on Etrade and decide to sell 100 shares of Google. The database behind the scenes should minus 100 shares from your overall holding of Google stock and at the same time credit the proceeds from the sale into your account. Usually trades take 3 days to settle but we’ll ignore that fact for this example. Now the Etrade backend has to keep two pieces of data in sync, the portfolio holdings and the money in your account. If these pieces of information are stored in different tables in case of relational databases or different documents in case of document databases then updating both at the same time can be achieved using a transaction. Isolation and atomicity attributes of an ACID-compliant database ensure that the online account of the trader reflects changes to both the pieces of information and not just partially to one and not the other. There are other scenarios where transactions prove useful, for instance:

1. Relational databases have the concept of foreign keys. A row in one table may have a foreign key reference in another table. When updating a row with a foreign key reference we must make sure that the change doesn’t make the reference invalid. The change in one table may also trigger an update in the other table. These multiple changes can be grouped together in one transaction.

2. Similar to relational databases, document databases with denormalized data models may require updates to several documents caused by a single change to one document in order to keep the denormalized data in sync.

3. Majority of datastores offer secondary indexes that need to be updated alongside changes to records that are indexed.

Single record changes

Atomicity and isolation can also be helpful when operating on a single record or document. Consider the situations where we are storing a binary object in a database of size 100KB. The operation can fail midway after only 50KB have been written. The half-written value is corrupt and incomplete and should be rolled-back. Atomicity prevents a situation like this. Even if there isn’t failure, another client can attempt to read the record while the value is being written to. The read would result in a garbage value which is incomplete. Isolation can prevent such reads. For operations on single records/documents on a single node almost all databases implement atomicity and isolation.