How To SELECT ... FOR UPDATE inside MongoDB Transactions

The most common question that I receive as a MongoDB Consultant since we introduced Multi-document ACID transactions in MongoDB 4.0 is: "How can I be sure that the documents that I am reading inside a transaction are not modified by another operation before the commit?"

Before answering this question, I would first like to explain how the locking system works inside a MongoDB transaction. Knowing this helps understand the answer.

Write conflicts, Locks and Transactions

When we perform write operations inside a transaction, the database needs to acquire an exclusive lock on the document that we are going to modify. If it's not able to acquire it in the time defined by maxTransactionLockRequestTimeoutMillis (default 5ms), it will raise a writeConflict error.

Let's consider the following examples:

Write Conflicts and Transactions

A transaction (t1) can be subject to a writeConflict if another write operation modifies the same document (D1) after the transaction starts and before the transaction itself tries to modify it. This can happen whether or not the other write operation is in a transaction. This is shown in the left image as an independent statement, and in the right image as a transaction (t2).

Here is an example of a writeConflict error as seen from the mongodb shell:

> coll1.update( { _id: 1 },{ $set: { a: 5 } });
	"errorLabels" : [
	"operationTime" : Timestamp(1566806753, 1),
	"ok" : 0,
	"errmsg" : "WriteConflict",
	"code" : 112,
	"codeName" : "WriteConflict",
	"$clusterTime" : {
		"clusterTime" : Timestamp(1566806753, 1),
		"signature" : {
			"keyId" : NumberLong(0)

Once a transaction acquires the lock on a document, write operations on that document that are happening outside the transaction will need to wait until the commit/abort of the running transaction holding the lock.

When a writeConflict is returned, the driver can safely retry the transaction using our new transaction callback API that allows to automatically retry in case of TransientTransactionError (such as writeConflicts) or UnknownTransactionCommitResult. For more details on our new callback API please refer to your driver documentation.

The Read Answer

Now that we know what happens every time we perform write operations inside a transaction, we can focus on what happens when we perform read operations.

When a transaction is started, we are guaranteed to read from a consistent point in time at the cluster level ("snapshot"). The data that we are reading inside the transaction is not affected by any write operation that happens outside the transaction.

For example, when transaction t1 starts it gets a snapshot of the data, including document D1 which it plans to read. Meanwhile transaction t2 has its own snapshot and it deletes document D1. Transaction t1 can still read document D1 though because it is referring to its snapshot and is therefore isolated from other transactions write operations.

What this means, practically, is that we can't be sure that, when reading documents inside a transaction, the document won't change during the lifetime of the transaction.

In the relational world this is resolved by using SELECT...FOR UPDATE. This statement allows locking rows for reading as though they were updated, preventing other operations from modifying or deleting them until the transaction ends.

The same behaviour can be reproduced in MongoDB by acquiring a write lock on the document so other transactions that try to write to it will fail with a writeConflict.

But what should we update? We can't just rewrite an existing field's value with its current value because MongoDB is efficient and will not modify a document if a write does not actually change the existing value.

Consider the following example, in the collection we have the document:

{ _id: 1, status: true }

And we perform the following update inside the transaction:{ _id: 1 }, { $set: { status: true } })

Since we are not modifying the document (status was already true), the transaction won't try to acquire any lock on it.

Getting a Write Lock while Reading

To ensure we get a write lock, we have to make sure that we are writing a new value to our document. Usually we don't have a field that we can just change to make a write happen, but MongoDB's flexible schema does make it easy for us to set an attribute we're only going to use exclusively to acquire our write lock.

The next question is what value should we set our new attribute to? It needs to be a value that we know will be different from whatever value may already be in the field. We already have a data type in MongoDB with those properties: ObjectId. Since ObjectId is generated based on a combination of the Unix Epoch, random values that are unique at machine and process level, and a counter, it's extremely unlikely for the same value to be generated more than once. That means when we update it, the update will set the field to a different value to what's already there. This ensures that the update will always happen and the write lock be acquired.

What does that look like in code? Like this:

var doc ={ _id: 1 },    
      { $set: { myLock: { appName: "myApp", pseudoRandom: ObjectId() } } })

The beauty of this method is that there is no extra step we need to do to unlock the document other than commit or abort the transaction. The value of the locking field - in our example, myLock - can be anything. It does not matter what the value is, as long as changing the value makes a write happen and the write lock is acquired. Using findOneAndUpdate also keeps the number of round trips to the database down to one as it both reads the document and updates the locking field.

The value of the locking field does not need to be read by any other transaction. However, you can use the locking field to store information on which application is locking the thread. In our example, myLock is an object with the name of the application taking the lock and the locking field.

In Closing

The ability to lock documents through MongoDB's transaction support is a powerful aid to ensuring consistency. Remember, even though MongoDB supports transactions, all our existing best practices are still valid and you should use transactions only when needed. You should always look to leverage the rich and flexible MongoDB document format which in many cases eliminates the need to use transactions at all.