SQLSERVER中的元数据锁

SQLSERVER中的元数据锁

网上对于元数据锁的资料真的非常少

元数据锁一般会出现在DDL语句里

下面列出数据库引擎可以锁定的资源

资源	说明
RID	用于锁定堆（heap）中的某一行
KEY	用于锁定索引上的某一行，或者某个索引键
PAGE	锁定数据库中的一个8KB页，例如数据页或索引页
EXTENT	一组连续的8页（区）
HOBT	锁定整个堆或B树的锁
TABLE	锁定包括所有数据和索引的整个表
FILE	数据库文件
APPLICATION	应用程序专用的资源
METADATA	元数据锁
ALLOCATION_UNIT	分配单元
DATABASE	整个数据库

 

锁住元数据的目的跟其他的锁是一样的，都是保证事务的一致性

 

实验环境：SQLSERVER2005 ，SQLSERVER2012，如果没有特别说明的话，SQL语句都是在SQLSERVER2005上运行

 

例如，在会话一里drop掉ABC表

--session 1
USE [pratice]
GO
CREATE TABLE ABC(ID INT)
GO

--------------------------
BEGIN TRAN
DROP TABLE ABC
--COMMIT TRAN

 

在会话二里使用元数据函数读取ABC这张表的objectid

 --session 2
USE [pratice]
GO
---------------------------------------
BEGIN TRAN
SELECT OBJECT_ID('ABC')
--COMMIT TRAN

这时候就会看到元数据锁，否则就会出问题

我们看一下在session一里面当drop掉表ABC的时候申请了哪些锁

USE [pratice]
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRAN
DROP TABLE ABC

--COMMIT TRAN


SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

SQLSERVER会锁住一些系统表，例如：syshobts、sysallocunits等，以便对这些系统表进行更新

还有看到SQLSERVER在元数据上加了架构锁

架构锁：数据库引擎在表数据定义语言（DDL）操作（例如添加列或删除表）的过程中使用架构修改（sch-m）锁

以阻止其他用户对这个表格的访问

数据库引擎在编译和执行查询时使用架构稳定（sch-s）锁（稳定stable），sch-s锁不会阻止其他事务访问表格里的数据，但是，

会阻止对表格做修改性的DDL操作和DML操作

 

这些元数据应该是位于resource数据库中

resource数据库：包含SQLSERVER附带的所有系统对象副本的只读数据库，resource数据库是不能备份的，而且在SSMS里是看不见的

关于resource数据库：SQL Server 2005的Resource数据库

Resource 数据库是只读数据库，它包含了 SQL Server 2005 中的所有系统对象。

SQL Server 系统对象（例如 sys.objects）在物理上存在于 Resource 数据库中，

但在逻辑上，它们出现在每个数据库的 sys 架构中。Resource 数据库不包含用户数据或用户元数据。

---

 当查询某些系统表的时候也会加上元数据锁

USE [pratice]
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRAN
select object_id from sys.tables  where name = 'xxx' 

--COMMIT TRAN


SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

---

令本人不明白的是：在查询时，有时候也会加上元数据锁

建表脚本：

USE [pratice]
GO
--建表
CREATE TABLE ct1(c1 INT,c2 INT, c3 VARCHAR (2000));
GO
--建立聚集索引
CREATE CLUSTERED INDEX t1c1 ON ct1(c1);
GO
 
--建立非聚集索引
CREATE  INDEX nt1c1 ON ct1(c2);
GO 
 
 
--插入测试数据
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a <= 1000)
BEGIN
    INSERT INTO ct1 VALUES (@a,@a, replicate('a', 2000))
    SELECT @a = @a + 1
END
GO




--查询数据
SELECT * FROM ct1 

 

查看申请的锁

USE [pratice]
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRAN
SELECT * FROM ct1 WHERE c1=50

--COMMIT TRAN


SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

---

但是在SQLSERVER2012中

无论是
BEGIN TRAN
select object_id from sys.tables with (nolock) where name = 'xxx'
还是
BEGIN TRAN
SELECT * FROM ct1 WHERE c1=50

都看不到元数据锁了

 

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

 

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

 

可能SQLSERVER2012隐藏了元数据锁，觉得就算显示出元数据锁对于排查阻塞也没有多大意义，干脆隐藏算了

但是这里并不是说SQLSERVER2012没有了元数据锁

元数据是一种资源，可以锁定的资源，元数据锁并不是一种锁类型！！！

---

相关文章：

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
 

如有不对的地方，欢迎大家拍砖o(∩_∩)o

 

2014-8-9修正

SQLSERVER也有像ORACLE数据字典的概念，实际上无论哪一种数据库都有数据字典，只是叫法不同，而sqlserver的数据字典叫元数据而不叫数据字典

mysql 的innodb引擎表会把数据字典存放ibdata共享表空间里

SQLSERVER会把数据字典存放在主文件组

ORACLE会把数据字典存放在system表空间

 

sqlserver里面的syshobts、sysallocunits表都是带sys开头的，都是数据字典

数据字典含义：描述数据的数据

记录了用户数据库中的各个表的表名、字段名、索引信息、表记录数等等相关信息

 

所以修改表数据的时候也会修改相应的数据字典表，所以sqlserver就要锁元数据

实际上无论数据字典还是元数据，实际上就是一张张的表，我们叫系统基本，一般我们是不能操作的，数据库会利用系统视图来对

这些系统基表进行封装屏蔽，例如sqlserver里的sys.[syscolumns]视图，oracle里面的数据字典视图，例如以x$ 开头的静态数据字典视图和

v$开头的动态数据字典视图（v$database）

 

文章中的错误：这些元数据位于resource数据库中

这些元数据是各自存放在用户库里的，在创建数据库的时候先从resource数据库里把这些系统视图和系统基表结构从resource数据库里拷贝过来

再从model数据库里拷贝一些存储过程、函数和数据库参数，在这里resource数据库和model数据库其实就是创建时候充当模版的角色

而这些系统视图和系统基表都是以为sys开头的sys 架构

 

由于有时候修改表数据的时候也会顺带修改这些系统基表，所以大家也会看到sqlserver申请了元数据锁

当数据库启动的时候，你没有任何操作，然后关闭数据库，可以通过开启sqlserver实例和关闭sqlserver实例来测试

你会看到ldf文件里会记录修改系统基表的操作，即使你什么操作也没有做