Unique index & transaction: E11000 duplicate key error inside transaction

Hello, I would like some help understanding how unique indices are updated with regards to transactions.

Starting with a collection with unique index on field “last_name”, and a document with “last_name” = “Smith”, lets say that I want to perform an atomic update using a transaction, such that:

  • while transaction executes, any operations outside transaction see old document Smith
  • after transaction commits, a new document is created with last_name Smith, while old document last_name has been updated to something else.

This however seems not possible to do? It seems that inside transaction itself while I can update old document to be “Smith_Old”, and a Find inside transaction is aware of this update, the Insert of “Smith” inside transaction fails due to duplicate key error.

My guess is that this is due to unique index being only updated AFTER transaction commits - is this true? I have been looking at mongo docs but I could not find any details regarding this.

Would someone be able to provide details /documentation links - confirm what I am trying to do here is impossible with mongo?
The actual use case here is mongo fle data encryption key rotation. I’d love to be able to rotate key and keep consumers happy with existance of static keyAltName while the underlying key changes.

Code reproducing this (Go lang):

    func TestTransactionWithIndex(t *testing.T) {
	ctx := context.Background()
	clientOpts := options.Client().ApplyURI("mongodb://localhost:27017/testDd")
	client, err := mongo.Connect(ctx, clientOpts)
	if err != nil {
		t.Fatalf("Error connecting to mongo: %v", err)
	}
	defer func() { _ = client.Disconnect(ctx) }()

	wcMajority := writeconcern.New(writeconcern.WMajority(), writeconcern.WTimeout(10*time.Second))
	wcMajorityCollectionOpts := options.Collection().SetWriteConcern(wcMajority)
	testIndexColl := client.Database("testDd").Collection("testIndex", wcMajorityCollectionOpts)
	err = testIndexColl.Drop(ctx)
	if err != nil {
		t.Fatalf("Cleanup: Error dropping collection: %v", err)
	}

	session, err := client.StartSession()
	if err != nil {
		panic(err)
	}
	defer session.EndSession(ctx)

	_, err = testIndexColl.Indexes().CreateOne(ctx, mongo.IndexModel{
		Keys:    bson.D{{Key: "last_name", Value: 1}},
		Options: options.Index().SetUnique(true)})
	if err != nil {
		t.Fatalf("Error creating unique index: %v", err)
	}

	_, err = testIndexColl.InsertOne(ctx, bson.M{"last_name": "Smith"})
	if err != nil {
		t.Fatalf("Error inserting document: %v", err)
	}

	callback := func(sessCtx mongo.SessionContext) (interface{}, error) {
		// Important: You must pass sessCtx as the Context parameter to the operations for them to be executed in the
		// transaction.
		_, err := testIndexColl.UpdateOne(sessCtx,
			bson.M{"last_name": "Smith"},
			bson.M{"$set": bson.M{
				"last_name": "Smith_Old",
			}})
		if err != nil {
			t.Fatalf("Error updating Smith inside transaction: %v", err)
		}

		type person struct {
			Id       string `bson:"_id"`
			LastName string `bson:"last_name"`
		}
		result := testIndexColl.FindOne(sessCtx, bson.M{"last_name": "Smith_Old"})
		var p person
		err = result.Decode(&p)
		if err != nil {
			t.Fatalf("Error decoding result: %v", err)
		}
		fmt.Printf("Found Smith Old inside transaction after updating: %v\n", p)

		//try create a document within the same transaction with key Smith, since old Smith has been updated
		_, err = testIndexColl.InsertOne(ctx, bson.M{"last_name": "Smith"})
		if err != nil {
			t.Fatalf("Error inserting new Smith inside transaction: %v", err)
		}
		return nil, err
	}

	result, err := session.WithTransaction(ctx, callback)
	fmt.Printf("result: %v\n", result)
	if err != nil {
		panic(err)
	}
}

And this is the output of the above code when run:

=== RUN   TestTransactionWithIndex
Found Smith Old inside transaction after updating: {61914ecc8f59c2f93901bf23 Smith_Old}
    eflat_test.go:79: Error inserting new Smith inside transaction: write exception: write errors: [E11000 duplicate key error collection: testDd.testIndex index: last_name_1 dup key: { last_name: "Smith" }]
--- FAIL: TestTransactionWithIndex (2.53s)

FAIL

Also, mongo version is 4.4 and go mongo driver version is 1.7.3. Thank you

@Elena123 did you ever get an answer to this? I’ve hit the same issue recently. I want to keep the atomic nature of my two commits bit also don’t really want to drop the unique constraint that I have.