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

Source: Internet
Author: User

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:

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.