ITPub博客

首页 > 数据库 > Oracle > PCM锁与BLOCK资源

PCM锁与BLOCK资源

原创 Oracle 作者:westzq1984 时间:2013-11-30 10:42:17 0 删除 编辑
RAC上,对于buffer cache的全局锁,称为PCM锁,当然,对于enq,lc的锁,称为non-PCM锁
锁的资源在GRD(旧称DLM)中维护,PCM通过GCS在GRD中维护

v$dlm_ress中,记录着GRD中的所有资源,对于PCM锁,其名称为[ID1][ID2][BL],第三位BL是固定的,代表buffer locks的意思
v$lock_element中,记录了当前的锁对象,锁对象用于对资源加锁。
这两个视图中,包含了PCM锁和non-PCM锁的所有信息

例如,查看资源[0x7870][0x30000],[BL]对应的数据块

SQL> SELECT * FROM GV$DLM_RESS WHERE resource_name  LIKE '%7870%[BL]%';

   INST_ID RESP     RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q PERSISTENT_RES MASTER_NODE NEXT_CVT_ VALUE_BLK_STATE
---------- -------- ------------------------------ ------------ ---------- -------------- ----------- --------- --------------------------------
VALUE_BLK
----------------------------------------------------------------
         1 52CE1F84 [0x7870][0x30000],[BL]                    0          1              1           0 KJUSERNL  KJUSERVS_NOVALUE
0x0

资源名[0x7870][0x30000],[BL],对于非UNDO块的PCM锁
第一位[0x7870]为lock_element_name,其是根据block number(BNO)和absolute file ID (AFN)计算出来的 => BNO bit-or’ed ( AFN << 22)
第二位[0x30000]为(AFN>>10)<<15
第三位[BL],对于PCM锁,其总为BL

对于UNDO块
id1 = ( BNO / _kcl_undo_grouping) % _kcl_undo_locks
id2 = block class

对于这个块,应该是由那个LMS进程处理,是通过如下公式计算 (id1+id2)%(number_of_LMS_procs)

SQL> SELECT * FROM gv$lock_element WHERE LOCK_ELEMENT_NAME = 30832;

   INST_ID LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID       FLAGS
---------- -------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
         2 23FF8484          3     196608             30832          2           1          0          0          0          65

LOCK_ELEMENT_NAME = 30832 = 0x7870,资源名的第一位
CLASS = 196608 = 0x30000,资源名的第二位
LOCK_ELEMENT_ADDR = 23FF8484,锁的地址

SQL> SELECT * FROM gv$bh WHERE lock_element_addr='23FF8484';

   INST_ID      FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N        OBJD        TS#
---------- ---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         2          3      30832         14 xcur             0            0             0 23FF8484             30832              196608 N N N N N N       4226          2

该锁保护的数据块为3,30832,object_id为4226
该数据块的master节点(锁的master节点)为节点1,但是该数据的当前块在节点2上

从gv$lock_element的BLOCK_COUNT列可以看到,一个锁其实可能覆盖多个数据块

PCM锁的一点小测试

SQL> desc ctais2.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,'CTAIS2','TEST') file_id,dbms_rowid.rowid_block_number(rowid) block_id from ctais2.test;

   FILE_ID   BLOCK_ID
---------- ----------
         4         67
         4         67
         4         67
         4         67
         4         67
         4         67
         4         67
         4         67
         4         67
         4         67

10 rows selected.

两个实例都重启
本库数据文件少,一般ID1就是block_id,所以只写了个简单的SQL

--1-- file4block67 的资源,没有在节点1的GRD中
SQL> @a
Enter value for 1: 67
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

no rows selected

old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

no rows selected

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

no rows selected


--2-- file4block67 的资源,也没有在节点1的GRD中,因为其还没有读入过buffer cache中
SQL> @a
Enter value for 1: 67
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

no rows selected

old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

no rows selected

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

no rows selected

--1-- 节点1查询表后,可以看到在实例1上,已经有了这个对象的lock_element,其以模式2(X)持有对象.
SQL> select count(*) from ctais2.test;

  COUNT(*)
----------
        10

SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

no rows selected

old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BF3154       1143     262144                67          2           1          0          0          0         65

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 xcur             0            0             0 21BF3154                67             262144 Y N N N N N      51837           4
         
--2-- 节点1查询表后,在节点2的v$dlm_ress可以看到该块的锁资源。说明资源信息只在master节点上。grant队列就1个进程        
SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

RESP     RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q PERSISTENT_RES MASTER_NODE NEXT_CVT_ VALUE_BLK_STATE
-------- ------------------------------ ------------ ---------- -------------- ----------- --------- --------------------------------
VALUE_BLK
----------------------------------------------------------------
52D1E428 [0x43][0x40000],[BL]                      0          1              1           1 KJUSERNL  KJUSERVS_NOVALUE
0x0


old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

no rows selected

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

no rows selected

--2-- 节点2也查询这个表,可以看到节点2上也有一个lock_element保护这个块,锁的级别为1,块的状态降为了scur
SQL>  select count(*) from ctais2.test;

  COUNT(*)
----------
        10

SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

RESP     RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q PERSISTENT_RES MASTER_NODE NEXT_CVT_ VALUE_BLK_STATE
-------- ------------------------------ ------------ ---------- -------------- ----------- --------- --------------------------------
VALUE_BLK
----------------------------------------------------------------
52D1F310 [0x43][0x40000],[BL]                      0          1              1           1 KJUSERNL  KJUSERVS_NOVALUE
0x0


old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BED954       1108     262144                67          1           1          0          0          0         65

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 scur             0            0             0 21BED954                67             262144 N N N N N N      51837           4


--1-- 在节点2也查询这个表后,可以看到节点1上,锁的模式变为1,这个块的状态也降级为scur

SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

no rows selected

old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BEF4D4       1131     262144                67          1           1          0          0          0         65

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 scur             0            0             0 21BEF4D4                67             262144 N N N N N N      51837           4


--1-- 在节点1上更新一条记录,节点1重新以模式2获得块的锁,块的状态变为xcur
SQL> update ctais2.test set id=99 where id=1;

1 row updated.

SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

no rows selected

old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BEF4D4       1131     262144                67          2           1          0          0          0         65

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 xcur             0            0             0 21BEF4D4                67             262144 Y N N N N N      51837           4
         
--2-- 在节点1上更新一条记录后,节点2上的块变为cr块,在锁上的模式为null
SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

RESP     RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q PERSISTENT_RES MASTER_NODE NEXT_CVT_ VALUE_BLK_STATE
-------- ------------------------------ ------------ ---------- -------------- ----------- --------- --------------------------------
VALUE_BLK
----------------------------------------------------------------
52D1F310 [0x43][0x40000],[BL]                      0          1              1           1 KJUSERNL  KJUSERVS_NOVALUE
0x0


old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BED954       1108     262144                67          0           0          0          0          0         67

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

no rows selected

SQL> select * from v$bh where file#=4 and block#=67;

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 cr               0            0             0 00                                    N N N Y N N      51837           4

--2-- 节点2也更新一条数据,节点2重新以模式2持有块的xcur        
SQL> update ctais2.test set id=999 where id=2;

1 row updated.

SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

RESP     RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q PERSISTENT_RES MASTER_NODE NEXT_CVT_ VALUE_BLK_STATE
-------- ------------------------------ ------------ ---------- -------------- ----------- --------- --------------------------------
VALUE_BLK
----------------------------------------------------------------
52D1F310 [0x43][0x40000],[BL]                      0          1              1           1 KJUSERNL  KJUSERVS_NOVALUE
0x0


old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BED954       1905     262144                67          2           1          0          0          0         65

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 xcur             0            0             0 21BED954                67             262144 Y N N N N N      51837           4
         
--1-- 在节点2也更新一条数据后,节点1的锁降级为null,块变为pi块
SQL> @a.sql
old   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(&&1,'xxxx'))||']%BL%'
new   1: select * from v$dlm_ress where resource_name like '%[0x'||ltrim(to_char(67,'xxxx'))||']%BL%'

no rows selected

old   1: select * from v$lock_element where LOCK_ELEMENT_NAME=&1
new   1: select * from v$lock_element where LOCK_ELEMENT_NAME=67

LOCK_ELE       INDX      CLASS LOCK_ELEMENT_NAME  MODE_HELD BLOCK_COUNT  RELEASING  ACQUIRING    INVALID      FLAGS
-------- ---------- ---------- ----------------- ---------- ----------- ---------- ---------- ---------- ----------
21BEF4D4       1131     262144                67          0           1          0          0          0         65

old   1: select * from v$bh where LOCK_ELEMENT_NAME=&1
new   1: select * from v$bh where LOCK_ELEMENT_NAME=67

     FILE#     BLOCK#     CLASS# STATUS         XNC FORCED_READS FORCED_WRITES LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD         TS#
---------- ---------- ---------- ------- ---------- ------------ ------------- -------- ----------------- ------------------ - - - - - - ---------- ----------
         4         67          1 pi               0            0             0 21BEF4D4                67             262144 Y N N N N N      51837           4                

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-1061710/,如需转载,请注明出处,否则将追究法律责任。

上一篇: asmlib
下一篇: Quorum FailGroup
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    961995