Database Reference
In-Depth Information
Transactions and Concurrency
The concurrency model implemented in in-Memory OLTP is quite complex. Before we dive deeper into its internal
implementation, it is beneficial to remember the level of data consistency provided by different transaction isolation
levels. We have discussed this in detail in Part 3 of the topic. However, let's review several points before we start to look
at the implementation details of the concurrency model of in-memory OLTP.
Transaction Isolation Levels and Data Consistency
Any transaction isolation level resolves write/write conflicts. Multiple transactions cannot update the same row
simultaneously. Different outcomes are possible and, in some cases, SQL Server uses blocking and prevents transactions
from accessing uncommitted changes until the transaction that made these changes is committed. In other cases, SQL
Server rolls back one of the transactions due to an update conflict. In-memory OLTP uses the latter method to resolve
write/write conflicts and aborts the transaction. We will discuss this situation in detail later, so let's focus now on read
data consistency.
There are three major data inconsistency issues that are possible in multi-user environments, including:
Dirty Reads : A transaction reads uncommitted (dirty) data from other uncommitted
transactions.
Non-Repeatable Reads : Subsequent attempts to read the same data from within the same
transaction return different results. This data inconsistency issue arises when the other
transactions modified, or even deleted, data between the reads done by the affected
transaction.
Phantom Reads : This phenomenon occurs when subsequent reads within the same
transaction return new rows (the ones that the transaction did not read before). This
happens when another transaction inserted the new data in between the reads done by the
affected transaction.
Table 32-1 shows the data inconsistency issues that are possible for different transaction isolation levels.
Table 32-1. Transaction isolation levels and data inconsistency issues
Isolation Level
Dirty Reads
Non-Repeatable Reads
Phantom Reads
READ UNCOMMITTED
YES
YES
YES
READ COMMITTED
NO
YES
YES
REPEATABLE READ
NO
NO
YES
SERIALIZABLE
NO
NO
NO
SNAPSHOT
NO
NO
NO
With the exception of the SNAPSHOT isolation level, SQL Server uses locking to address data inconsistency issues
when dealing with on-disk tables. It blocks sessions from reading or modifying data to prevent data inconsistency.
Such behavior also means that, in the case of a write/write conflict, the last modification wins. For example, when two
transactions are trying to modify the same row, SQL Server blocks one of the transactions until another transaction
is committed, allowing blocked transaction to modify the data afterwards. No errors or exceptions would be raised,
however changes from the first transaction would be lost.
 
 
Search WWH ::




Custom Search