lock mechanism for SQL Server (ii)--Overview (lock compatibility and resources that can be locked)

Second, the Complete lock compatibility matrix (see)

The right is the code description: see.

Third, the following table lists the resources that the database engine can lock.

Name	Resources	Abbreviation	Coding	How the resource is described when rendering a lock	Description
Data rows	RID	RID	9	File Number: Paging Number: Slot number	The row identifier used to lock a single row in the heap.
Index key	KEY	KEY	7	6-byte hash value	The row lock in the index that is used to protect the range of keys in a serializable transaction.
Pagination	PAGE	Pag	6	File Number: Pagination number	A 8-KB page in the database, such as a data page or index page.
Range	EXTENT	EXT	8	File Number: The number of the first page of a range	A contiguous set of eight pages, such as a data page or index page.
	HoBT				Heap or B-tree. A lock that is used to protect a B-tree (index) or heap data page in a table that does not have a clustered index.
Data Sheet	TABLE	TAB	5	Data table ID (objid field)	The entire table that includes all the data and indexes.
File	FILE	FIL	3	File number	Database files.
Application	Application	APP	10	6-byte hash value	Application-specific resources.
	METADATA				Meta data locks.
	Allocation_unit				Allocation unit.
Database	DATABASE	Db	2	Database code (dbid field)	The entire database.
Index		IDX	4	Other resources related to Db_id:object_id:index_id	The data row in the index is locked,

Iv. when SQL Server locks resources, the default is to lock from the bottom level, for example, index key values, data rows, to avoid large-scale locking, to avoid affecting other people to access other data in that range at the same time, but when memory is low, SQL Server automatically expands the locking range to reduce the overhead of managing locks. Let's look at an example below.

--Establish sp_lock output cache table if exists (SELECT * from tempdb. sysobjects where name like ' #temp% ' and type = ' u ') begindrop table #tempcreate table #temp (spid int,dbid int, ObjID int,ind ID int,type varchar (3), resource varchar (), mode varchar, status varchar (5)) Endbegin tranupdate wbk_pde_head Set [ cop_ems_no]= ' ABCDE ' where wbook_no= ' BE404942850177 ' insert #temp exec sp_lock @ @spidcommit tran-----Get dbid--select db_ ID (' Test ')--view only resources related to a custom database, SQL 2008select spid, database =db_name (dbid), Object =object_name (objid), index = (select NAME from sysindexes where Id=objid and Indid=t.indid), type,resource,mode,status from #temp t where Dbid=28order by Dbid,objid,indi d------Query the relevant data with the Sys.indexes table in SQL 2005 select SPID, Database =db_name (dbid), Object =object_name (objid), index = (select NAME from sys.indexes where Object_id=objid and Index_id=t.indid), type,resource,mode,status from #temp t where Dbid=28order by dbid , Objid,indid

Description

1. Create a temporary table #temp the data that is used to store the sp_lock output of the system stored procedure

2. Turn on the transaction and update the data (update), but do not confirm the transaction, the database locks the related object, inserts the related data that sp_lock renders into the #temp table, and queries the results.

Execute the following code in Query Analyzer

Select A.*,b.name from #temp a LEFT join sysobjects B in a.objid=b.id order by A.type

The figure is shown below:

lock mechanism for SQL Server (ii)--Overview (lock compatibility and resources that can be locked)