Recently, in the forum, I saw a lot of SQL Server's lock mode and discussion of working principle. It seems necessary to summarize it.
SQL Server has 4 neutralization patterns, and multiple locks. I simply organize the experience, if there is any mistake, please refer to it.
Foreword
Isolation mode and lock are different, don't mix. The isolation mode is standardized to control the control behavior, and the lock is the particle size that controls the lock. But both will have a significant impact on your application system. The default is the read committed quarantine and rowlock.
Different databases, in this regard, there are many differences and common places. These surface phenomena is actually different from the architecture.
What needs to be pointed out is: We don't want to judge the problem of this difference, because different database products have their own indicators. In particular, it is very naive than the convenience of programming. As an application system, it should be adapted to the database on programming, rather than letting a database to adapt to programming. Because the database selection scheme is even more convenient to consider programming. Many business logic control issues should be considered in the system design and cannot rely on the lock mechanism of the database system to solve the logical problem of your application system.
Read commandted mode
This is the default of SQL Server, and it is also the most commonly used. It is also a place to have used Oracle people feel uncomfortable.
EXAMPLE:
Session 1
Begin TRAN
INSERT INTO T1 VALUES (1, 'ALLAN')
Session 2
SELECT * FROM T1
Ok? What happened, I was hung. Oracle can not, I can't see 1, this record of 'Allan' is not good.
In fact, this is the difference between Oracle and SQL Server at this point. Oracle uses Rollback mechanism to ensure that the Locking of the READ COMMITTED mode does not affect other transactions (updates will be lived by LOCK). Therefore, Oracle provides stronger concurrency. Obviously, SQL Server simplifies this architecture, which can only be like this.
SQL Server In Read Committed mode, a query statement of a matter is not ignored by other transactions (if your query conditions include other transactions as submitted), SQL Server will let you wait for other commitments, so that Data consistency, obviously concurrently higher than oracle. If there is a waiting, everyone can determine according to this standard.
However, if the two transactions update a record or insert the same record, there will be a wait, SQL Server and Oracle are like this.
Then let me use the example to carefully explain:
The test form is as follows: Test form is as follows: C1 C2 C3 ------------------------- ------------- 1 200.5000 Hellen2 129.1400 Hellen3 288.9700 Allan
Session 1:
Begin TransactionDelete from test where c1 = 1
SESSION 2: Select * from test This is hidden because of the record of C1 = 1, SQL Server certainly asks you to wait.
If I don't choose C1 = 1 record, I will not be Waitting. Session3:
Select * from test where c1 = 2select * from test where c1 = 3 C1 C3 ------------------------------- -------------------- 2 129.1400 Hellen (the number of rows affected) C1 C3 ------------- ------------------------------------ 3 288.9700 Allan (the number of rows affected is 1 line ) Not hang, everything is fine. At this point, you can also find a very interesting, it is easy to confuse your phenomenon. Session 4Select * from test where c1 <> 1 results are also hung, as if RowLock has "problem"? Don't worry, I originally built a primary key (C1 field) because of this table TEST. I think this is due to Update, DELETE operation caused an index upstream LOCK. At this time, if you do SELECT * from test where c1> 1 is no problem. Then, we only need to force the part of the index page and the index node page (data page) in the indexed index (data page). Select * from test with where c1 <> 1, there is everything OK. Therefore, for many phenomena, we need further thinking and defecting.
Below, we look at the SP_LOCK.
View SP_LOCK View:
SPID DBID Objid Indid Type Resource Mode Status ------ ------ ---------------- ---- --------- ----------------------------------------------- ------- 53 7 789577851 1 PAG 1: 126 IX Grant53 7 789577851 1 Key (010086470766) x Grant53 7 789577851 1 PAG 1: 127 IX Grant53 7 789577851 2 Key (090041892960) x Grant53 7 789577851 0 Tab IX GRANT
(1) Id 789577851 is Table Test, you can query SysObjects.
(2) About TAB IX,
The intention of the table structure is locked. At this point, if you execute the alter table command to change the table structure (the X-lock on the table structure) is hamged.
(3) PAG is a page lock, which is the index page lock. Why is there two? Obviously 1: 126 is the intermediate page node page of the index tree, while 1: 127 is the leaf node page, that is, the data page (the table storage structure of the aggregation index). Therefore, any operation on the X lock on the index page will be hung, and the upper IX, S will not, and SQL Server will further determine the row lock. At this point, you can test it via Select * from test with WHERE C2 = 2.
(4) Key (010086470766), the two X X X X X X (090041892960) is the most obvious lock. One is a row-level lock on the intermediate page, one is a row-level lock on a leaf node (data page).
This is the case of the READ COMMITTED Isolation mode of SQL Server, and continue to discuss the Read Uncommitted Isolation mode next time.
