Web-Scale Transactions

How they differ

When we talk about transactions, we are talking about an abstraction. A transaction is a collection of operations that are treated as a single logical operation. And we are also frequently talking about transactions using the ACID principle:

• Atomicity
  All parts of a transaction must succeed for the transaction to succeed. If any part fails, the transaction fails and all of the operations must be “undone”

• Consistency
  This is referring to the referential integrity of the data. If a transaction changes data in three places and the change in the last place fails, then the first two change need to be undone so the data looks the way it would if the transaction had never occurred.

• Isolation
  This is the guarantee that multiple transactions should not interfere each other. If three transactions are running on the same set of data and the middle one fails, the end result of the data should look just as it would have if only the two successful transactions had been executed

• Durability
  This is the guarantee that, once the transaction is completed successfully or undone successfully, the data is written to some non-volatile store, such as a disk

Quick Example

Let’s take a quick look at a simple example in SQL server. If we create a table that has an identity column for the primary key and some random other columns, we can then do a quick insert into it. Since SQL treats every operation that is not part of a programmatically defined transaction as a transaction, that insert is a transaction and completes. There is a row and it has a value of 1 for the identity column

Rolling Back Leaves Tracks

If, however, you begin a transaction, insert a row, rollback the transaction and then insert another row, you’ll find that there are two rows in the table but the identity column for the second row is 3 not 2. Now, the ACID principle has been adhered to and the data is perfectly valid, but there is no way to tell if the missing identity value of 2 is because an insert failed or because an existing row of data was deleted.

Blocking Issues

While a discrete insert/update/delete of a single row of data is treated as a transaction and won’t often result in blocking other transactions, processing multiple rows at once or having a programmatically defined transaction can lead to blocking. If an update is part of a larger transaction and it takes 1 second to process the entire transaction and your application is processing multiples of this transaction, you may be experiencing performance problems.

Patterns for Performant Transactions in SQL

There are several things to think about when developing a pattern for performant transactions in SQL Server:

• The transaction should not use BEGIN TRANSACTION, it should use the implicit transaction in every data change already in SQL Server

• The number of rows affected at any time should be somewhat limited. While you can update 10,000 rows in a multi-billion row table with one command, there is a chance SQL Server will decide to lock the table until the update is done, which we are trying to avoid. It’s best to break up updates and deletes into smaller chunks. Depending on the table and the data being updated, it can be anywhere from 100 to 1000 rows at a time.

• Determine how ACID the transaction needs to be. If you are moving 100,000 rows from one client to another and something stops the process after 90,000 rows have been moved, is it ok to leave those 90,000 rows where they are and move the 10,000 remaining rows in another operation?

• Log everything so you know what you did, what you didn’t do, and what needs rolled back.

Write one record that contains all of the information needed to complete the operation and use that to handle the highest level of the transaction. If you need to move all of the data from one tenant in a multi-tenant SaaS system to another, because of a merger for example, then have a row that represents that transaction with all of the necessary information. Then have a process that moves data in discrete chunks, logging the changes in a way that can be monitored.

Patterns for Performant Web-Scale Transactions

If you view discrete SQL Server operations as Microservice APIs, there are a lot of parallels to draw between the patterns for handling Web-Scale Transactions. If it does not need to be immediate, have a back end process controlling the transaction at a high level, calling the various APIs to handle all of the ins and outs of the overall transaction. It’s a bit more work to pattern a transaction this way so that it writes to a datastore and then gets processed, but it will make scaling your application much easier.