Azure-sdk-for-js: Unique Key Policy is not being enforced

[donaldduy-lb]

• package: @azure/cosmos
• version: ^3.6.2
• Linux:
• [x] nodejs
  
  
  
  • version: ^8.5.4
  
  
• [x] Google Chrome
  
  
  
  • version/name: 88.0.4324.190 (Official Build) (64-bit)
  
  
• [x] typescript
  
  
  
  • version: ^3.7.3
  
  

Describe the bug
The container I have created is not following the unique key policy that I have set. I suspect this is due to the fact that both items are being persisted at the exact same time.

To Reproduce
Steps to reproduce the behavior:

1. Insert two items that have the exact same _ts, subject and seq value.
2. Both items persist in the database.

Expected behavior
I expect one or both of these documents to get rejected from persistence.

Screenshots

Additional context
Here is the code I used to create the container and set the unique key policy:

await db.containers.createIfNotExists({
  id: eventstore,     
  partitionKey: { paths: ["/subject"] },    
  uniqueKeyPolicy: {     
     uniqueKeys: [   
         { paths: ["/seq"] }   
     ]    
  }   
})

I am also using a stored procedure to insert items into this database:

function bulkInsertItems(items) {
  var container = getContext().getCollection();
  var containerLink = container.getSelfLink();

  // Validate input
  if (!items) throw new Error("The array is undefined or null.");

  // End if there are no items
  if (items.length == 0) {
    getContext().getResponse().setBody(0);
  }

  items.map(item => {
    var isAccepted = container.createDocument(containerLink, item);

    // If the item fails to persist, throw an error and CosmosDB will handle rolling back
    if (!isAccepted) {
      throw new Error("Unable to create item");
    }
  })

  getContext().getResponse().setBody(items.length);
}

[msftbot[bot]]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @southpolesteve, @zfoster

[zfoster]

Hey @donaldduy-lb, taking a look at this now

[donaldduy-lb]

@zfoster Hi Zachary, just wondering if there has been any progress on this issue?

[zfoster]

hey @donaldduy-lb, still looking, I should have an update soon

[zfoster]

hey @donaldduy-lb, are you able to try using the new bulk apis on container? I'm going to work on reproducing this but I suspect this may be an issue with stored procedures. Here's a simple sample: https://github.com/Azure/azure-sdk-for-js/blob/master/sdk/cosmosdb/cosmos/samples/Bulk.ts

[donaldduy-lb]

@zfoster Thanks for bringing this to my attention! I'm also suspecting the issue lies with the stored procedure, I'll give the API a shot and update you soon

[donaldduy-lb]

Hey @zfoster, quick question. When using bulk, is a rollback performed on the database if any of the operations fail?

[zfoster]

It is not - we're calling that Transactional batch and there are some asks for it. It's not actively being worked on but there's definitely a plan to get it done in the coming months https://github.com/Azure/azure-sdk-for-js/issues/13368

[donaldduy-lb]

I don't think I can reliably use bulk then. The database I have is using CQRS and Event Sourcing architecture. The original intent of using this stored procedure was to perform a transaction. We either want all the events to be stored or none of them if any of events in the batch fails to persist.

[zfoster]

Sounds good, thanks for the info. We've had a few asks like I said for Transactional batch so I can see that coming soon. I'm still planning to find time to verify I can reproduce this and would then hand it to the backend team.

[zfoster]

hey @donaldduy-lb i have a working sample (it correctly throws an error due to violating a unique index constraint). Could you by chance paste me a couple items I can attempt to use to reproduce? I've tried with undefined partition key and with subject

[zfoster]

``async function run() { const client = new CosmosClient({ endpoint, key: masterKey }) const { database: db } = await client.databases.create({ id: 'duplicate-id-test' +${Math.random() * 10000}}) const id =${Math.random() * 10000}`
const { container } = await db.containers.createIfNotExists({
id,
partitionKey: { paths: ["/subject"] },
uniqueKeyPolicy: {
uniqueKeys: [
{ paths: ["/seq"] }
]
}
})

const sproc: StoredProcedureDefinition = {
    id: 'bulkInsertItems',
    body: function (items) {
        var container = getContext().getCollection();
        var containerLink = container.getSelfLink();

        // Validate input
        if (!items) throw new Error("The array is undefined or null.");

        // End if there are no items
        if (items.length == 0) {
            getContext().getResponse().setBody(0);
        }

        items.map(item => {
            var isAccepted = container.createDocument(containerLink, item);

            // If the item fails to persist, throw an error and CosmosDB will handle rolling back
            if (!isAccepted) {
                throw new Error("Unable to create item");
            }
        })

        getContext().getResponse().setBody(items.length);
    }
}

const items = [
    {id: '1',
    subject: 'paper plastic cup',
    seq: 13},
    {id: '2',
    subject: 'paper plastic cup',
    seq:13}
]

const { resource: storedProcedure } = await container.scripts.storedProcedures.create(sproc)
const response = await container.scripts.storedProcedure(storedProcedure.id).execute('paper plastic cup', [items]);
console.log(response)

}

run().then(console.log).catch();
```

here's the code I'm using. Notice anything off?

[donaldduy-lb]

Nope that looks correct. I think the issue occurs specifically when the two items are inserted independently at exact the same time - I'm guessing there isn't a way to evaluate whether there is a violation if both items come into existence at the same time.

Perhaps something like 10 items that violate the unique key constraints and performing a Promise.all() on 10 independent calls to the stored procedure would better replicate my issue?

[zfoster]

Hmm I'm still encountering an error with a Promise.all inserting 10 items, a Promise.all inserting 1 item, and separate promises inserting 1 or 10 items

[donaldduy-lb]

Let me try to recreate this on my end

[donaldduy-lb]

@zfoster You're right, I'm getting the error as well. This is so strange, I'm consistently experiencing unique key constraint violations in my production environment. Do you have any idea how else this could happen?

[zfoster]

@donaldduy-lb if you're having trouble reproducing, you could consider filing a support ticket. We may eventually reproduce in the SDK but the SDK doesn't really do anything here, if the backend is capable of inserting multiple items which violate a unique key policy, I suspect nothing we can change in the SDK will fix that issue.

[zfoster]

I'll close this for now, feel free to reopen if you end up with some reproducible. Happy to find an SDK-side fix if there is one.