Transactions and optimistic concurrency control
APPLIES TO: SQL API
The database engine in Azure Cosmos DB supports full ACID (Atomicity, Consistency, Isolation, Durability) compliant transactions with snapshot isolation. All the database operations within the scope of a container's logical partition are transactionally executed within the database engine that is hosted by the replica of the partition. These operations include both write (updating one or more items within the logical partition) and read operations. The following table illustrates different operations and transaction types:
|Operation||Operation Type||Single or Multi Item Transaction|
|Insert (without a pre/post trigger)||Write||Single item transaction|
|Insert (with a pre/post trigger)||Write and Read||Multi-item transaction|
|Replace (without a pre/post trigger)||Write||Single item transaction|
|Replace (with a pre/post trigger)||Write and Read||Multi-item transaction|
|Upsert (without a pre/post trigger)||Write||Single item transaction|
|Upsert (with a pre/post trigger)||Write and Read||Multi-item transaction|
|Delete (without a pre/post trigger)||Write||Single item transaction|
|Delete (with a pre/post trigger)||Write and Read||Multi-item transaction|
|Execute stored procedure||Write and Read||Multi-item transaction|
|System initiated execution of a merge procedure||Write||Multi-item transaction|
|System initiated execution of deleting items based on expiration (TTL) of an item||Write||Multi-item transaction|
|Change Feed||Read||Multi-item transaction|
|Paginated Read||Read||Multi-item transaction|
|Paginated Query||Read||Multi-item transaction|
|Execute UDF as part of the paginated query||Read||Multi-item transaction|
Optimistic concurrency control
Optimistic concurrency control allows you to prevent lost updates and deletes. Concurrent, conflicting operations are subjected to the regular pessimistic locking of the database engine hosted by the logical partition that owns the item. When two concurrent operations attempt to update the latest version of an item within a logical partition, one of them will win and the other will fail. However, if one or two operations attempting to concurrently update the same item had previously read an older value of the item, the database doesn’t know if the previously read value by either or both the conflicting operations was indeed the latest value of the item. Fortunately, this situation can be detected with the Optimistic Concurrency Control (OCC) before letting the two operations enter the transaction boundary inside the database engine. OCC protects your data from accidentally overwriting changes that were made by others. It also prevents others from accidentally overwriting your own changes.
The concurrent updates of an item are subjected to the OCC by Azure Cosmos DB’s communication protocol layer. For Azure Cosmos accounts configured for single-region writes, Azure Cosmos DB ensures that the client-side version of the item that you are updating (or deleting) is the same as the version of the item in the Azure Cosmos container. This ensures that your writes are protected from being overwritten accidentally by the writes of others and vice versa. In a multi-user environment, the optimistic concurrency control protects you from accidentally deleting or updating wrong version of an item. As such, items are protected against the infamous "lost update" or "lost delete" problems.
In an Azure Cosmos account configured with multi-region writes, data can be committed independently into secondary regions if its
_etag matches that of the data in the local region. Once new data is committed locally in a secondary region, it is then merged in the hub or primary region. If the conflict resolution policy merges the new data into the hub region, this data will then be replicated globally with the new
_etag. If the conflict resolution policy rejects the new data, the secondary region will be rolled back to the original data and
Every item stored in an Azure Cosmos container has a system defined
_etag property. The value of the
_etag is automatically generated and updated by the server every time the item is updated.
_etag can be used with the client supplied
if-match request header to allow the server to decide whether an item can be conditionally updated. The value of the
if-match header matches the value of the
_etag at the server, the item is then updated. If the value of the
if-match request header is no longer current, the server rejects the operation with an "HTTP 412 Precondition failure" response message. The client then can re-fetch the item to acquire the current version of the item on the server or override the version of item in the server with its own
_etag value for the item. In addition,
_etag can be used with the
if-none-match header to determine whether a refetch of a resource is needed.
_etag value changes every time the item is updated. For replace item operations,
if-match must be explicitly expressed as a part of the request options. For an example, see the sample code in GitHub.
_etag values are implicitly checked for all written items touched by the stored procedure. If any conflict is detected, the stored procedure will roll back the transaction and throw an exception. With this method, either all or no writes within the stored procedure are applied atomically. This is a signal to the application to reapply updates and retry the original client request.
Learn more about database transactions and optimistic concurrency control in the following articles: