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
锁的资源在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