社群 學習 工具庫 休閒
搜尋
繁体中文
首頁 > 資料庫 > mysql教程 > SQLSERVER中的元数据锁

SQLSERVER中的元数据锁

WBOY
發布： 2016-06-07 15:45:23
原創
1310 人瀏覽過

SQLSERVER中的元数据锁 网上对于元数据锁的资料真的非常少 元数据锁 一般 会出现在DDL语句里 下面列出数据库引擎可以锁定的资源 资源 说明 RID 用于锁定堆（heap）中的某一行 KEY 用于锁定索引上的某一行，或者某个索引键 PAGE 锁定数据库中的一个8KB页，例

SQLSERVER中的元数据锁

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

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

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

资源 

说明

RID

用于锁定堆（heap）中的某一行

KEY

用于锁定索引上的某一行，或者某个索引键

PAGE 

锁定数据库中的一个8KB页，例如数据页或索引页

EXTENT

一组连续的8页（区）

HOBT

锁定整个堆或B树的锁

TABLE 

锁定包括所有数据和索引的整个表

FILE

数据库文件

APPLICATION 

应用程序专用的资源

METADATA   

元数据锁

ALLOCATION_UNIT  

分配单元

DATABASE

整个数据库

 

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

 

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

 

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

<span> 1</span> <span>--</span><span>session 1</span>
<span> 2</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 3</span> <span>GO</span>
<span> 4</span> <span>CREATE</span> <span>TABLE</span> ABC(ID <span>INT</span><span>)
</span><span> 5</span> <span>GO</span>
<span> 6</span> 
<span> 7</span> <span>--</span><span>------------------------</span>
<span> 8</span> <span>BEGIN</span> <span>TRAN</span>
<span> 9</span> <span>DROP</span> <span>TABLE</span><span> ABC
</span><span>10</span> <span>--</span><span>COMMIT TRAN</span>
登入後複製

 

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

<span>1</span>  <span>--</span><span>session 2</span>
<span>2</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span>3</span> <span>GO</span>
<span>4</span> <span>--</span><span>-------------------------------------</span>
<span>5</span> <span>BEGIN</span> <span>TRAN</span>
<span>6</span> <span>SELECT</span> <span>OBJECT_ID</span>(<span>'</span><span>ABC</span><span>'</span><span>)
</span><span>7</span> <span>--</span><span>COMMIT TRAN</span>
登入後複製

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

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

<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span>
<span> 4</span> <span>GO</span>
<span> 5</span> 
<span> 6</span> <span>BEGIN</span> <span>TRAN</span>
<span> 7</span> <span>DROP</span> <span>TABLE</span><span> ABC
</span><span> 8</span> 
<span> 9</span> <span>--</span><span>COMMIT TRAN</span>
<span>10</span> 
<span>11</span> 
<span>12</span> <span>SELECT</span>
<span>13</span> <span>[</span><span>request_session_id</span><span>]</span><span>,
</span><span>14</span> c.<span>[</span><span>program_name</span><span>]</span><span>,
</span><span>15</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname,
</span><span>16</span> <span>[</span><span>resource_type</span><span>]</span><span>,
</span><span>17</span> <span>[</span><span>request_status</span><span>]</span><span>,
</span><span>18</span> <span>[</span><span>request_mode</span><span>]</span><span>,
</span><span>19</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname,
</span><span>20</span> p.<span>[</span><span>index_id</span><span>]</span>
<span>21</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p
</span><span>22</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span>
<span>23</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span>
<span>24</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span>  <span>--</span><span>--要查询申请锁的数据库</span>
<span>25</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>
登入後複製

SQLSERVER中的元数据锁

SQLSERVER中的元数据锁

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 数据库不包含用户数据或用户元数据。

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

<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span>
<span> 4</span> <span>GO</span>
<span> 5</span> 
<span> 6</span> <span>BEGIN</span> <span>TRAN</span>
<span> 7</span> <span>select</span> <span>object_id</span> <span>from</span> sys.tables  <span>where</span> name <span>=</span> <span>'</span><span>xxx</span><span>'</span> 
<span> 8</span> 
<span> 9</span> <span>--</span><span>COMMIT TRAN</span>
<span>10</span> 
<span>11</span> 
<span>12</span> <span>SELECT</span>
<span>13</span> <span>[</span><span>request_session_id</span><span>]</span><span>,
</span><span>14</span> c.<span>[</span><span>program_name</span><span>]</span><span>,
</span><span>15</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname,
</span><span>16</span> <span>[</span><span>resource_type</span><span>]</span><span>,
</span><span>17</span> <span>[</span><span>request_status</span><span>]</span><span>,
</span><span>18</span> <span>[</span><span>request_mode</span><span>]</span><span>,
</span><span>19</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname,
</span><span>20</span> p.<span>[</span><span>index_id</span><span>]</span>
<span>21</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p
</span><span>22</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span>
<span>23</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span>
<span>24</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span>  <span>--</span><span>--要查询申请锁的数据库</span>
<span>25</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>
登入後複製

SQLSERVER中的元数据锁

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

建表脚本：

SQLSERVER中的元数据锁SQLSERVER中的元数据锁 

<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>--</span><span>建表</span>
<span> 4</span> <span>CREATE</span> <span>TABLE</span> ct1(c1 <span>INT</span>,c2 <span>INT</span>, c3 <span>VARCHAR</span> (<span>2000</span><span>));
</span><span> 5</span> <span>GO</span>
<span> 6</span> <span>--</span><span>建立聚集索引</span>
<span> 7</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> t1c1 <span>ON</span><span> ct1(c1);
</span><span> 8</span> <span>GO</span>
<span> 9</span>  
<span>10</span> <span>--</span><span>建立非聚集索引</span>
<span>11</span> <span>CREATE</span>  <span>INDEX</span> nt1c1 <span>ON</span><span> ct1(c2);
</span><span>12</span> <span>GO</span> 
<span>13</span>  
<span>14</span>  
<span>15</span> <span>--</span><span>插入测试数据</span>
<span>16</span> <span>DECLARE</span> <span>@a</span> <span>INT</span><span>;
</span><span>17</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>1</span><span>;
</span><span>18</span> <span>WHILE</span> (<span>@a</span> <span> <span>1000</span><span>)
</span><span>19</span> <span>BEGIN</span>
<span>20</span>     <span>INSERT</span> <span>INTO</span> ct1 <span>VALUES</span> (<span>@a</span>,<span>@a</span>, <span>replicate</span>(<span>'</span><span>a</span><span>'</span>, <span>2000</span><span>))
</span><span>21</span>     <span>SELECT</span> <span>@a</span> <span>=</span> <span>@a</span> <span>+</span> <span>1</span>
<span>22</span> <span>END</span>
<span>23</span> <span>GO</span>
<span>24</span> 
<span>25</span> 
<span>26</span> 
<span>27</span> 
<span>28</span> <span>--</span><span>查询数据</span>
<span>29</span> <span>SELECT</span> <span>*</span> <span>FROM</span> ct1 </span>
登入後複製
View Code

 

查看申请的锁

<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span>
<span> 4</span> <span>GO</span>
<span> 5</span> 
<span> 6</span> <span>BEGIN</span> <span>TRAN</span>
<span> 7</span> <span>SELECT</span> <span>*</span> <span>FROM</span> ct1 <span>WHERE</span> c1<span>=</span><span>50</span>
<span> 8</span> 
<span> 9</span> <span>--</span><span>COMMIT TRAN</span>
<span>10</span> 
<span>11</span> 
<span>12</span> <span>SELECT</span>
<span>13</span> <span>[</span><span>request_session_id</span><span>]</span><span>,
</span><span>14</span> c.<span>[</span><span>program_name</span><span>]</span><span>,
</span><span>15</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname,
</span><span>16</span> <span>[</span><span>resource_type</span><span>]</span><span>,
</span><span>17</span> <span>[</span><span>request_status</span><span>]</span><span>,
</span><span>18</span> <span>[</span><span>request_mode</span><span>]</span><span>,
</span><span>19</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname,
</span><span>20</span> p.<span>[</span><span>index_id</span><span>]</span>
<span>21</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p
</span><span>22</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span>
<span>23</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span>
<span>24</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span>  <span>--</span><span>--要查询申请锁的数据库</span>
<span>25</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>
登入後複製

SQLSERVER中的元数据锁

但是在SQLSERVER2012中

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

都看不到元数据锁了

 

<span>1</span> <span>BEGIN</span> <span>TRAN</span>
<span>2</span> <span>select</span> <span>object_id</span> <span>from</span> sys.tables <span>with</span> (nolock) <span>where</span> name <span>=</span> <span>'</span><span>xxx</span><span>'</span>
登入後複製
登入後複製

 

SQLSERVER中的元数据锁

<span>1</span> <span>BEGIN</span> <span>TRAN</span>
<span>2</span> <span>select</span> <span>object_id</span> <span>from</span> sys.tables <span>with</span> (nolock) <span>where</span> name <span>=</span> <span>'</span><span>xxx</span><span>'</span>
登入後複製
登入後複製

 

SQLSERVER中的元数据锁

可能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文件里会记录修改系统基表的操作，即使你什么操作也没有做

 

相關標籤：
sqlserver 數位 數據
來源：php.cn
上一篇：Oracle的data block内容研读(转) 下一篇：SQLSERVER中的逻辑读与物理读
本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
作者最新文章
最新問題
SQL Server：使用帶有 WHERE 子句的 CASE WHEN 表達式 我有一個有SQLServer的新SpringBoot項目，我需要用SQLServer本機查詢取代舊項目中儲存庫方法上的MySQL本機查詢。這是一個複雜的查詢，其中條件為casewh...
來自於 2024-04-03 14:46:55
0
1
351
Laravel 中無法連線 SQL Server 我無法在Laravel中連接sql-server。我已經在.env中給了sqlserver配置值，但在運行命令“phpartisanmigrate”時仍然低於錯誤。 SQLSTAT...
來自於 2024-03-31 13:08:48
0
1
395
相關專題
更多>
熱門推薦
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板