关于TX锁问题求助

cib_ct

Yong Huang 发表于 2016-9-30 00:15
I can only reproduce this problem with a distributed transaction. In the source database, v$lock for ...

感谢答复。
这几天找了些资料，看到maclean的一篇文章，如下几个场景均能产生仅有TX锁的情况。最奇怪的是select 远程对象也会在本地产生TX锁。不明白这个TX锁究竟锁的是什么资源。。。
1、事务中包括远程DML操作；
2、远程select也会在本地产生TX锁，且语句执行完毕后v$Lock中仍然显示TX锁，运行commit或rollback才能释放。
3、事务中使用savepoint；
4、使用dbms_transaction包的相关函数
5、事务中所涉及到的表启用了”disable table lock”或DML_LOCKS设置为0

zergduan

http://maclean.blog.51cto.com/2923249/1277590

Yong Huang

Among the 5 cases you listed above, number 5 doesn't apply to you. Correct? 1 and 2 are both called distributed transaction (a remote query creates a transaction). But since you said "未使用分布式事务", 1 and 2 may not apply to you either. Did you have savepoint or call dbms_transaction?

I can add one more case where this problem is reproduced:

SQL>  with x as (select /*+ materialize */ inst_id, sid, type, count(*) from gv$lock group by inst_id, sid, type) select * from x where type in ('TM','TX','DX') order by 1,2,3;

   INST_ID        SID TY   COUNT(*)
---------- ---------- -- ----------
         1        406 TM          1
         1        406 TX          1
         1        419 TM          3
         1        419 TX          1
         1        800 TX          1

SQL>  with x as (select /*+ materialize */ sid, type, count(*) from v$lock group by sid, type) select * from x where type in ('TM','TX','DX') order by 1,2;

       SID TY   COUNT(*)
---------- -- ----------
       406 TM          1
       406 TX          1
       419 TM          3
       419 TX          1
       800 TX          1

Sid 800 is my current session. During the execution of the SQL, it seems to hold a TX lock but not TM (nor DX) lock. The database is 11.2.0.3 single node. The materialize hint speeds up the query against gv$lock but not v$lock. If I remove this hint, the single TX lock (i.e. not accompanied with TM) disappears. I'm guessing that a TM lock on the global temporary table created on the fly may be briefly held during the execution and is not captured while the TX lock is, probably because it's held longer. We may be able to verify it with events such as 10704 (Print out information about what enqueues are being obtained). We know that a query against v$ views is not guaranteed read consistency.

But I doubt that's what you have in your database.

Yong Huang

Yet one more case is found. I analyze table compute statistics. While that's running, from another session, I find that the first session running analyze takes a TX lock in mode 6 (plus an AE lock which always exists), with no TM lock. It ends with the completion of analyze. This is mentioned in
ORA-2049 IN ANY DISTRIBUTED ENVIRONMENT (Doc ID 1029942.6)
for version 10.2. But I reproduced it in 11.2.0.3.

This doesn't happen with dbms_stats.gather_table_stats, which takes TX and also TM at the beginning, and no lock afterward.