Does a read operation within a stored procedure in Cosmos DB take a exclusive lock on data?

user_5492375 0
Viewed 1440

Suppose that I have stored procedure that does following:

  1. Selects top 10 records matching a condition. Like say, Select TOP 10 * FROM c WHERE c.complete=false.
  2. It updates the complete flag to true for the 10 documents selected.
  3. Replaces these 10 documents that have the updated flag.
  4. Returns to client these 10 documents.

Suppose, from the client application, I spawn multiple tasks that all run this same stored procedure simultaneously.

Questions:

  1. Is it possible that the two or more simultaneous run of the stored procedure can cause it to return similar documents? Or will they run in complete isolation?

  2. Does Cosmos DB stored procedure lock the data being read?

Results observed:

None of the tasks returned same documents and the return from stored procedure was always a set of distinct documents. But I am not sure whether this behavior will be consistent. I tried running the stored procedure by spawning varying numbers of tasks as high as 20 but could not observe inconsistency.

azure-cosmosdb azure javascript
2 Answers

Questions:

  1. Is it possible that the two or more simultaneous run of the stored procedure can cause it to return similar documents? Or will they run in complete isolation?

  2. Does Cosmos DB stored procedure lock the data being read?

Cosmos DB to guarantee ACID for all operations that are part of a single stored procedure.

In Cosmos DB, JavaScript is hosted in the same memory space as the database. Hence, requests made within stored procedures and triggers execute in the same scope of a database session. This enables Cosmos DB to guarantee ACID for all operations that are part of a single stored procedure/trigger.

You could find the description above from Database program transactions in this official doc.

Update

The consistency level of stored procedure or triggers is determined by the consistency level of the cosmos DB account you set , as stated in the official document.

Cosmos DB offers five consistency levels.

I'm not sure you set which consistency level, probably just default Session consistency level.

Session consistency level only ensure strong consistency in Session life cycle. So, stored procedures you tested are not running concurrently. There will be some overlap. The subsequent updates will cover the previous updates.

Solutions:

  1. You could try to change your consistency level to Strong. It ensures the strongest consistency, but increases the delay.

  2. Or you could add transaction locks to your business layout to ensure strong consistency.

Hope it helps you.

user_81989460

This does not sound correct according to MSDN:

Data consistency

Stored procedures and triggers are always executed on the primary replica of the Azure Cosmos DB container. This ensures that reads from inside stored procedures offer strong consistency. Queries using user-defined functions can be executed on the primary or any secondary replica, but you ensure to meet the requested consistency level by choosing the appropriate replica.

https://docs.microsoft.com/en-us/azure/cosmos-db/programming#database-program-transactions

user_3445200
Related