When I introduce the transaction script pattern in my domain-driven design classes, my students often raise their eyebrows, and some even ask, “Is it worth our time? Aren’t we here for the more advanced patterns and techniques?”

The thing is, the transaction script pattern is a foundation for the more advanced business logic implementation patterns you will learn in the forthcoming chapters. Furthermore, despite its apparent simplicity, it is the easiest pattern to get wrong. A considerable number of production issues I have helped to debug and fix, in one way or another, often boiled down to a misimplementation of the transactional behavior of the system’s business logic.

Let’s take a look at three common, real-life examples of data corruption that results from failing to correctly implement a transaction script.

Lack of transactional behavior

A trivial example of failing to implement transactional behavior is to issue multiple updates without an overarching transaction. Consider the following method that updates a record in the Users table and inserts a record into the VisitsLog table:

01 public class LogVisit

02 {

03 ...

04

05 public void Execute(Guid userId, DataTime visitedOn) 06 {

07 _db.Execute("UPDATE Users SET last_visit=@p1 WHERE user_id=@p2", 08 visitedOn, userId);

09 _db.Execute(@"INSERT INTO VisitsLog(user_id, visit_date)

10 VALUES(@p1, @p2)", userId, visitedOn);

11 }

12 }

If any issue occurs after the record in the Users table was updated (line 7) but before appending the log record on line 9 succeeds, the system will end up in an inconsistent state. The Users table will be updated but no corresponding record will be written to the VisitsLog table. The issue can be due to anything from a network outage to a data‐ base timeout or deadlock, or even a crash of the server executing the process.

This can be fixed by introducing a proper transaction encompassing both data changes:

public class LogVisit

{

...

public void Execute(Guid userId, DataTime visitedOn)

{

try

{

_db.StartTransaction();

_db.Execute(@"UPDATE Users SET last_visit=@p1 WHERE user_id=@p2",

visitedOn, userId);

_db.Execute(@"INSERT INTO VisitsLog(user_id, visit_date) VALUES(@p1, @p2)",

userId, visitedOn);

_db.Commit();

} catch {

_db.Rollback();

throw;

}

}

}

The fix is easy to implement due to relational databases’ native support of transac‐ tions spanning multiple records. Things get more complicated when you have to issue multiple updates in a database that doesn’t support multirecord transactions, or when you are working with multiple storage mechanisms that are impossible to unite in a distributed transaction. Let’s see an example of the latter case.

Distributed transactions

In modern distributed systems, it’s a common practice to make changes to the data in a database and then notify other components of the system about the changes by publishing messages into a message bus. Consider that in the previous example, instead of logging a visit in a table, we have to publish it to a message bus:

01 public class LogVisit

02 {

03 ...

04

05 public void Execute(Guid userId, DataTime visitedOn) 06 {

07 _db.Execute("UPDATE Users SET last_visit=@p1 WHERE user_id=@p2", 08 visitedOn,userId);

09 _messageBus.Publish("VISITS_TOPIC",

10 new { UserId = userId, VisitDate = visitedOn });

11 }

12 }

As in the previous example, any failure occurring after line 7 but before line 9 suc‐ ceeds will corrupt the system’s state. The Users table will be updated but the other components won’t be notified as publishing to the message bus has failed.

Unfortunately, fixing the issue is not as easy as in the previous example. Distributed transactions spanning multiple storage mechanisms are complex, hard to scale, error prone, and therefore are usually avoided. In Chapter 8, you will learn how to use the CQRS architectural pattern to populate multiple storage mechanisms. In addition, Chapter 9 will introduce the outbox pattern, which enables reliable publishing of messages after committing changes to another database.

Let’s see a more intricate example of improper implementation of transactional behavior.

Implicit distributed transactions

Consider the following deceptively simple method:

public class LogVisit

{

...

public void Execute(Guid userId)

{

_db.Execute("UPDATE Users SET visits=visits+1 WHERE user_id=@p1", userId);

}

}

Instead of tracking the last visit date as in the previous examples, this method main‐ tains a counter of visits for each user. Calling the method increases the corresponding counter’s value by 1. All the method does is update one value, in one table, residing in one database. Yet this is still a distributed transaction that can potentially lead to inconsistent state.

This example constitutes a distributed transaction because it communicates informa‐ tion to the databases and the external process that called the method, as demon‐ strated in Figure 5-2.

Figure 5-2. The LogVisit operation updating the data and notifying the caller of the operation’s success or failure

Although the execute method is of type void, that is, it doesn’t return any data, it still communicates whether the operation has succeeded or failed: if it failed, the caller will get an exception. What if the method succeeds, but the communication of the result to the caller fails? For example:

• If LogVisit is part of a REST service and there is a network outage; or

• If both LogVisit and the caller are running in the same process, but the process fails before the caller gets to track successful execution of the LogVisit action?

In both cases, the consumer will assume failure and try calling LogVisit again. Exe‐ cuting the LogVisit logic again will result in an incorrect increase of the counter’s value. Overall, it will be increased by 2 instead of 1. As in the previous two examples, the code fails to implement the transaction script pattern correctly, and inadvertently leads to corrupting the system’s state.

As in the previous example, there is no simple fix for this issue. It all depends on the business domain and its needs. In this specific example, one way to ensure transac‐ tional behavior is to make the operation idempotent: that is, leading to the same result even if the operation repeated multiple times.

For example, we can ask the consumer to pass the value of the counter. To supply the counter’s value, the caller will have to read the current value first, increase it locally, and then provide the updated value as a parameter. Even if the operation will be exe‐ cuted multiple times, it won’t change the end result:

public class LogVisit

{

...

public void Execute(Guid userId, long visits)

{

_db.Execute("UPDATE Users SET visits = @p1 WHERE user_id=@p2", visits, userId);

}

}

Another way to address such an issue is to use optimistic concurrency control: prior to calling the LogVisit operation, the caller has read the counter’s current value and passed it to LogVisit as a parameter. LogVisit will update the counter’s value only if it equals the one initially read by the caller:

public class LogVisit

{

...

public void Execute(Guid userId, long expectedVisits)

{

_db.Execute(@"UPDATE Users SET visits=visits+1

WHERE user_id=@p1 and visits = @p2", userId, visits);

}

}

Subsequent executions of LogVisit with the same input parameters won’t change the data, as the WHERE...visits = @prm2 condition won’t be fulfilled.