修改表字段长度的操作,对业务是否有影响?

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

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

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

原文链接:blog.ouyangsihai.cn >> 修改表字段长度的操作,对业务是否有影响?

前两天测试同学问了一个问题,表中某一个字段,需要改一下长度,对业务是否会有影响?

可能隐约之中,我们觉得没影响,但又好像有影响,究竟有何影响,我们从实验来看最科学。

首先建测试表,NAME字段是VARCHAR2(10),10个字节的字符串类型,表有256万数据。我们将其长度改为20,从执行时间看,只有20毫秒,

修改表字段长度的操作,对业务是否有影响?

我们对上面的操作,做一下10046 trace,发现确实,首先使用LOCK以EXCLUSIVE模式锁定了TBL表,

修改表字段长度的操作,对业务是否有影响?

接下来执行alter table修改操作,

修改表字段长度的操作,对业务是否有影响?

从trace文件看,主要是针对一些数据字典表的操作,其中包含28次select,10次update,12次delete,可以想象一个改字段长度的操作,就有几十次SQL操作,但用时仅为毫秒级,效率可见一斑。

我们从的介绍,可以知道,EXCLUESIVE模式的锁,是最高级别的锁,Alter table,Drop table,Drop index,Truncate table这些常见的DDL操作,都会需要这种级别的锁,我们知道Oracle中select这种查询(不带for update)是不会有锁的,因此若表有EXCLUSIVE级别的锁时,仅允许select操作(不带for update),禁止其他类型的操作,

从锁的强弱看,EXCLUSIVE(exclusive,X)SHARE ROW EXCLUSIVE(S/Row-X,SRX)SHARE(Share,S)ROW EXCLUSIVE(Row-X,RX)ROW SHARE(Row-S,RS)。

最后,引述一篇博客的总结(http://blog.itpub.net/9252210/viewspace-626388/), 2级锁Row-S行共享(RS):共享表锁,sub share,锁有:Select for update,Lock For Update,Lock Row Share。 3级锁Row-X行独占(RX):用于行的修改,sub exclusive,锁有:Insert, Update, Delete, Lock Row Exclusive。 4级锁Share共享锁(S):阻止其他DML操作,share,锁有:Create Index, Lock Share,locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会hang。 5级锁S/Row-X共享行独占(SRX):阻止其他事务操作,share/sub exclusive,锁有:Lock Share Row Exclusive,具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。 6级锁exclusive 独占(X):独立访问使用,exclusive,锁有:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive。

因此,针对上面VARCHAR2(10)改为VARCHAR2(20)的操作,我们的结论是修改字段长度的操作是会阻碍其他非select操作,但是持续的时间很有限,几乎可以说是忽略不计,因为需要操作的是数据字典信息,并不是表自身,所以和要操作表的记录总量,没有任何关系。

无意之中,发现了另一个问题,将字段长度从VARCHAR2(20)改为VARCHAR2(10),用时比之前要久,540毫秒,几乎是之前的10倍,

修改表字段长度的操作,对业务是否有影响?

我们看下他的trace,首先还是以EXCLUSIVE模式锁表,

修改表字段长度的操作,对业务是否有影响?

接着执行alter table操作,

修改表字段长度的操作,对业务是否有影响?

我们从下面的信息,看出了一些端倪,

修改表字段长度的操作,对业务是否有影响?

以FIRST_ROWS优化器模式执行select操作,条件是字段NAME长度10,因为现在是要将字段长度,从20改为10,就需要判断是否已存数据中,有违反长度的记录,如果有则禁止此操作,所以需要以全表扫描,来检索表中所有记录,rows是0,则继续执行其他操作,需要注意的是,他采用了FIRST_ROWS模式,会以最快的速度返回记录,因此执行时间还是可控的,从操作上来看,整个操作包含27次select,10次update,12次delete,其中判断LENGTH(“NAME”)10的语句占用了几乎90%的SQL执行时间。

总结:

  1. 若是增加长度的操作,会以EXCLUSIVE模式锁表,但其主要操作的是数据字典表,锁占用时间几乎可以忽略不计,所以几乎不会影响业务。
  1. 若是缩短长度的操作,还会以EXCLUSIVE模式锁表,但需要以FIRST_ROWS优化器模式,执行全表扫描,判断已存数据是否有超长的记录,因此相比(1)执行时间会略久,但基本可控。

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

修改表字段长度的操作,对业务是否有影响?
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

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

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

原文链接:blog.ouyangsihai.cn >> 修改表字段长度的操作,对业务是否有影响?


 上一篇
alter table新增字段操作究竟有何影响?(下篇) alter table新增字段操作究竟有何影响?(下篇)
本文的上篇文章可参考: 作为本篇的内容,主要包含两个方面: 话题1:不同锁模式的实验接着上篇博文的话题,针对几种常见的锁模式通过实验感受下之间的不同。 话题2:不同方式新增字段的效率最近有个系统执行新增字段的操作出现了hang,借此使
下一篇 
数据迁移中碰见的一些问题 数据迁移中碰见的一些问题
单位有一套Oracle 9i的古老测试数据库,因为机房搬迁,所以需要迁移数据,新库是Oracle 11g了,一个比较简单的需求,但过程中碰见了一些问题,看似比较琐碎,值得总结一下。 由于源库是9i,因此只能用imp/exp,不能用数据泵。