SQL server lock mechanism (II)-Overview (Lock compatibility and lockable resources)-SQL server lock mechanism (I)-Overview (lock type and scope) SQL server lock mechanism (I)-Overview (lock type and scope)

Connect to the SQL server lock mechanism (I)-Overview (lock type and scope)

 

 

 

Ii. Complete lock compatibility matrix (see)

 

Correct Code Description: see.

 

III. The following table lists the resources that can be locked by the database engine.

Name	Resources	Abbreviations	Encoding	Specifies the method to describe the resource when a lock is presented.	Description
Data row	RID	RID	9	File No.: Page No.: Slot No.	The row identifier used to lock a single row in the heap.
Index key	KEY	KEY	7	6-byte Hash Value	The index is used to protect the row locks for key ranges in serializable transactions.
Paging	PAGE	PAG	6	File No.: Page No.	8 KB pages in the database, such as data pages or index pages.
Range	EXTENT	EXT	8	File number: Number of the first page in the range	A group of eight consecutive pages, such as data pages or index pages.
	HoBT				Heap or B-tree. Used to protect the locks of the B-tree (INDEX) or heap data pages in a table without clustered indexes.
Data Table	TABLE	TAB	5	Data Table ID (OBJID field)	The entire table that includes all data and indexes.
File	FILE	FIL	3	File number	Database files.
Applications	APPLICATION	APP	10	6-byte Hash Value	Resources dedicated to applications.
	METADATA				Metadata lock.
	ALLOCATION_UNIT				Allocation unit.
Database	DATABASE	DB	2	Database code (DBID field)	The entire database.
Index		IDX	4	Db_id: object_id: other resources related to index_id	The data row in the index is locked,

 

 

 

 

4. When SQL SERVER wants to lock resources, it will start from the bottom level by default, for example, index key value, data row, to avoid large-scale locking, to avoid affecting other people's access to other data within this range at the same time, but when the memory is insufficient, SQL SERVER will automatically expand the lock range to reduce the load of Management locks. Here is an example.

 

-- Create the 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, indid int, type varchar (3), resource varchar (20), mode varchar (20), 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 ') -- only view the related resources of the custom database, SQL 2008 select 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 = 28 order by dbid, objid, indid ------ use SQL 2005 sys. 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 = 28 order by dbid, objid, indid

 

Note:

1. Create a temporary table # Temp is used to store the data output by sp_lock in the system Stored Procedure

2. start the transaction, and then update the data (update), but do not confirm the transaction, the database will lock the relevant objects, insert the data presented by sp_lock into the # Temp table, and query the results.

 

Run the following code in the query Analyzer:

 

select a.*,b.name from #temp a left join sysobjects b on a.objid=b.id order by a.type

 

The figure below shows:

 

 

For other examples, see "Sample Code 2" in SQL server lock mechanism (I)-Overview (lock type and scope.