Locking in SQL Server is not physical. Understanding such logical concepts takes a lot of time and baffles you a lot. I'm one of such victims. I'll try to make your work easy by writing what I got from my effort.
Locking is necessary to avail protection on the objects (table). It is applied when a particular operation is performed. These locks are applied at different levels in the database engine.
Have an important point in your mind. As I said, Locking is a logical concept. It can be understood only if you use Transactions. Otherwise the locking mechanism gets completed in a fraction of second such that you blink your eye slower than that. One more point is, you can observe Locking well if your table has a lot of rows (1 lakh or more).
For our learning purpose, let's create a table and insert 100000 records into it:
CREATE TABLE mySampleTable
(
ID1 int,
ID2 int,
SomeData varchar(100)
)
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
Thus a table is created and 100000 records are inserted into it.
Create an index on one of the columns now. Let it be a Clustered or Non-Clustered. For now, I'm creating a Non-Clustered Index.
CREATE NONCLUSTERED INDEX NON_IX ON mySampleTable(ID1 ASC)
Creating an index demonstrates Object and Page Level Locking
SQL Server applies locks at 3 levels mainly
1. Row Level
2. Page Level
3. Object Level (Table Lock)
All the above levels of locks are applied at a time depending on the transaction. I'll try to show them with examples of different types of locks.
Shared Lock (S) / Intent Shared Lock (IS)
When a transaction tries to read data from a table, a Shared Lock is applied. When some transactions acquired shared locks on a page, no other transaction can get an Exclusive Lock (X) on the page unless all the existing shared locks are released. But Shared Lock gets released as soon as the data is read.
We created an index above. When query predicate contains Indexed Column, Shared Lock (S) is applied at Row Level while Intent Shared Lock (IS) is applied at Object and Page levels. If the query predicate contains non-indexed column then no intent locks are applied. Only a Shared Lock (S) is applied.
Shared Lock cannot be observed though you read the data through a transaction because the lock is released as soon as the data is read without depending on the completion of the transaction. In such case, you need to use a table hint to observe the shared lock.
Let me clear your confusion by showing a practical.
Now, Run the following query in a Query Window
BEGIN
TRAN
SELECT
*
FROM
mySampleTable
WITH
(HOLDLOCK)
WHERE
ID1
=
2549801 --- (Index is created on column ID1)
You will get data. Don't end the transaction with a COMMIT or ROLLBACK.
Open a new Query Window and run the following query
SELECT
resource_type
as
type,
request_status
as
status,
request_mode
as
mode,
request_session_id
as
spid,
resource_description
as
resource,
resource_database_id
as
dbid
FROM
sys.dm_tran_locks
WHERE
resource_database_id
=
6;
(NOTE: Please make a note that the query predicate resource_database_id differs from database to database. My database's id is 6. To know the id of your database use SELECT
DB_ID('<your-database-name>') and enter the result in the query predicate)
Now see the result pane. You can observe that there is a Shared Lock (S) applied at the Row Level and an Intent Shared Lock (IS) is placed at Page Level and Object Level.
Intent Shared Lock (IS) on Object and Page means, on this particular Page and Object, other locks like Intent Exclusive (IX) or one more Intent Shared (IS) can be placed. In the above situation, IS on Page and Object, S on Row are applied because in the transaction we searched data based on the column on which Index is created. When an index is created it divides the table into pages and accommodates some number of rows per page. It applies Intent Locks at Object and Page levels, normal locks at row levels.
If you search based on non-indexed columns, then no page and row level locks are applied. Only a lock at the Object Level is applied. Here's a small example of that.
Run the following query:
BEGIN
TRAN
SELECT
*
FROM
mySampleTable
WITH
(HOLDLOCK)
WHERE
ID2=1
Note that the query predicate contains non-indexed column in the above query. If you look at the lock applied by above query then
.png)
Here a normal Shared Lock (S) is applied at Object level as we searched based on the non-indexed column.
One more thing about Shared Lock is, it can allow other transactions to acquire a Shared Lock on the same rows and pages to read the data. It doesn't allow an Exclusive Lock (X) which tries to update the data. An Exclusive Lock (X) is allowed only after all the shared locks are released. Let's see an example of this situation.
Exclusive Lock (X) / Intent Exclusive Lock (IX)
This lock is like a possessive wife. It doesn't allow any other locks to be applied on the page simultaneously. Shared Lock (S) allows another shared lock but only one Exclusive Lock(X) can be applied at one time. This is applied when DML operations are performed on the table.
To apply this, there is no need to use any table hint like we used for Shared Lock (S). Let's have a small demonstration on Exclusive Lock (X):
Run the following query in a query window:
BEGIN
TRAN
UPDATE
mySampleTable
SET
ID2=3
WHERE
ID1
=
2549801 --- (Index is created on ID1)
Don't commit or rollback this, open a new query window and run the following query:
SELECT
resource_type
as
type,
request_status
as
status,
request_mode
as
mode,
request_session_id
as
spid,
resource_description
as
resource,
resource_database_id
as
dbid
FROM
sys.dm_tran_locks
WHERE
resource_database_id
=
6;
.png)
You can observe that Intent Exclusive Locks (IX) are applied at Object and Page levels and an Exclusive Lock (X) is applied at Row Level. As I said earlier, Intent locks allow other intent locks but normal locks do not. Now, open a new query window and run the following query:
SELECT
*
FROM
mySampleTable
WHERE
ID1
=
2549801
You can see that this query keeps executing but no result comes out. Let's see what's happening now. Run the following query:
SELECT resource_type as type, request_status as status,
request_mode as mode, request_session_id as spid,
resource_description as resource, resource_database_id as dbid
FROM sys.dm_tran_locks
WHERE resource_database_id = 6;
.png)
You can observe what I said. At the Page and Object levels, there are two different locks applied. One is Intent Shared (IS) and another Intent Exclusive (IX). But at the row level, there is a Shared Lock (S) with status WAIT. This means existing Exclusive Lock (X) at Row Level is not allowing Shared Lock (S) to read the same row. The S is waiting for X to be released. To release that, end the first update transaction by COMMIT or ROLLBACK. Then you can read the data from the other transaction.
To make Shared Lock (S) not to wait, you can use table hint (NOLOCK) but this just gets the data from table. It doesn't guarantee that it is the correct data because the user who initiated update transaction hasn't ended it. He may COMMIT it or ROLLBACK it. If he rolls back then you may get wrong data. This concept is called DIRTY READS.
That's all about Exclusive Lock (X).
Update Lock (U) / Intent Update Lock (IU)
Update Lock (U) is considered as a child of above two locks. It is difficult to show practically. It is applied by a transaction before performing a DML operation.
Suppose a transaction needs to update data in a table as follows:
BEGIN
TRAN
UPDATE
mySampleTable
SET
ID2=1
WHERE
ID1=2549801
The Query Optimizer, before updating the row, it has to read the row which needs to be updates. Here in the above transaction, firstly the row with ID1=2549801 has to be read and then Update should be done. So while reading that row, an Update Lock (U) is applied. After reading, existing Update Lock is converted to Exclusive Lock (X) and updating is done. So, we can't notice when an Update Lock (U) is applied. But we can force the optimizer to hold update lock as follows:
BEGIN
TRAN
SELECT
*
FROM
mySampleTable
WITH
(UPDLOCK)
WHERE
ID1
=
2549801
Run the following query again
SELECT resource_type as type, request_status as status,
request_mode as mode, request_session_id as spid,
resource_description as resource, resource_database_id as dbid
FROM sys.dm_tran_locks
WHERE resource_database_id = 6;
.png)
See that there are both (IX) and (IU) applied, one at Object level and the other at Page level respectively and a (U) at Row Level. If the same transaction tries to update data then an (X) is applied at Row level and update is done. Update Lock (U) needs to be escalated to Exclusive Lock (X) always.
The Update Lock (U) is used mainly when transaction isolation level is set to either REPEATABLE READ or SERIALIZABLE. At those levels, Update Locks are applied to avoid Deadlock problems.
Shared with Intent Exclusive Lock (SIX)
Shared with Exclusive Lock (SIX) is the combination of Shared and Exclusive Locks can be applied if a single transaction tries to read all the data as well as modify some data. It is applied at Object Level.
Take a transaction as:
BEGIN
TRAN
SELECT
*
FROM
mySampleTable
WITH
(HOLDLOCK)
UPDATE
mySampleTable
SET
ID2=98146
WHERE
ID1=53
Now in another Query Window, run the following query:
SELECT resource_type as type, request_status as status,
request_mode as mode, request_session_id as spid,
resource_description as resource, resource_database_id as dbid
FROM sys.dm_tran_locks
WHERE resource_database_id = 6;
Now see the result pane,
.png)
Now see the result pane,
You can see that an SIX is applied at Object Level while an IX and an X are applied at Page and Row levels respectively. The SIX doesn't allow any other
transaction to read or modify the data.
Now run the following query in a new
Query Window.
SELECT
*
FROM
mySampleTable
The query keeps executing without
displaying any results.
To get theoretical knowledge types of Locks, read official documentation of Microsoft
