SQL2008R2 a patch bug-when you use it please note

We all know SQL Server to increase concurrency, allow optimistic isolation levels (read-commit snapshots, snapshots) so that there is no blocking between read and write. Here's a hot patch in SQLSERVER2008R2 SP2 (CU11) RCSI (read commit snapshot) The exception under the isolation level. I hope you will pay attention.

Here I show you through examples.

Code test data (sql2008r2 SP2 cu11)

Create DatabaseTestbugSelect @ @VERSION --Microsoft SQL Server R2 (SP2)-10.50.4302.0 (Intel X86) UseTestbugGoCreate TableTestbug (IDint Identity(1,1)Primary Key, str1Char(Ten))Insert  intoTestbugSelect 'AA'Go 10000 ALTER DATABASETestbugSETRead_committed_snapshot on with rollbackImmediate-------------Modify the library isolation level to RCSI

Open Session 1, explicit transaction update

begin Tran TTT Update set str1='cc'

Opening session 2,select will be blocked.

Select *  from Testbug---will be blocked

Unexpectedly, not a good optimistic isolation level read and write do not block it??

Cause analysis

We look at the specific blocking situation from the DMV sys.dm_tran_locks 1-1

Select *  from

Figure 1-1

The original query operation requested is lock, here I can pass the trace flag 1200 authentication 1-2

DBCC TRACEON (3604,1)--------Open lock trace Output

Figure 1-2

In other database versions (here I tested SQL 2008R2 sp1,sp2,sql SP1) (you can generate data from the test script above)

The select operation actually applies for the lock Sch-s schema share lock, which is compatible with exclusive lock (X) 1-3

Figure 1-3

Lock-compatible instance 1-4 (lock compatibility table)

Figure 1-4

Note When the test is complete, we turn off the trace flag

DBCC Traceoff (3604,1)

It can be seen that the read-commit snapshot (RCSI) under the Sql2008r2 CU11 is blocked from reading and writing in the same granularity, which violates his intention to increase concurrency, so it should be attributed to an exception.

Impact Version: sql2008r2 SP2 under CU11 , cu12,cu13 Hot Patches

PostScript: The original has been raised this bug, Microsoft will be in the next version (SQL2008R2SP3?) This issue is resolved in

Https://connect.microsoft.com/SQLServer/feedback/details/914650/ Sql2008-r2-sp2-cu12-undermines-rcsi-by-making-select-statements-use-locks-instead-of-reading-row-versions

Conclusion: in the production environment, we may need patches to improve the efficiency and robustness of our database. But you need to ask yourself before patching, why patch? SQL Server patch is divided into "SP", "CU" two granularity, CU is a timely adjustment repair, and SP is a phased fill. My personal experience is that only SP patches (from this example can be seen, SP Patch is generally stable:)), special needs only to play CU.

SQL2008R2 a patch bug-when you use it please note