A more precise formulation of the question comes from an understanding of serializability.

First, the term serial refers to one-at-a-time execution. A single-user system is a way to enforce serial execution. A publish/subscribe or event queuing architecture is another way to enforce serial execution (see Chapter 10): each requested operation is queued up and then processed one-by-one by a single subscriber.

The trouble with serial execution is that it is slow to wait for one-at-a-time execution.

The term serializability is a generalization of serial execution. A multiuser-system can achieve higher throughput by carefully interleaving processing of requests while ensuring that the overall result of any calculation is equivalent to some serial execution.

Suppose we have a bank that performs the following steps during a transfer of money from x to y:

Now consider two bank transfers. Suppose we want to transfer $1 from Alice (a) to Bobby (b), and $1 from Alice (a) to Carol (c). Assume that each starts at $100.

Here is one serial execution:

As expected, the result is that Alice (a) has $98, while Bobby (b) and Carol (c) have $101 each.

Another serial execution might perform the operations in a separate order:

As expected, even though the execution reverses the order of the two requests, the result is the same: Alice (a) has $98, while Bobby (b) and Carol (c) have $101 each.

Now, suppose the serial execution is no longer required, and one operation from each request overlaps:

The result is the same. Assuming that the system can simultaneously read from a and write to c in timestep 6, the overlap has potentially saved one timestep.

How much could these requests overlap without changing the result? Here is another execution trace that saves even more time:

While this trace is not serial, it is serializable because the result is equivalent to a serial execution.

Here is another serializable execution trace:

And here is another serializable execution trace:

As we saw in the previous section, some traces are not serializable:

In this non-serializable trace, $1 has gone missing: Alice (a) has $99, while Bobby (b) and Carol (c) have $101 each.

It is easy to ensure serial execution: only do one thing at a time. But how can serializability be enforced?

One technology provided by database systems is the transaction. A transaction is a unit of work, consisting of potentially many individual operations. The database ensures that the transaction executes as though it runs in isolation.

More formally, a transaction is a unit of work to which the database guarantees atomicity, consistency, isolation and durability (ACID):

Transactions are an important part of the standards implemented by SQL databases. In most database management systems, a transaction is started by executing START TRANSACTION. ^[3] After initiating the transaction, the database management system will monitor all queries and updates (SELECT, INSERT INTO, UPDATE and DELETE statements) to ensure that the ACID criteria hold. At any point, ROLLBACK will abort the transaction and all of its changes. To finish the transaction, executing COMMIT will cause the database to perform any final consistency checks and logging to ensure all of the ACID criteria. Should the commit fail (e.g., because the database cannot perform operations required to guarantee ACID), then the transaction will be reversed and the database will return an error.

In simple terms:

The state diagram below depicts the flow:

In working code, all of the transaction operations must execute on the same database connection. On Node.js with the pg package and the PostgreSQL database, you should request a client from the connection pool, rather than performing the queries directly on the pool. ^[4]

For example, the following code that does not use a transaction:

To obtain all the benefits of transactions, only slightly more work is required:

In practice, the ACID guarantees do incur a performance cost. Thus, it is important to minimize the use of transactions and limit their scope as much as possible. A transaction should not last more than a single request and should take as short a time as possible. Do not think of ROLLBACK as a way to add user-friendly ‘undo’ functionality to an application. Instead, think of it as a way of aborting mid-way through a change. If you would like to add undo capabilities to an application, it is better to treat undo as a separate reversing transaction (e.g., use a new transaction to subtract $1 from Bobby and return it to Alice).

Many NoSQL databases, such as MongoDB, do not provide or encourage the use of general-purpose transactions. MongoDB provides ACID guarantees at the level of individual requests. However, by default, MongoDB does not allow transactions to span multiple updates or multiple tables. ^[5]

Database management systems use a range of internal implementation strategies to guarantee ACID properties in a database.

In computer science, a lock is a mechanism that restricts concurrent access to a resource. For example, when you are editing a document on your computer, the operating system will lock the file so that another program cannot simultaneously open it.

A single lock — one lock across the entire database — can enforce serial execution. Instead of using transactions, one exclusive lock could be acquired at the start of each request, and then released at the end of the request. Other concurrent requests must wait until the lock is released so that only one transaction will be making changes at a time. ^[6]

Production database systems use more fine-grained locks for higher performance. Instead of having a single lock for the entire database, a database can have locks for each table or even individual rows. For example, there might be separate locks for the balance of Alice’s account, Bobby’s account and Carol’s account. Reading Alice’s balance within a transaction will automatically cause the database to establish a lock on the balance of her account until the end of the transaction. A transaction transferring money from Marcia to Greg does not need to lock the balance of Alice, Bobby or Carol or Sam’s accounts. Because they don’t share the same locks, they run simultaneously without interfering with each other.

Production database systems can also use more flexible locks. A common technique is a read/write lock that allows multiple simultaneous readers or just one writer. A read/write lock provides higher performance if most operations involve reading lots of data or shared data, but where writes are comparatively rare.

Take a numbered serial execution of 5 transactions:

Now, suppose that, along with the bank accounts, the database stores the identifier of the last transaction that read or wrote the balance. In the table below, I depict the ‘last involved transaction’ in brackets.

The numbers of the ‘last involved transaction’ only increase. Alice’s bank balance isn’t involved in every single transaction so there is no #3. However, the ‘last involved transaction’ never decreases: Alice’s balance follows the sequence #1, #2, #2, #4, #5.

This rule — that transactions identifiers only increase — is the principle of versioning and timestamping.

A database management system can allow multiple concurrent transactions. If the ‘last involved transaction’ (the timestamp) never decreases for any value in the database, then the operations will be serializable. If the database management system detects that a change would cause the timestamp to decrease, it will abort the transaction and expect the user (or the domain logic) to try again later.

As with locking, the performance of versioning can be also be increased by using separate read and write timestamps. Multiple reads of the same value can be performed out-of-order, provided that the writes are consistent with the read timestamps.

Snapshots provide additional flexibility to the database management system to assist with ensuring serializability. The database can keep track of multiple versions of the data. For example, suppose we are transferring Alice’s money to Bobby while calculating the total balance in all accounts:

At the start, the total was $200 (i.e., $100 + $100). At the end, the total was the same amount of $200 (i.e., $99 + $101). However, the total is the inconsistent result of $199 ($99 + $100): the transaction will believe that $1 is missing from the bank.

This problem could be solved using locks:

The database lock will postpone the ‘Compute total’ until the Alice to Bobby transfer is complete.

The problem could also be solved using timestamps:

In step 6, the ‘Alice to Bobby’ transaction (#1) aborts because writing b' in step 6 would result in the timestamp decreasing from #2 to #1. The ‘Compute total’ transaction (#2) also needs to be aborted because its result depends on values from the, now aborted, Transaction #1.

Snapshots offer a better possibility. The database can store old and new versions of the same data:

The ‘Compute total’ transaction starts after changes have already been made to the database by the ‘Alice to Bobby’ transaction. However, the ‘Compute total’ transaction reads the old snapshots as if it were executing before ‘Alice to Bobby’ started. While this may seem slightly counterintuitive, note that serializability only means that the operations are equivalent to some serial execution. Serializability does not mean that the latest values are always read. In practice, your users will not notice. If two transactions are running simultaneously, users are unlikely to notice that the results are a few milliseconds out of date. However, the benefit is dramatic: snapshots make it possible to maintain serializability without any need to delay or abort either transaction.

Locking, versioning and snapshots are usually internal details of the transaction processing code of a database management system.

However, the same techniques can be useful when writing code for presentation or domain logic.

There are many situations where two or more people can edit the same data:

There is a danger that two users simultaneously see and update the same values. As a simple example, imagine that you do an hour of work for each of two different managers at a large restaurant. By chance, the managers both happen to log in to the system at 5:35 pm on Saturday to add the extra hours to your existing count of 15 hours:

As a result, instead of having 16 hours of pay owing to you, you end up with one hour missing.

Instead, it would be better to lock the record:

Or, to detect the invalid update using versions:

The domain logic can enforce consistency by simulating transactions:

The latter approach (versions) is widespread in object-relational mapping and object-document mapping frameworks. Both Sequelize (through a version attribute) and Mongoose (through the version key) provide built-in optimistic concurrency control, in addition to the lower-level optimistic concurrency features already performed by the database management system.

ACID is an ideal for transactions, but there is a significant performance penalty involved:

However, these guarantees may not matter. What does your application really need?

For a business’s most critical data, the answer is probably a resounding “Yes! It does matter if we lose data!”. However, for non-critical data, users may prefer higher performance and overlook the occasional glitch (e.g., in online games, in social media ‘like’ counts, in informal messaging).

The SQL standards and most SQL databases offer reduced forms of isolation that do not guarantee full ACID or full serializability. Some phenomena that can occur at lower isolation levels include:

The default configuration of most commercial databases is a reduced isolation level. For example, in PostgreSQL, non-repeatable reads and phantom reads can occur unless you execute SET TRANSACTION ISOLATION LEVEL SERIALIZABLE command after starting each transaction.

MongoDB and other NoSQL databases also provide lower levels of isolation than full serializability.