Concurrency
Optimistic Concurrency
Concurrency management is often a concern in shared systems. In some cases, when multiple users attempt to modify the same data simultaneously, a 'last write wins' approach is employed. This means that the changes made by the last user to submit their modifications will be accepted, potentially overwriting any earlier changes. This strategy is part of optimistic concurrency control, where conflicts are resolved after the modifications are attempted.
Please see below for situations where this approach leads to potential issues.
Example 1
Consider a scenario where two threads concurrently access and read the quantity of a product, both obtaining a value of 2. If each thread then increments the quantity by 1, the final result would be 3 for each thread. Ideally, the desired outcome would be a quantity of 4.
Example 2
Imagine a product with fields for price and currency in a database. Two concurrent threads retrieve the product simultaneously, with one thread intending to change the currency and the other aiming to alter the price. This situation can lead to an inconsistent state. For instance:
- The original value is £10.99.
- Thread 1 sets it to $10.99.
- Thread 2 sets it to £12.99.
Since the system typically updates only the fields that have changed, thread 1 updates the currency, and thread 2 updates the price, resulting in a final value of $12.99.
Note that the behavior of updating only the changed fields is the default in EF Core but needs manual activation in NHibernate by enabling dynamic insert/update. In KIT, we automatically enable this feature for all entities, which is worth keeping in mind.
Solution 1
To address concurrency issues, it's advisable to incorporate a row version field into your entities and configure it as follows:
- EF Core: modelBuilder.Entity<Product>().Property(x => x.RowVersion).IsRowVersion()
- NHibernate: Property(x => x.RowVersion, m => m.Type(NHibernateUtil.Binary).Generated(Generated.Always)) NEEDS TESTING
The database should update the row version automatically whenever a row is modified. If your database system doesn't support automatic row versioning (e.g. SQLite), then you will have to manually update it.
With row versioning in place, when an entity is updated, the system ensures that the row version matches the value it had when initially retrieved. This is achieved by applying a WHERE clause to the update operation. If the row version has changed, indicating a concurrent modification, an exception is thrown. In such cases, retrying the operation by re-fetching the entity with the latest row version and reapplying the changes is recommended. You should apply this retry logic within a while loop until the update succeeds.
Note: The row version filter will not automatically be added when doing bulk updates (e.g., using ExecuteUpdateAsync in EF Core or UpdateAsync in NHibernate). In such cases, you must manually incorporate the row version filter into your update and determine whether the update was successful to decide whether to retry.
Solution 2
In EF Core, another approach to address concurrency issues is to map the Price and Currency properties as Concurrency Tokens. With this configuration, a row version field becomes unnecessary. Instead, when an update operation is attempted, EF Core ensures that the price and currency values match the ones retrieved initially, achieved through a WHERE clause in the update query. In case of a mismatch, indicating concurrent modifications, it's advisable to re-fetch the entity and retry until it succeeds.
Solution 3
In EF Core, you have the option to manually mark both the Price and Currency properties as dirty, ensuring that they are both included in the update statement. Alternatively, you can mark the Price as dirty if the Currency has changed, and vice versa.
While this approach resolves the issue presented in the second example by ensuring both properties are updated, it may not fully address the problem in the first example, as the update relies on the original state, which may have since changed.
Disconnected Updates
Disconnected updates occur when there's a significant delay between sending data to a client and receiving subsequent updates, commonly encountered in frameworks like ASP.NET.
Similar to scenarios with concurrent server-side updates, this situation presents challenges when multiple users are modifying the same data retrieved earlier. One pessimistic approach is to prevent simultaneous modifications by locking the data. Alternatively, optimistic solutions, as discussed earlier, can also be effective. However, it might be beneficial to shift the retry loop to the client side. In other words, when an optimistic concurrency error occurs, users are notified to retry until successful.
To minimize the window for encountering optimistic concurrency errors during disconnected updates, it's advisable to always retrieve the latest data when it's sent back to the server and apply any changes to that dataset.
Database Concurrency
SQL Isolation Levels
SQL Isolation levels prevent concurrent sessions from interfering with one another. The isolation level is set against the transaction and data retrieved during a transaction should be consistent and shouldn't allow another session to interfere with it.
As you move down the list of isolation levels below you will run into fewer issuers but will perform worse. Each one will include the same fixes as the ones above them, but will also solve another problem (from left to right).
Please note that Snapshot is a Microsoft specific isolation level and has to be manually enabled.
| Isolation Level | Dirty Read | Non-repeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes | Yes |
| Repeatable Read | No | No | Yes | Yes |
| Snapshot | No | No | No | Yes |
| Serializable | No | No | No | No |
To better understand them, we will walk through how each isolation level may fail.
Please note that the following scenarios can be replicated by creating 2 instances of SQL Server Management Studio, where each instance will act as a session.
Read Uncommitted Isolation
| Session 1 | Session 2 |
|---|---|
| DROP TABLE foo IF EXISTS foo CREATE TABLE foo (bar INT) INSERT INTO foo (bar) VALUES (1), (2) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION SELECT * FROM foo | |
| BEGIN TRANSACTION UPDATE foo SET bar = 8 WHERE bar = 1 | |
| SELECT * FROM foo This will return the update from session 2 since it can read the un-committed (dirty) data. COMMIT TRANSACTION | |
| COMMIT TRANSACTION |
Read Committed Isolation
| Session 1 | Session 2 |
|---|---|
| DROP TABLE foo IF EXISTS foo CREATE TABLE foo (bar INT) INSERT INTO foo (bar) VALUES (1), (2) SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION SELECT * FROM foo | |
| BEGIN TRANSACTION UPDATE foo SET bar = 8 WHERE bar = 1 This will create a write lock on the modified data. | |
| SELECT * FROM foo This will block because it cannot read the un-committed data from session 2. Once session 2 is committed, this query will execute but includes the update from session 2. This is known as a non-repeatable read (since it's result is different from the query above). COMMIT TRANSACTION | |
| COMMIT TRANSACTION |
Repeatable Read Isolation
As you've seen above, Read Committed solves the problem of dirty reads, by locking the update until it is committed. However, you now suffer from non-repeatable reads. This can be fixed by using Repeatable Read isolation level, which not only locks the updated rows, but will also lock the data read.
| Session 1 | Session 2 |
|---|---|
| DROP TABLE foo IF EXISTS foo CREATE TABLE foo (bar INT) INSERT INTO foo (bar) VALUES (1), (2) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM foo This will create a read lock on the data selected. | |
| UPDATE foo SET bar = 8 WHERE bar = 1 This will block since it is trying to update data which has a read lock. Once session 1 is committed, the update will execute and a write lock will be created against the modified data. | |
| SELECT * FROM foo COMMIT TRANSACTION |
Repeatable Read can lead to the occurrence of phantom reads. Here's an example to demonstrate that:
| Session 1 | Session 2 |
|---|---|
| DROP TABLE foo IF EXISTS foo CREATE TABLE foo (bar INT) INSERT INTO foo (bar) VALUES (1), (2) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM foo This will create a read lock on the data selected. | |
| INSERT INTO foo (bar) VALUES (3) This will execute since the original data read (in session 1) doesn't include this inserted row. | |
| SELECT * FROM foo This will include the data from session 2, this is known as a phantom read. This can be fixed by using a Serializable isolation level. COMMIT TRANSACTION |
Further example:
| Session 1 | Session 2 |
|---|---|
| DROP TABLE foo IF EXISTS foo CREATE TABLE foo (bar INT) INSERT INTO foo (bar) VALUES (1), (2) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM foo WHERE bar >= 2 This will create a read lock on the data selected. | |
| UPDATE foo SET bar = 8 WHERE bar = 1 This will execute since the original data read (in session 1) doesn't include this updated row. | |
| SELECT * FROM foo This will include the update data from session 2, since no lock was taken when we read it above and therefore the update went through. COMMIT TRANSACTION |
MVCC / RCSI
Phantom reads are not a concern in PostgreSQL due to its implementation of Multi-Version Concurrency Control (MVCC). Instead of using locks, PostgreSQL takes a snapshot of the database, minimizing the risk of deadlocks.
However, this approach can affect performance as it requires creating multiple snapshots. Nonetheless, there's a performance gain since locking is no longer necessary.
One drawback is that PostgreSQL only reads data as of the snapshot creation time. This can result in reading outdated data, particularly if the transaction persists for an extended period.
This behavior can also be enabled in Microsoft SQL Server by executing the following query:
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ONThis feature is referred to as Read Committed Snapshot Isolation (RCSI) in Microsoft SQL Server. If you create your database using EF Core migrations, RCSI is enabled by default.
It's important to note that these snapshots are stored within the temp db, so it's crucial to monitor its size.
Enabling RCSI in Microsoft SQL Server offers the advantage of eliminating the need for the "WITH (NOLOCK)" query hint. This is particularly beneficial for preventing slow queries caused by waiting for locks to be released, as no locks are generated initially (except for Repeatable Read, as discussed below). Additionally, it's worth noting that using query hints is generally considered bad practice.
When you enable RCSI in SQL Server, it allows readers to access the last committed version of data without being blocked by concurrent writers. This means that readers will not block writers, and writers will not block readers, similar to the behavior in PostgreSQL. However, RCSI in SQL Server only applies to the Read Committed isolation level, not to the Repeatable Read isolation level.
In SQL Server, when you use the Repeatable Read isolation level, readers can still experience blocking due to the use of shared locks to maintain consistency. This is different from PostgreSQL, where the default behavior with MVCC allows for non-blocking reads even at the Repeatable Read isolation level.
After enabling RCSI, you should be able to re-run all the scenarios mentioned above and achieve similar results. However, you won't encounter blocking issues except in the case of Repeatable Read isolation level, where updating data that has a shared lock (read in another transaction) will still block until it is committed. In this case you should use Snapshot Isolation, which doesn't suffer from this issue.
Snapshot Isolation
As mentioned previously, this needs to be manually enabled in Microsoft SQL Server. This can be done by running the following query:
ALTER DATABASE test SET ALLOW_SNAPSHOT_ISOLATION ONThis isolation level sits between repeatable read and serializable. It will give consistent reads as a snapshot is created for the transaction, but write conflicts will fail and will have to retry. This is a form of optimistic concurrency where if 2 sessions update the same data, one will succeed and the other will fail.
This is equivalent to how repeatable read works in PostgreSQL.