DX锁的一些实验

DX锁即Distributed TX，只要通过DBLINK访问远程对象就会在远程数据库里产生DX锁，那怕仅仅是select。

假设有A库和B库，在A库中建立到B库的DBLINK，B库中有一张名为TEST的分区表，数据量2亿条。

1、在A库和B库中建立一张名为mylock的表，对系统中当前的锁做一个快照：
SQL> create table mylock as select sid, type, id1, lmode, request, block from v$lock;

2、在A库中通过DBLINK查询TEST表：
SQL> select count(*) from test@syslink;

3、去B库中查询新增的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
       2  minus
       3  select sid, type, id1, lmode, request, block from mylock;

       SID TY        ID1     LMODE    REQUEST      BLOCK
  ---------- -- ---------- ---------- ---------- ---------
       518 DX         24         4          0          0

库中增加了SID为518的session，lock type正好为DX。
session执行的语句为：
SQL> select sql_text from v$sqltext where sql_id=(select sql_id from v$session where sid=518);

SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM "TEST" "A1"

和在A库中执行的sql一致，看看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(select sql_id from v$session where sid=518));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  1zp6mqy4b5f8m, child number 0
-------------------------------------
SELECT COUNT(*) FROM "TEST" "A1"

Plan hash value: 1545934152

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |       |   110K(100)|          |       |       |
|   1 |  SORT AGGREGATE        |              |     1 |            |          |       |       |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
|   2 |   PARTITION RANGE ALL  |              |   192M|   110K  (1)| 00:22:05 |     1 |    62 |
|   3 |    INDEX FAST FULL SCAN| TEST_INDEX01 |   192M|   110K  (1)| 00:22:05 |     1 |    62 |
-----------------------------------------------------------------------------------------------

下面在B库中新开一个session，在A库的SELECT语句执行的同时执行DML语句，看看B库中的session会阻塞不：
SQL> select max(sid) from v$mystat;

  MAX(SID)
----------
       261

A库执行：
SQL> select count(*) from test@syslink;
由于表很大，这块会执行很久，可以有充分时间去执行其他sql

B库执行：
SQL> delete from test;

查询261会话的等待信息：
SQL> select sid, event, p1, p1text, wait_class from v$session_wait where sid=261;   
                                                                                             
       SID EVESS     NT                                         P1 P1TEXT            WAIT_CLA
---------- --------  ---------------------------------- ---------- ----------------- --------
       261 db        file sequential read                      107 file#             User I/O

可见261会话在运行，没有被阻塞
看看锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus                                                   
  3  select sid, type, id1, lmode, request, block from mylock;
                                                             
       SID TYPE        ID1      LMODE    REQUEST      BLOCK  
---------- ---- ---------- ---------- ---------- ----------  
       261 TM        26278          3          0          0  
       261 TM        26280          3          0          0  
       261 TM        26281          3          0          0  
       261 TX        25253          6          0          0  
       518 DX              24          4          0          0  

261会话没有被远程DBLINK查询所阻塞。

DDL语句的实验同样如此：
A库执行：
SQL> select count(*) from test@syslink;

B库执行：
SQL> alter table test add primary key(id, gcode) using index;

查询B库锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus                                                   
  3  select sid, type, id1, lmode, request, block from mylock;
                                                             
       SID TYPE        ID1      LMODE    REQUEST      BLOCK  
---------- ---- ---------- ---------- ---------- ----------  
       261 TM        26278          4          0          0  
       518 DX               24          4          0          0  

261会话仍然顺利执行。

那什么情况下会发生会话阻塞呢？继续做实验。

1、在A库中开两个session，session 150执行：
SQL> delete from test@syslink where rownum<10;

9 rows deleted.

2、查询A库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       150 TX       720931          6          0          0

查询B库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       461 TM       144945          3          0          0
       461 TX       393255          6          0          0

OBJECT_ID为144945的即为TEST对象：
SQL> select object_name from dba_objects where object_id=144945;

OBJECT_NAME
-----------------------------------------------------------------
TEST

3、在A库的另外一个session 123执行：
SQL> delete from test@syslink where rownum<10;
delete from sys.test@syslink where rownum<10
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from SYSLINK

Elapsed: 00:01:00.01

4、在第3步执行的时候查询A库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       123 DX                13          1          0          0
       123 TX       655442          6          0          0
       150 TX       720931          6          0          0

查询B库锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus                                                   
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       461 TM       144945          3          0          0
       461 TX       393239          6          0          1
       518 DX                58          6          0          0
       518 TM       144945          3          0          0
       518 TX       393239          0          6          0                    

B库中的session 518被阻塞，也即A库中的session 123被阻塞，  而且A库的session 123的delete阻塞正好一分钟，这是因为初始化参数 distributed_lock_timeout的值为60。

如果此时在B库中查询dba_pending_transactions，该查询会一直阻塞，查询等待事件为：
SQL> select sid, event, p1, p1text from v$session_wait where wait_class<>'Idle';

       SID EVENT                                                        P1 P1TEXT
---------- ---------------------------------------------------- ---------- ------------
       320 enq: DX - contention                                 1146617860 name|mode
       518 enq: TX - row lock contention                        1415053318 name|mode

查询B库中的锁信息：
SQL>  select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       320 DX           58          0          4          0
       461 TM       144945          3          0          0
       461 TX       393239          6          0          1
       518 DX           58          6          0          1
       518 TM       144945          3          0          0
       518 TX       393239          0          6          0

session 461阻塞了518，518又阻塞了320，320请求的正是DX锁，这两session的ID1值一样，不明白 查询dba_pending_transactions为什么需要DX锁。

最后假设如果A库通过DBLINK删除后，在B库中删除会怎样呢？
1、在A库中执行：
SQL> delete from test@syslink where rownum<10;

2、查询A库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       150 TX       720931          6          0          0

查 询B库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       518 TM       144945          3          0          0
       518 TX       393255          6          0          0

3、在B库中执行：
 SQL> delete from test  where rownum<10;             

4、查询A库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       150 TX       720931          6          0          0

没有变化。

查 询B库的锁信息：
SQL> select sid, type, id1, lmode, request, block from v$lock
  2  minus
  3  select sid, type, id1, lmode, request, block from mylock;

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       349 TM       144945          3          0          0
       349 TX       393228          0          6          0
       518 TM       144945          3          0          0
       518 TX       393228          6          0          1            

没有产生DX锁，因此B库中的session会一直阻塞。    

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

转载于:http://blog.itpub.net/339291/viewspace-670958/