小豹子的网络记事本

记录每一个有意思的细节

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

一、锁

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

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

查询数据库中的锁
微信截图1.png
可以看到有三种类型的锁,其中AE我们不去管它。主要看TM和TX。

TM的ID1对应这个锁的对象的OBJECT_ID
微信截图2.png

TX的ID1和ID2通过转换对应v$transaction的XIDUSN、XIDSLOT、XIDSQN
微信截图3.png

微信截图4.png

当对一张表进行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锁,因为没有事务。
微信截图5.png

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

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

微信截图6.png

查询数据库中的锁,可以只看到TX锁,没有TM锁,而且有个事务与TX锁对应。
微信截图7.png

微信截图7.1.png

微信截图7.2.png

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

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

微信截图8.png
微信截图9.png

可以看到提交和回滚按钮并没有变亮,而且数据库中并没有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;  

微信截图10.png

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

微信截图11.png

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

三、分页

当我们用工具去查询一张大表的时候,工具会自动分页,主要是为了快速返回结果。那么之后对该表的操作会影响分页吗?
微信截图12.png
在另外一个窗口删除该表

SQL> drop table t1;  

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

如果是dblink去查的,又会是什么情况呢?
微信截图14.png
在dblink所指向的数据库端删除该表

SQL> drop table t1; 

回到前一个窗口,发现依然能往下翻页,跟普通的select现象一样。
微信截图15.png

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

那如果是一张本地的表和一张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;  

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

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

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

微信截图18.png

微信截图19.png
可以看到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)为什么会产生这种区别,前者不把远端的表全部拉过来,后者把本地的表全部推到远端去
posted @ 2021-11-13 18:07  小豹子加油  阅读(95)  评论(0编辑  收藏  举报