Unable to lock the query

Environment: SQL Server or 2008

Recently, when dealing with a lock problem, found a more depressing thing, using X lock can not lock the query, simulate this problem, you can use the following T-SQL script to establish a test environment.

Use master;

Go

IF @ @TRANCOUNT > 0

ROLLBACK TRAN;

Go

-- =======================================

--Establish a test database

--A. Delete Test library if it already exists

IF db_id (N ' db_xlock_test ') is not NULL

BEGIN;

ALTER DATABASE Db_xlock_test

SET Single_user

With

ROLLBACK after 0;

DROP DATABASE db_xlock_test;

End;

--B. Establish test database

CREATE DATABASE db_xlock_test;

--C. Close read_committed_snapshot to keep the default lock mode of SELECT

ALTER DATABASE Db_xlock_test

SET read_committed_snapshot off;

Go

-- =======================================

--Set up a test table

Use Db_xlock_test;

Go

CREATE TABLE DBO.TB (

ID int IDENTITY

PRIMARY KEY,

Name sysname

);

INSERT DBO.TB

SELECT Top (50000)

O1.name + n '. ' + o2.name + n '. ' + o3.name

From sys.objects O1 with (NOLOCK),

Sys.objects O2 with (NOLOCK),

Sys.objects O3 with (NOLOCK);

Go

Then, establish a connection and execute the following script to implement the lock.

-- =======================================

--Test Connection 1-lock

BEGIN TRAN

--The original purpose of the test was to lock with a select, and found that update could not lock

UPDATE DBO.TB SET name = Name

--select COUNT (*) from DBO.TB with (Xlock)

WHERE ID <= 2;

SELECT

SPID = @ @SPID,

Tran_count = @ @TRANCOUNT,

database_name = db_name (),

object_id = object_id (n ' dbo.tb ', n ' Table ');

--Display lock

EXEC sp_lock @ @SPID;

By executing the result, you can see that the object is locked: The table level and the page level are IX locks, and the record is an X lock.

spid	tran_count	database_name	object_id
Wuyi	1	Db_xlock_test	21575115
spid	dbid	objid	indid	Type	resource	M