Oracle 锁、dblink、分页一些巧妙的联系

一、锁

当对一张表进行dml操作时，数据库会加两种锁，TM（表级锁）和TX（事务锁）

SQL> select userenv('sid') from dual; // 34，当前会话的sid  
SQL> delete from test1;  

查询数据库中的锁

可以看到有三种类型的锁，其中AE我们不去管它。主要看TM和TX。

TM的ID1对应这个锁的对象的OBJECT_ID

TX的ID1和ID2通过转换对应v$transaction的XIDUSN、XIDSLOT、XIDSQN

当对一张表进行dml时，总会加上TM和TX锁，似乎TM和TX都是成对出现的，有没有哪种情况只有TM或者只有TX呢？
关闭前面的delete窗口，重新开启一个窗口，执行下面语句

SQL> select userenv('sid') from dual; // 21，当前会话的sid  
SQL> lock table test1 in share mode; 或  
SQL> lock table test1 in exclusive mode;  

查询数据库中的锁，可以看到只有TM锁，没有TX锁，因为没有事务。

再看另外一种情形，当我们用dblink去查询远端的一张表，可以看到plsql developer工具中的提交和回滚按钮变亮了。

SQL> select * from yang.t1@dl_test where rownum < 20;  

查询数据库中的锁，可以只看到TX锁，没有TM锁，而且有个事务与TX锁对应。

二、dblink

是否是所有的dblink查询都会有事务呢，点击rollback，我们把前面的语句改写成

SQL> select * from yang.t1@dl_test where rownum < 5;  

可以看到提交和回滚按钮并没有变亮，而且数据库中并没有TX锁，也就不会有事务。为什么返回条数多的有事务，而返回条数少的却没有呢？我猜想唯一的区别可能就是在plsql developer的分页这里。数据没有显示完全就有事务，数据已经显示完全了就没有事务。是不是这样呢？plsql developer会自动分页，而原生的sqlplus并不会，我们在sqlplus中执行以下sql语句，分别看看是否会产生事务

SQL> select userenv('sid') from dual; // 3878，当前会话的sid  
SQL> select * from yang.t1@dl_test where rownum < 20;  

SQL> rollback;  
SQL> select * from yang.t1@dl_test where rownum < 1;  

可以看到在sqlplus中执行带dblink的语句，无论返回多少条都会产生事务，而且我用datagrip去实验，得到的效果跟sqlplus一致。看来plsql developer通过dblink查数据，结果显示完全后就关闭事务是该工具特殊的地方。

三、分页

当我们用工具去查询一张大表的时候，工具会自动分页，主要是为了快速返回结果。那么之后对该表的操作会影响分页吗？

在另外一个窗口删除该表

SQL> drop table t1;  

回到前一个窗口，发现依然能往下翻页，也就是说当我执行select时，数据库已经在内存中提供好了结果集。后续对表的任何修改不影响返回结果。

如果是dblink去查的，又会是什么情况呢？

在dblink所指向的数据库端删除该表

SQL> drop table t1; 

回到前一个窗口，发现依然能往下翻页，跟普通的select现象一样。

那么通过dblink执行的结果集是在远端数据库还是已经拉到本地数据库来了呢？我将远端的数据库服务器的网络流量监控间隔调整为5s，发现我执行select语句的时候并没有出现流量突升，只有当我去点击显示剩下所有结果集的时候，流量才会突升。也就是结果集仍然是在远端，并没有拉到本地数据库中来。

那如果是一张本地的表和一张dblink的表进行关联，又是什么样子呢？
driving_site(a)表示在a表所在的数据库端执行sql，也就是本地

SQL> select /*+driving_site(a)*/ * from t1 a, yang.t1@dl_test b where a.object_id = b.object_id;  

可以看到，执行select语句并没有出现流量大的情况，只有点击显示剩下所有结果集的时候，流量才会突升。

换一种写法：到dblink所在的端执行sql

SQL> select /*+driving_site(b)*/ * from t1 a, yang.t1@dl_test b where a.object_id = b.object_id;  

可以看到incoming的流量瞬间升高达到20mbs，之后显示全部数据的时候，出口流量才会突升。也就是说select在执行的时候，就已经将本地的a表全部发送到了远端。

四、总结

1. TM表锁，TX事务锁，并不总是成对出现
2. 执行sql语句中带dblink产生TX锁，plsql developer工具会对已经完全显示了结果集的dblink事务进行特殊处理
3. 其它会话对工具的分页并不会产生影响
4. 本地表和远端表通过dblink进行关联的时候，在本地执行和在远端执行不一样，本地执行，远端表并不会一次性拉过来。而远端执行，本地表会一次性全部发送过去

还剩下几个没想明白的问题，期待高手指点

1. 如果表中有外键，对于该表和外键所指向的表的任何dml操作，都会在这两张表上面加TM锁
2. dblink为什么会产生事务
3. 上面例子中的driving_site(a)和driving_site(b)为什么会产生这种区别，前者不把远端的表全部拉过来，后者把本地的表全部推到远端去