An Introduction to Transactional Databases

A transactional database is a database that supports ACID transactions. Transactional databases power a variety of applications—everything from the silliest of applications to critical financial services and healthcare applications.

Transactions are a vital database feature. Based on the way the data is modeled in the database, single-record transactions may be sufficient or multi-record transactions may be required. For example, applications that use relational databases will frequently rely on multi-record transactions, because related data is commonly split across multiple tables.

Applications that use document databases like MongoDB rarely need multi-document transactions, because related data can be stored together in a single document. We estimate that 80%-90% of applications that leverage the document model will not need to use multi-document transactions. However, to provide developers with maximum flexibility, MongoDB does support multi-document transactions for the applications that need them.

In this article, we'll discuss the answers to the following questions:

What is a transactional database?

A transactional database is a database that supports ACID (atomicity, consistency, isolation, and durability) transactions. A transaction is a set of database read and write operations where either all or none of the operations succeed.

What are the database requirements for transactions?

Databases with transactions must satisfy the ACID properties:

  • Atomicity: All database operations in the transaction must succeed or none of the operations should be committed.
  • Consistency: All database operations must satisfy the database rules and leave the database in a valid state.
  • Isolation: Transactions that are run concurrently must leave the database in the same state they would have if the transactions were run sequentially.
  • Durability: Once the database has confirmed the transaction has been committed, the database must retain the changes even in the event of an unexpected error.

What are transactional databases good for?

Transactional databases excel at storing and querying the data required to power an application. They ensure data integrity and consistency.

Most relational databases support multi-record transactions.

With proper data modeling in non-relational databases, multi-record transactions are not always essential. In MongoDB, related data can be modeled in a single, rich, and natural data structure by using a variety of types, including subdocuments and arrays. Data does not need to be spread across separate tables composed of flat rows and columns.

MongoDB has always provided transactional guarantees on single-document operations.

  • Atomicity: Single document operations have always been atomic in MongoDB. These operations can write to one or more fields, including subdocuments, elements in an array, and even nested arrays.
  • Consistency: Single document operations leave the database in a consistent state. When MongoDB added support for schema validation and triggers, write operations observed these rules and ensured the data remained in a consistent state.
  • Isolation: MongoDB guarantees isolation when a document is updated. Any errors cause the operation to roll back so that the client receives a consistent view of the document.
  • Durability: MongoDB provides tunable write concerns that ensure writes are not lost even in the event of an unexpected failure.

With this design, application owners get the same data integrity guarantees as those provided by relational databases. As we noted earlier, MongoDB supports multi-document ACID transactions for the use cases that require them.

Let's consider an example database for a library. The library allows each person to check out a maximum of 10 books at a time. The database stores the number of books each person has checked out as well as a record of each book the user has checked out. Ensuring that the number of books the person has checked out remains consistent with the records of the books the person has checked out is vital. In a document database like MongoDB, all of this information could be stored in a single document, eliminating the need for a multi-document transaction.

{
  "_id": 123456789,
  "firstName": "Bert",
  "lastName": "Maklyn",
  "numberOfBooksCheckedOut": 1,
  "booksCheckedOut": [
     {
        "bookId": "fbi101",
        "dateCheckedOut": 2022-02-02T05: 00: 00.000+00: 00,
        "dateDue": 2022-02-02T05: 00: 00.000+00: 00
     }
  ]
}

Database transaction example

Let's take a look at an example of a multi-document ACID transaction. Consider an online book store that tracks current inventory as well as customer orders. This information might be stored in two collections (or tables): an inventory collection and an orders collection.

Let's take a look at each of the ACID properties in this example.

  • Atomicity: Whenever an order is placed, the inventory collection needs to be updated to accurately reflect the number of copies remaining in stock. If an error occurs while updating the inventory or recording the order, the entire transaction should be aborted. Updating the inventory without recording the order or vice versa would leave the data in an inconsistent state.
  • Consistency: A book's inventory should never drop below zero as this would indicate a book had been sold that was out of stock. A database constraint could be applied to prevent a book's inventory from going below zero. If an order would cause a book's inventory to drop below zero, the transaction should be aborted.
  • Isolation: Consider the case where two users place orders at the same time. The final result of running the transactions simultaneously should be the same as if they were run sequentially.
  • Durability: Consider an unexpected error like a database server crashing immediately after the database has confirmed that the transaction has been committed. The database should still have the updated inventory and order details from the committed transaction despite the unexpected server crash.

 A flow diagram shows the inner workings of three big pieces:  User1 purchasing a copy of "parks-rec-book", an Internet outage immediately after a transaction has been committed or aborted, and User2 purchasing a copy of "parks-rec-book." The diagram shows the flow of events inside transactions as two users purchase copies of a book.

Database transactions with MongoDB

Single document operations have always been atomic in MongoDB. MongoDB added support for multi-document ACID transactions in version 4.0, and MongoDB expanded that support to include distributed transactions in version 4.2.

You can implement a transaction in a variety of programming languages using any of MongoDB's official drivers. See the MongoDB Drivers documentation for more information on how to implement transactions in a specific programming language.

Transaction example code

Let's continue with the online book store example from the previous section. Below is a code snippet that updates the inventory and records the order when a user purchases a book. The database operations happen inside of a transaction.

await session.withTransaction(async () => {
           // Update the inventory to reflect the book has been sold
           const updateInventoryResults = await inventoryCollection.updateOne(
               { _id: bookId },
               { $inc: { numberInStock: quantity * -1 } },
               { session });
           if (updateInventoryResults.modifiedCount !== 1) {
               await session.abortTransaction();
               return;
           }
 
           // Record the order in the orders collection
           const insertOrderResults = await ordersCollection.insertOne(
               { "userId": userId , bookId: bookId, quantity: quantity, status: status },
               { session });
       }, transactionOptions);

Code snippet that shows how to implement a MongoDB transaction in Node.js.

The first line of the code snippet calls session.withTransaction(). withTransaction() starts the transaction, executes the callback function, and commits or aborts the transactions appropriately.

The callback function includes the database operations that are part of the transaction. In this case, the transaction includes operations to update the book's inventory and record the order in the orders collection.

To view the full Node.js script and try the code yourself, visit the Quick Start: Node.js and MongoDB GitHub Repo.

Summary

Databases like MongoDB that support ACID transactions are known as transactional databases. Transactions allow developers to group database operations together in a way that they all succeed or all fail together.

Get started with transactions by creating a free database deployment in MongoDB Atlas and then implementing a transaction using your favorite programming language.

FAQ

What is the difference between a transactional and relational database?

A transactional database is a database that supports ACID (atomicity, consistency, isolation, and durability) transactions. Any database—relational or non-relational—can be a transactional database.