Exploring MongoDB 4.0 Multi-Document ACID Transactions vs MySQL

Introduction

MongoDB 4.0 has been released as a GA version, bringing many new features, the most notable being support for multi‑document ACID transactions. This article focuses on the core transaction capabilities—isolation levels, repeatable reads, phantom reads, and conflict detection—and compares them side‑by‑side with MySQL.

ACID Basics

Atomicity: All operations in a transaction succeed or all fail; there is no partial success.

Consistency: The database must be in a consistent state before and after a transaction.

Isolation: Locks ensure that concurrent transactions do not interfere; only the transaction holding the lock can modify the data.

Durability: Once a transaction commits, its changes survive crashes.

Limitations and Preconditions

MongoDB transactions have several constraints:

They are only available on replica set deployments (single‑node setups must be switched to replica‑set mode).

Only the WiredTiger storage engine is supported.

Sharded clusters do not support transactions until version 4.2.

Transactions are limited to CRUD operations; DDL/DCL statements are not allowed.

System databases ( config, admin, local) cannot be read or written within a transaction.

Writes to system.* collections are prohibited.

The total size of a transaction’s write set cannot exceed 16 MB; large transactions should be split into smaller batches.

Creating the First Transaction

Before starting a transaction, a session must be created; transactions cannot run outside a session.

var session1 = db.getMongo().startSession();

var session2 = db.getMongo().startSession();

Mongo Shell now provides three new commands for transaction control: session.startTransaction() – begins a transaction in the current session. session.commitTransaction() – permanently applies the changes made in the transaction. session.abortTransaction() – discards all changes made in the transaction.

Transaction Lifetime Settings

Idle transactions are governed by the transactionLifetimeLimitSeconds parameter, which defaults to 60 seconds. You can view the current setting with:

db.adminCommand({ getParameter: 1, transactionLifetimeLimitSeconds: 1 })

To change it at runtime:

db.adminCommand({ setParameter: 1, transactionLifetimeLimitSeconds: 30 })

Or make it permanent by adding to /etc/mongod.conf:

setParameter = transactionLifetimeLimitSeconds=30

Long‑running idle transactions hold connections and memory, potentially exhausting resources; adjusting the timeout helps mitigate this.

Isolation Demonstrations

Demo 1 – Transaction Conflict Detection

When two or more concurrent transactions modify the same document, MongoDB detects the conflict immediately, even before commit. Unlike MySQL, which uses innodb_lock_wait_timeout to auto‑rollback after a timeout, MongoDB provides no such parameter.

Lock‑wait timeout can be tuned via:

db.adminCommand({ setParameter: 1, maxTransactionLockRequestTimeoutMillis: 15 })

Or permanently in /etc/mongod.conf:

setParameter = maxTransactionLockRequestTimeoutMillis=15

Demo 2 – Repeatable Read

Repeatable Read prevents dirty reads and non‑repeatable reads. In this level, a transaction sees a consistent snapshot of data for the duration of the transaction, even if other transactions modify the data.

Demo 3 – Phantom Read

Under MySQL’s default Repeatable Read, a second session may see rows that were inserted by a concurrent transaction that has not yet committed, resulting in a phantom read. MongoDB’s default isolation is Snapshot, which lies between Repeatable Read and Serializable, eliminating dirty reads, non‑repeatable reads, and phantom reads while preserving concurrency.

Conclusion

MongoDB 4.0’s transaction model uses Snapshot isolation by default (especially when readConcern=majority is set), offering stronger guarantees than MySQL’s Repeatable Read without the performance penalty of full serializable isolation. However, developers must be aware of the replica‑set requirement, WiredTiger engine limitation, transaction size cap, and the inability to run transactions on sharded clusters until version 4.2.

References

https://docs.mongodb.com/manual/core/transactions/

https://www.percona.com/blog/2018/12/04/mongodb-4-0-using-acid-multi-document-transactions/