コミュニティ 学ぶ ツールライブラリ レジャー
検索
日本語
ホームページ > データベース > 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 プロジェクトがあり、古いプロジェクトのリポジトリ メソッドの MySQL ネイティブ クエリを SQLServer ネイ...
から 2024-04-03 14:46:55
0
1
351
LaravelでSQL Serverに接続できない LaravelでSQLサーバーに接続できません。 .env で SQLserver 構成値を指定しましたが、コマンド「phpartisanmaigrate」を実行するとまだエラーが...
から 2024-03-31 13:08:48
0
1
395
関連トピック
詳細>
人気のおすすめ
人気のチュートリアル
詳細>
関連するチュートリアル
人気のおすすめ
最新のコース
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート