SQL Server isolation mode and lock depth analysis (i)

The server recently saw a lot of discussions on the locking patterns and how it works in SQL Server on the forums. It seems necessary to sum up.

SQL Server has 4 in isolation mode, and multiple locks. I will simply tidy up the experience, if there is a mistake, please correct me.

Objective

Isolation mode and lock are different, we must not confuse. Isolation mode is the specification of concurrency control behavior, while locks are the granularity of control locking. But both will have a significant impact on the concurrency of your application system. The default is read Committed isolation mode and row-level lock (Rowlock).

There are many differences between different databases and in this respect, there are also common places. These superficial phenomena actually lie in the difference of architecture.

One thing to point out is that we don't have to judge the difference between the pros and cons, because different database products have their own indicators. In particular, it is naïve to judge by programming convenience. As an application system, it should be in the programming development should adapt to the database, rather than let the database to adapt to programming development. Because the database selection scheme is more will not consider the convenience of programming or not. Many business logic control problems should be considered in the system design, not only rely on the database system locking mechanism to solve your application system logic problems.

Read committed mode

This is SQL Server default and is one of the most common. It is also a place that many people feel uncomfortable with Oracle.

Example:

Session 1

BEGIN Tran

INSERT into T1 values (1, ' Allan ')

Session 2

SELECT * FROM T1

Well? What's going on, being hung up. Oracle can not, I do not see 1, ' ALLAN ' of this record is not good.

This is actually the difference between Oracle and SQL Server at this point. Oracle has adopted a rollback mechanism to ensure that record locking in Read Committed mode does not affect reading of other transactions (updates are still locked). As a result, Oracle provides a stronger degree of concurrency. Obviously, SQL Server simplifies this architecture, and that's all that's natural.

SQL Server in Read committed mode, a query statement for a thing does not ignore data that is not committed by other transactions (if your query condition includes data submitted by other transactions), SQL Server will let you wait for other submissions to ensure data consistency, Obviously, the concurrency is lower than that of Oracle. If there is a waiting situation, you can judge according to this criterion.

However, when two transactions update a record or insert the same record of a primary key, there is a wait, as is the case with SQL Server and Oracle.

So let me take a look at the following examples to illustrate:

The test table is as follows:

The test table is as follows:
C1 C2 C3
----------- --------------------- --------------------
1 200.5000 Hellen
2 129.1400 Hellen
3 288.9700 Allan

Session 1:

BEGIN TRANSACTION

DELETE from Test where c1=1



Session 2:
SELECT * FROM Test
is hung at this time because the C1=1 records are included, SQL Server certainly requires you to wait.

If I don't choose C1=1 's record, it will not be waitting.
SESSION3:
SELECT * FROM Test where c1=2
SELECT * FROM Test where c1=3

C1 C2 C3
----------- --------------------- --------------------
2 129.1400 Hellen

(The number of rows affected is 1 rows)

C1 C2 C3
----------- --------------------- --------------------
3 288.9700 Allan

(The number of rows affected is 1 rows)



Nothing was hung up, everything was fine.


At this point, you can also find a very interesting, very easy to confuse you phenomenon.
Session 4
SELECT * FROM Test where c1<>1
The result was also hung up, as if rowlock out "problem"? Do not worry, originally because I this table test built a primary key (C1 field). I think this is because the update,delete operation caused the lock on the index uplink.
At this point, it is not a problem if you execute SELECT * from Test where c1>1.

So, all we have to do is force the locked portion of the index page and Index leaf node page (data page) of the clustered index to be skipped.
SELECT * FROM Test with (Fastfirstrow) where c1<>1
Sure enough, everything is OK.
Therefore, for many phenomena, we need to think further and to solve the mystery.


Below, we look through the sp_lock to illustrate

View through Sp_lock:
SPID dbid objid indid Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------ ------------------------------------
7 789577851 1 PAG 1:126 IX GRANT
7 789577851 1 KEY (010086470766) X GRANT
7 789577851 1 PAG 1:127 IX GRANT
7 789577851 2 KEY (090041892960) X GRANT
7 789577851 0 TAB IX GRANT

(1) ID 789577851 is table test, you can query sysobjects.
(2) With regard to Tab IX, is the intention exclusive lock of the table structure. At this point, if you execute the ALTER TABLE command to alter the table structure (which will have an X lock on the table structure), it will be suspended.
(3) The PAG is the page lock, is the index page lock, why there are two at this time? Obviously 1:126 is the middle Page node page of the index tree, and 1:127 is the leaf node page, which is the data page (the table storage structure of the clustered index). As a result, any action on an X-lock on an index page will be suspended, and the ix,s will not, and SQL Server will further determine the row-level lock. At this point, you can test with the select * from Test with (Paglock) where c2=2.
(4) key (010086470766), the two X of key (090041892960) is most obvious, is the row level exclusive lock. One is the row-level lock on the index's middle page, and one is the row-level lock on the leaf node (data page).

This is the case with SQL Server's most commonly used Read Committed isolation mode, which continues to discuss the READ UNCOMMITTED isolation mode next time.