Today I want to talk about another very interesting topic in SQL Server: disabling Row and Page Level Locks in SQL Server. Every time that you rebuild an Index in SQL Server, you can use the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options to specify that SQL Server should acquire Row and Page Level Locks when accessing your data for reading and writing. Let’s look at what happens internally when we disable these locks.
One of the most commonly seen questions when dealing with firewalls and other Internet connectivity issues is the difference between active and passive FTP and how best to support either or both of them. Hopefully the following text will help to clear up some of the confusion over how to support FTP in a firewalled environment.
This may not be the definitive explanation, as the title claims, however, I’ve heard enough good feedback and seen this document linked in enough places to know that quite a few people have found it to be useful. I am always looking for ways to improve things though, and if you find something that is not quite clear or needs more explanation, please let me know! Recent additions to this document include the examples of both active and passive command line FTP sessions. These session examples should help make things a bit clearer. They also provide a nice picture into what goes on behind the scenes during an FTP session. Now, on to the information…
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.