一个诡异的SQL事务现象

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个诡异的SQL事务现象

今天测试过程中,同事提出了一个,看似诡异,实则很基础的问题,乍一看会被迷惑。

用实验来复现下这个问题,

(1) 创建测试表,A表的id字段是主键,B表的id_a字段是外键,参考A表的id主键,

一个诡异的SQL事务现象

(2) 应用有这么一个逻辑,一个事务中,先更新表A,再INSERT表B,其中表B的id_a字段值是来自于表A刚才操作的主键,模拟如下,

一个诡异的SQL事务现象

可以看出,更新表A的操作正常,但使用表A的主键值id=1,来INSERT表B的时候,报了FK_B_A外键完整性约束的错误。

明明A表有id=1的记录,并且更新UPDATE操作成功了,为什么用id_a=1来INSERT表B,提示了外键完整性约束错误,其含义就是无法从主表找出字表要INSERT的外键值id=1,两者相矛盾么?

此处为分割线,朋友们可以思考下,为什么会有这种问题?

使用log miner利器,挖掘下redo日志,发现这张表曾经做过rename操作,

一个诡异的SQL事务现象

此时检索下约束信息,表B的外键约束FK_B_A,即ID_A字段,参考引用的是约束PK_A,

一个诡异的SQL事务现象

记得没错的话,PK_A是表A的主键字段id,可实际上,约束PK_A是表A_BAK表的主键字段列id,

一个诡异的SQL事务现象

原因就是之前rename了表A为表A_BAK,虽然表名变了,但表上的约束名称未变,因此表B的外键参考的表名,从表A变为了表A_BAK。

如果此时删除A_BAK可以么?

一个诡异的SQL事务现象

报错的原因是因为有子表参考引用了这张表的唯一键/主键,和删除数据相同,必须从子表开始操作,关系干净了,然后才能操作主表。

一个诡异的SQL事务现象

啰嗦几句,这里使用了drop,其实11g下这些对象并为真正删除,而是放入了回收站,

一个诡异的SQL事务现象

可以看出,表B、表A_BAK以及表A_BAK的主键索引,这些对象名均被改写了。

约束名称也同时被改了,

一个诡异的SQL事务现象

不变的则是表的字段列,

一个诡异的SQL事务现象

如果不想存回收站了,直接删除,则可以使用purge属性。

总结:

  1. 凡是有主外键约束的表,无论删除数据还是删除表,均需要从子表开始,所有子表清理干净了,才能继续操作主表数据。
  1. 任何看似诡异的现象背后,都有他存在的原因,即哲学所说的“存在即合理”。

顺便打一个小广告了,之前写了一篇小文《》,有幸被中赫国安官方授权的公众号“MAX国安“选中,作为一期推文,欢迎各位点击、指教、分享,链接地址是:《》。

P.S. 点击量超过5000,稿费翻番。。。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

一个诡异的SQL事务现象
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个诡异的SQL事务现象


 上一篇
解决Logical Reads高的方法和实验 解决Logical Reads高的方法和实验
》,解决逻辑读过高的SQL语句,是优化方向。为了更直观地说明这个问题,通过模拟实验,来了解下。 创建测试表,test表三个字段,分别是id1,id2和name,insert入100万行记录,其中id1每个distinct值100次,id2针
下一篇 
生产数据导入测试环境碰见的一些问题 生产数据导入测试环境碰见的一些问题
我们某一个系统的夜维出现了性能的问题,删除N张表,数据量从几万到几百万不等,现在需要3.5-4个小时,看了一下SQL AWR,有些采用了TABLE ACCESS FULL,而且是数据量百万级的表,并且一次删除5000条,批量要删除几百次,相