READ UNCOMMITTED是限制性最弱的隔离级别，因为该级别忽略其他事务放置的锁。使用READ UNCOMMITTED级别执行的事务，可以读取尚未由其他事务提交的修改后的数据值，这些行为称为“脏”读。这是因为在Read Uncommitted级别下，读取数据不需要加S锁，这样就不会跟被修改的数据上的X锁冲突。比如，事务1修改一行，事务2在事务1提交之前读取了这一行。如果事务1回滚，事务2就读取了一行没有提交的数据，这样的数据我们认为是不存在的。
Much like any powerful tool with a command line interface, the WinDBG expression syntax can sometimes seem quite cryptic. In the interest of trying to unlock the power of the command line interface, in this article we’re going to cover a fundamental WinDBG concept that is key to understanding those long, strange commands: the expression evaluators.
As it turns out, there are two built in expression evaluators in WinDBG, the MASM evaluator and the C++ evaluator. In order to better understand the differences, we’ll first discuss the MASM evaluator in detail and then move on to the C++ evaluator.
Please note that this article is not going to attempt to be definitive and list every possible operator or feature of the expression evaluators. We’re simply going to focus on what we find to be the most useful in the hopes of getting you started. However, once you’ve gotten your feet wet full details are available in the WinDBG documentation.
In todays blog posting I want to talk about Lock Escalations in SQL Server. Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server. Let’s start in the first step with the description of the so-called Lock Hierarchy in SQL Server, because that’s the reason why the concept of the Lock Escalations exists in a relational database like SQL Server.
The following picture shows you the lock hierarchy used by SQL Server:
I blogged 2 years ago about why we need UPDATE locks in SQL Server. Today I want to continue this discussion by talking about Intent Locks in SQL Server, and why they are needed.
When I talked about Lock Escalations in SQL Server, I started by briefly mentioning that SQL Server uses a Lock Hierarchy when you read or change your data.
When you read a row, SQL Server always acquires by default a Shared Lock (S), and when you change a row SQL Server acquires an Exclusive Lock (X). Those Locks are incompatible with each other, and that will introduce blocking situations when you want to read/write a row concurrently.
In addition to the row level locks, SQL Server also acquires so-called Intent Locks at higher levels within the Lock Hierarchy: at the page and at the table level. SQL Server acquires the following Intent-Locks based on the requested row level lock:
- Intent Shared Lock (IS), when you have a Shared Lock at the row level
- Intent Update Lock (IU), when you have an Update Lock at the row level
- Intent Exclusive Lock (IX), when you have an Exclusive Lock at the row level
Therefore you always get the Lock Hierarchy as shown above when you read and write your records. But why is SQL Server using these Intent Locks?
Today I want to talk about a specific question that I almost get every time when I teach about Locking & Blocking in SQL Server: Why does SQL Server need to have Update Locks? Before we go down to the details of why they are needed, I first want to give you a basic overview of when an Update (U) Lock is acquired, and how the lock itself behaves regarding its compatibility.
In general an Update Lock is used in SQL Server when performing an UPDATE statement. When you look at the underlying query plan, you can see that such a plan always consists of 3 parts:
- Reading data
- Calculating new values
- Writing data
When SQL Server initially reads the data to be changed in the first part of the query plan, Update Locks are acquired on the individual records. And finally these Update Locks are converted to Exclusive (X) Locks when the data is changed in the third part of the query plan. The question that arrises with this approach is always the same: why does SQL Server acquire Update Locks instead of Shared (S) Locks in the first phase? When you normally read data through a SELECT statement, a Shared Lock is also good enough. Why is there now a different approach with UPDATE query plans? Let’s have a more detailed look at it.
Snapshot Isolation level was introduced in SQL Server 2005 and has been available ever since. Snapshot isolation levels improve performance but there are some things to take into consideration when using this feature. Some people use it frequently as it minimizes blocking and improves performance/concurrency without knowing its impact on maintaining versions in tempdb, whereas some people stay away from it because of this extra overhead. Some people get confused about the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one where the other is needed or vice versa.
In this article series, I am going to discuss what snapshot isolation levels are, their variants, why and when we should use them and how we should start using this feature with examples.
SQL Server provides two physical implementations of the read committed isolation level defined by the SQL standard, locking read committed and read committed snapshot isolation (RCSI). While both implementations meet the requirements laid down in the SQL standard for read committed isolation behaviours, RCSI has quite different physical behaviours from the locking implementation we looked at in the previous post in this series.
To avoid deadlocks, we switched from ReadCommittedSnapshot isolation to SnapShot isolation for a SQL Server database at the database level and transaction level in the client code. Now, when two users perform concurrent operations on the database through the client, one of the clients get this error:
“Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.cust_table’ directly or indirectly in database ‘cust_database’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.”
What can we do to avoid deadlocks and update conflicts at the same time?
(The same code with Oracle database and Oracle client works without any issues with the default Read Committed Snapshot isolation level)