Metadata lock in SQLSERVER

Metadata locks in SQLSERVER the metadata locks on the Internet are really very small, metadata locks will usually appear in the DDL statement below list the database engine can lock the Resource Description RID used to lock heap) A row of KEY is used to lock a row on the index, or an index key page is used to lock an 8 kb page in the database. For example

Metadata locks in SQLSERVER the metadata locks on the Internet are really very small, metadata locks will usually appear in the DDL statement below list the database engine can lock the Resource Description RID used to lock heap) A row of KEY is used to lock a row on the index, or an index key page is used to lock an 8 kb page in the database. For example

Metadata lock in SQLSERVER

There is really little information about metadata locks on the Internet.

Metadata lockAverageWill appear in the DDL statement

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

Resources	Description
RID	Used to lock a row in heap
KEY	Used to lock a row or an index key on an index
PAGE	Lock an 8 KB page in the database, such as the data page or index page
EXTENT	A group of 8 consecutive pages (zones)
HOBT	Lock the entire heap or B-tree
TABLE	Lock the entire table including all data and Indexes
FILE	Database Files
APPLICATION	Resources dedicated to applications
METADATA	Metadata lock
ALLOCATION_UNIT	Allocation Unit
DATABASE	Entire database

The purpose of locking metadata is the same as that of other locks, ensuring transaction consistency.

Lab environment: SQLSERVER2005 and SQLSERVER2012. If not specified, SQL statements are run on SQLSERVER2005.

For example, drop the ABC table in session 1.

 1 --session 1 2 USE [pratice] 3 GO 4 CREATE TABLE ABC(ID INT) 5 GO 6  7 -------------------------- 8 BEGIN TRAN 9 DROP TABLE ABC10 --COMMIT TRAN

Use in session 2Metadata FunctionsRead the objectid of the table ABC.

1  --session 22 USE [pratice]3 GO4 ---------------------------------------5 BEGIN TRAN6 SELECT OBJECT_ID('ABC')7 --COMMIT TRAN

At this time, you will see the metadata lock, otherwise there will be problems.

Let's take a look at the locks applied for when table ABC is dropped in session 1.

1 USE [pratice] 2 GO 3 set transaction isolation level repeatable read 4 GO 5 6 begin tran 7 drop table abc 8 9 -- COMMIT TRAN10 11 12 SELECT13 [request_session_id], 14 c. [program_name], 15 DB_NAME (c. [dbid]) AS dbname, 16 [resource_type], 17 [request_status], 18 [request_mode], 19 [resource_description], OBJECT_NAME (p. [object_id]) AS objectname, 20 p. [index_id] 21 FROM sys. [dm_tran_locks] AS a left join sys. [partitions] AS p22 ON. [resource_associated_entity_id] = p. [hobt_id] 23 left join sys. [sysprocesses] AS c ON. [request_session_id] = c. [spid] 24 WHERE c. [dbid] = DB_ID ('prate') AND. [request_session_id] = @ SPID ---- query the database applying for the lock 25 order by [request_session_id], [resource_type]

SQLSERVER locks some system tables, such as syshobts and sysallocunits, to update these system tables.

We can also see that SQLSERVER has added the schema lock to the metadata.

Architecture lock: The database engine uses schema modification during Table Data Definition Language (DDL) operations (such as adding columns or deleting tables ).(Sch-m) Lock

To prevent other users from accessing this table

Database engines use stable architecture during compilation and query execution(Sch-s) Lock(Stable), The sch-s lock does not prevent other transactions from accessing the data in the table. However,

It will prevent table modification DDL and DML operations.

These metadata should be in the resource Database

Resource Database: a read-only database that contains copies of all system objects attached to SQLSERVER. The resource database cannot be backed up and is invisible in SSMS.

Resource Database:SQL Server 2005 Resource database

A Resource database is a read-only database that contains all System Objects in SQL Server 2005.

SQL Server System objects (such as sys. objects) are physically stored in the Resource database,

But logically, they appear in the sys architecture of each database. The Resource database does not contain user data or user metadata.

Metadata locks are added when you query some system tables.

1 USE [pratice] 2 GO 3 set transaction isolation level repeatable read 4 GO 5 6 begin tran 7 select object_id from sys. tables where name = 'xxx' 8 9 -- COMMIT TRAN10 11 12 SELECT13 [request_session_id], 14 c. [program_name], 15 DB_NAME (c. [dbid]) AS dbname, 16 [resource_type], 17 [request_status], 18 [request_mode], 19 [resource_description], OBJECT_NAME (p. [object_id]) AS objectname, 20 p. [index_id] 21 FROM sys. [dm_tran_locks] AS a left join sys. [partitions] AS p22 ON. [resource_associated_entity_id] = p. [hobt_id] 23 left join sys. [sysprocesses] AS c ON. [request_session_id] = c. [spid] 24 WHERE c. [dbid] = DB_ID ('prate') AND. [request_session_id] = @ SPID ---- query the database applying for the lock 25 order by [request_session_id], [resource_type]

What I do not understand is that metadata locks are sometimes added during queries.

Table creation script:

1 USE [pratice] 2 GO 3 -- create table 4 create table ct1 (c1 INT, c2 INT, c3 VARCHAR (2000 )); 5 GO 6 -- create clustered index 7 create clustered index t1c1 ON ct1 (c1); 8 GO 9 10 -- create index nt1c1 ON ct1 (c2 ); 12 GO 13 14 15 -- insert test data 16 DECLARE @ a INT; 17 SELECT @ a = 1; 18 WHILE (@ a <= 1000) 19 BEGIN20 insert into ct1 VALUES (@ a, @ a, replicate ('A', 2000 )) 21 SELECT @ a = @ a + 122 END23 GO24 25 26 27 28 -- query data 29 SELECT * FROM ct1

View Code

View applied locks

1 USE [pratice] 2 GO 3 set transaction isolation level repeatable read 4 GO 5 6 begin tran 7 SELECT * FROM ct1 WHERE c1 = 50 8 9 -- COMMIT TRAN10 11 12 SELECT13 [request_session_id], 14 c. [program_name], 15 DB_NAME (c. [dbid]) AS dbname, 16 [resource_type], 17 [request_status], 18 [request_mode], 19 [resource_description], OBJECT_NAME (p. [object_id]) AS objectname, 20 p. [index_id] 21 FROM sys. [dm_tran_locks] AS a left join sys. [partitions] AS p22 ON. [resource_associated_entity_id] = p. [hobt_id] 23 left join sys. [sysprocesses] AS c ON. [request_session_id] = c. [spid] 24 WHERE c. [dbid] = DB_ID ('prate') AND. [request_session_id] = @ SPID ---- query the database applying for the lock 25 order by [request_session_id], [resource_type]

HoweverSQLSERVER2012Medium

Whether it is
BEGIN TRAN
Select object_id from sys. tables with (nolock) where name = 'xxx'
Or
BEGIN TRAN
SELECT * FROM ct1 WHERE c1 = 50

No metadata lock is available.

1 BEGIN TRAN2 select object_id from sys.tables with (nolock) where name = 'xxx' 

1 BEGIN TRAN2 select object_id from sys.tables with (nolock) where name = 'xxx' 

SQLSERVER2012 may have hidden the metadata lock. I think that even if the metadata lock is displayed, it does not make much sense to troubleshoot blocking. simply hide it.

However, this does not mean that SQLSERVER2012 has no metadata lock.

Metadata is a type of resource that can be locked. Metadata locks are not a type of lock !!!

Http://social.msdn.microsoft.com/Forums/zh-CN/10c07757-741d-4473-888c-174c9c91f038
Http://social.msdn.microsoft.com/Forums/zh-CN/c5c20bed-3fb7-414e-ade5-fb70c532cd84
Http://msdn.microsoft.com/zh-cn/library/ms187812 (v = SQL .105). aspx

If anything is wrong, you are welcome to make a brick o

Amendment

SQL Server also has the concept of ORACLE Data Dictionary. In fact, no matter which database has a data dictionary, it is called differently, while SQL Server's data dictionary is called metadata instead of data dictionary.

The innodb Engine table of mysql stores the data dictionary in the ibdata shared tablespace.

SQLSERVER stores data dictionaries in the master file group

ORACLE stores data dictionaries in the system tablespace.

The syshobts and sysallocunits tables in sqlserver start with sys and are all data dictionaries.

Data Dictionary Description: Describes data.

Records the table name, field name, index information, number of table records, and other related information of each table in the user database.

Therefore, when modifying table data, the corresponding data dictionary table will also be modified, so sqlserver will lock the metadata

In fact, no matter the data dictionary or metadata, it is actually a single table. We call it a basic system, and we generally cannot operate it. The database will use the System View

These system base tables are encapsulated and shielded, such as the sys. [syscolumns] view in sqlserver, the data dictionary view in oracle, such as the static data dictionary View starting with x $ and

Dynamic Data Dictionary View starting with v $ (v $ database)

Error in the article: The metadata should be in the resource Database

These metadata are stored in the user library. When creating a database, you first copy these system views and system base table structures from the resource database.

Copy some stored procedures, functions, and database parameters from the model database.ResourceDatabases andModelThe database actually acts as a template during creation.

These system views and system base tables both start with sys.

Because these system base tables are also modified when you modify table data, you can also see that sqlserver has applied for a metadata lock.

When the database is started, you do not have any operations, and then close the database. You can test the function by enabling sqlserver instance and disabling sqlserver instance.

You will see that the operation to modify the base table of the system will be recorded in the ldf file, even if you have not done any operation