alter table新增字段操作究竟有何影响?(下篇)

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

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

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

原文链接:blog.ouyangsihai.cn >> alter table新增字段操作究竟有何影响?(下篇)

本文的上篇文章可参考:

作为本篇的内容,主要包含两个方面:

  1. 话题1:不同锁模式的实验
    接着上篇博文的话题,针对几种常见的锁模式通过实验感受下之间的不同。
  2. 话题2:不同方式新增字段的效率
    最近有个系统执行新增字段的操作出现了hang,借此使用实验说明下不同新增字段的方式对效率的影响和原理。

话题1:不同锁模式的实验

上篇博文查看了执行如下新增字段的trace文件:
alter table t add (sex varchar2(1));

发现执行该语句时是以NOWAIT方式对表添加了一个ROW EXCLUSIVE模式锁:
LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT

我们看下Oracle的官方解释:

ROW SHARE
ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access.

ROW SHARE允许并发访问被锁定的表,但是禁止用户以排他访问的方式锁定整张表。

ROW EXCLUSIVE
ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

ROW EXCLUSIVE和ROW SHARE相同,但禁止以SHARE模式锁定。当执行update、insert或delete语句时会自动获得ROW EXCLUSIVE锁。

我是初学者,反正我是没太明白两者的区别,晦涩。只有通过实验,才是最有助于理解其含义的方法。


实验版本: SQL select * from v$version where rownum = 1; Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 
64bit Production -----------------------  实验1:  session 1执行: SQL lock table t in row share mode; Table(s) Locked.  session 2此时可以执行以下语句: SQL update t set a = 1 where id =1;
1 row updated.  SQL select count(*) from t;
1000000

----------------------- 实验2:  session 1和session 2都可以执行: SQL lock table t in row share mode; Table(s) Locked.  此时session 1可以执行: update t set a = 1 where id = 1;
1 row updated.  此时session 2执行以下语句会hang: update t set a = 1 where id = 1;  但可以对其他行记录操作: SQL update t set b = 'B' where id = 2;
1 row updated.  -----------------------  实验3:  session 1执行: SQL lock table t in row share mode; Table(s) Locked.  此时session 2可以执行以下两条语句: SQL lock table t in share mode;   SQL lock table t in row exclusive mode;  但session 2执行以下语句hang: SQL lock table t in exclusive mode;

从ROW SHARE的效果来看,这种行级锁,允许不同session同时持有ROW SHARE或SHARE或ROW EXCLUSIVE锁,但某一session执行DML语句后,其他session就无法针对相同的数据行做DML操作,处于hang,除非上一session的DML操作commit或rollback,但此时还是可以允许并发的只读访问。但不允许其他session获得EXCLUSIVE锁。证明了Oracle官方所说的“禁止用户以排他访问的方式锁定整张表”。

接下来看看ROW EXCLUSIVE模式锁的实验。


实验1:  session 1执行: SQL lock table t in row exclusive mode; Table(s) Locked.  session 2以下语句均可执行: SQL select count(*) from t;
1000000

SQL update t set a = 1 where id = 1;
1 row updated.  但此时session 1再执行同行的操作则会hang: SQL update t set a = 1 where id = 1;  -----------------------  实验2:  session 1和session 2均可执行: SQL lock table t in row exclusive mode; Table(s) Locked.  如果session 1执行: SQL update t set a = 1 where id = 1;
1 row updated.  此时session 2执行以下语句就会hang: SQL update t set a = 1 where id = 1;  session 1和session 2均可执行: SQL select count(*) from t;
1000000

-----------------------  实验3:  session 1执行: SQL lock table t in row exclusive mode; Table(s) Locked.  此时session 2可以执行以下两条语句: SQL lock table t in row share mode; SQL lock table t in row exclusive mode;  但执行以下两条语句会hang: SQL lock table t in share mode; SQL lock table t in exclusive mode;

从ROW EXCLUSIVE的效果来看,这种行级锁,允许不同session同时持有ROW EXCLUSIVE或ROW SHARE锁,但某一session执行DML语句后,其他session就无法针对相同的数据行做DML操作,处于hang,除非上一session的DML操作commit或rollback,但此时还是可以允许并发的只读访问。即他允许多个会话拥有ROW行级EXCLUSIVE或SHARE锁,但无法同时获得EXCLUSIVE或SHARE锁,从限制上要比ROW SHARE更严格。证明了Oracle官方所说的“禁止以SHARE模式锁定”,EXCLUSIVE比SHARE更严格,自然也不能获得EXCLUSIVE锁。

以上是对两种ROW行级锁的实验,结论就是ROW EXCLUSIVE和ROW SHARE均可以允许并发只读操作,从锁的强弱看,ROW EXCLUSIVE ROW SHARE,但其实这种行级锁可能更多地还是通过DML语句自动获得,而不是用实验中的LOCK语句。以上只是为了更好地说明两者区别。

说完了行级锁,接下来看下表级锁:

SHARE
SHARE permits concurrent queries but prohibits updates to the locked table.

SHARE锁允许并发查询,但是禁止其他session对锁定的表更新。

EXCLUSIVE
EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

EXCLUSIVE允许锁定表的查询操作,但禁止其他session对该表的任何操作。

我觉得这里Oracle的介绍是有些问题的,不够严谨,至少没有说清楚到底限制有何不同,接下来,我们还是通过实验的方式进行说明。

首先来看SHARE模式锁:


实验1:  session 1执行: SQL lock table t in share mode; Table(s) Locked.  此时session 2可以执行: SQL select count(*) from t;
1000000

但执行以下语句会hang: SQL update t set b = 'a' where a = 1;  -----------------------  实验2:  session 1和session 2均可执行: SQL lock table t in share mode; Table(s) Locked.  session 1和session 2均可执行: SQL select count(*) from t;
1000000

但此时无论是session 1还是session 2先执行以下语句都会hang
(比如此处是session 1先执行): SQL update t set b = 'a' where a = 1;  此时session 2执行以下语句也会hang: SQL update t set b = 'a' where a = 1;  但同时session 1处于hang的语句会报错: SQL update t set b = 'a' where a = 1; update t set b = 'a' where a = 1* ERROR at line 1: ORA-00060: deadlock detected while waiting for resource  此时session2还处于hang的状态。  -----------------------  实验3:  session 1执行: SQL lock table t in share mode; Table(s) Locked.  此时session 2可以执行: SQL lock table t in row share mode; SQL lock table t in share mode;  但以下两条语句均会hang: SQL lock table t in row exclusive mode; SQL lock table t in exclusive mode;

从SHARE模式锁的效果来看,

(1) 如果某一session获得SHARE模式锁后,其他session还可以执行DML操作。

(2) 如果多个session同时获得SHARE模式锁,则这些session只能执行读操作,做DML操作会hang。

(3) 如果两个session同时对一行记录做DML操作,则第一个session会报ORA-60死锁错误,直接被Oracle检测退出,第二个session继续处于hang。

(4) 如果一个session获得了SHARE模式锁,则其他session不能再获得ROW EXCLUSIVE或EXCLUSIVE模式锁,但可以获得ROW SHARE或SHARE模式锁。

因此对开始的介绍:

SHARE锁允许并发查询,但是禁止其他session对锁定的表更新。

更严谨的是说对多个获得SHARE锁的session来说,允许并发读,但禁止做DML操作,即只需看,不许改,这也是SHARE的含义。

接下来看看EXCLUSIVE模式锁的实验。


实验1:  session 1执行: SQL lock table t in exclusive mode; Table(s) Locked.  此时session 2可以执行: SQL select count(*) from t;
1000000

但执行以下语句会hang: SQL update t set b = 'a' where a = 1;  -----------------------  实验2:  session 1执行: SQL lock table t in exclusive mode; Table(s) Locked.  session 2执行以下语句会hang: SQL lock table t in exclusive mode;  -----------------------  实验3:  session 1执行: SQL lock table t in exclusive mode; Table(s) Locked.  此时session 2执行以下语句均会hang: SQL lock table t in row share mode; SQL lock table t in share mode; SQL lock table t in row exclusive mode; SQL lock table t in exclusive mode;

从EXCLUSIVE模式锁的效果来看,

(1) 如果某一session获得EXCLUSIVE模式锁,则其他session只能允许读操作,禁止DML操作。

(2) 如果某一session获得EXCLUSIVE模式锁,则禁止其他session再获得ROW SHARE、SHARE、ROW EXCLUSIVE或EXCLUSIVE各种模式锁。

因此对开始的介绍:

EXCLUSIVE允许锁定表的查询操作,但禁止其他session对该表的任何操作。

更严谨的是说对多个获得EXCLUSIVE模式锁的session来说,除了读操作外,禁止其他任何操作。

显然,从锁的强弱看,EXCLUSIVESHAREROW EXCLUSIVEROW SHARE。

另外,还有一种锁

SHARE ROW EXCLUSIVE
SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

SHARE ROW EXCLUSIVE模式锁用来查看整张表,允许其他session检索表中的行,但禁止其他session以SHARE模式锁定表或者更新行。


实验1:  session 1执行: SQL lock table t in share row exclusive mode; Table(s) Locked.  此时session 2可以执行: SQL select count(*) from t;
1000000

但执行以下语句会hang: SQL update t set b = 'a' where a = 1;  -----------------------  实验2:  session 1执行: SQL lock table t in share row exclusive mode; Table(s) Locked.  session 2执行以下语句会hang: SQL lock table t in share row exclusive mode;  -----------------------  实验3:  session 1执行: SQL lock table t in share row exclusive mode; Table(s) Locked.  此时session 2可以执行: SQL lock table t in row share mode;  但session 2执行以下语句均会hang: SQL lock table t in share mode; SQL lock table t in row exclusive mode; SQL lock table t in exclusive mode;

从SHARE ROW EXCLUSIVE的效果来看,相比SHARE允许其他session同时获得SHARE模式锁,其禁止其他session获得SHARE模式锁。

从锁的强弱看,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。

数字越大锁级别越高, 影响的操作越多。

话题2:不同方式新增字段的效率


实验1:  SQL set timing on
SQL alter table t add add_a number;  Table altered.  Elapsed: 00:00:00.29

新增一个允许NULL,且无默认值的字段,用时0.29秒。

前文介绍了,获得的是一个ROW EXCLUSIVE模式锁。

LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT


实验2:
SQL alter table t add add_b number default 0;
Table altered.
Elapsed: 00:00:59.34

新增一个允许NULL,但有默认值的字段,用时59秒。

从10046的trace文件看,他会首先用EXCLUSIVE模式锁来锁定表。

LOCK TABLE "T" IN EXCLUSIVE MODE  NOWAIT

同时,在最后执行了更新字段ADD_B为默认值的操作:

update "T" set "ADD_B"=0;

因此不难想为什么耗时这样久,就是还需要更新所有字段为默认值,另外,还会因为数据量的增加,可能需要更多的UNDO空间,进而可能因为一条新增字段的操作,导致整个库的UNDO表空间不够用,不仅影响对这张表的正常增删改操作(因为获取了最高级别EXCLUSIVE锁),还有可能影响其他业务功能(因为UNDO表空间不够用)。


实验3:  SQL alter table t add add_c number default 0 not null; Table altered. Elapsed: 00:00:00.16

新增一个包含NOT NULL约束,有默认值的字段,用时0.16秒。

从10046的trace文件看,会获得一个ROW EXCLUSIVE模式锁来锁定表。

“LOCK TABLE “T” IN ROW EXCLUSIVE MODE NOWAIT“`

锁的级别比实验2要低,而且该默认值是存储于col$数据字典表中的,并不是保存在原表记录上,这点的原因可以参见David的博文(http://blog.csdn.net/tianlesoftware/article/details/7226893)。即新增一个NOT NULL和默认值的字段,以后每次需要使用该字段时,默认值都是从数据字典中查询到的,这样就减少了新增字段时的DDL语句时间,也减少了存储空间(不用每条需要使用默认值的记录都存储默认值)。


实验4:  SQL select count(*) from t;
1000000
   
SQL alter table t add add_h number not null;           alter table t add add_h number not null             *ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column

新增一个仅有NOT NULL约束,没有默认值的字段,则需要表为空。

从官方文档的介绍看,其实从11g之后,对于新增字段,Oracle进行了优化,

Adding Table Columns

If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table. For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML.

You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value.

说的很明白了,如果新增一个含有默认值的字段,那么会立即更新每一行,在更新过程中,会有一个EXCLUSIVE级别的锁在该表上。如果指定NOT NULL和默认值,则会进行优化,降低阻止DML操作的时间。如果增加一个仅有NOT NULL的约束字段,那么需要表不能包含任何记录,否则就需要必须指定一个默认值,这也好理解,如果执行之前有记录,又要求NOT NULL,那么之前的记录字段默认值是什么就需要指定才行。

顺带提一句,删除表字段的操作:


SQL alter table t drop column add_b;
Table altered.
Elapsed: 00:00:43.44

从10046的trace文件看,也是获得了一个EXCLUSIVE锁,进而更新的过程中是对整张表的DML操作有影响的。

LOCK TABLE "T" IN EXCLUSIVE MODE  NOWAIT

总结:

  1. 11g以上的版本,如果使用NOT NULL和默认值的方式新增字段,那么执行时间会大大降低。且只会有一个ROW EXCLUSIVE级别锁。
  2. 11g以上的版本,如果使用默认值,没有NOT NULL约束的方式新增字段,那么执行时间会很久,取决于表中数据量的大小,获得的是EXCLUSIVE级别锁,期间会影响所有记录的DML操作,可能会因UNDO不足对其他操作有影响。
  3. 11g以上的版本,如果新增字段没有默认值,也没有NOT NULL约束,则还是会使用ROW EXCLUSIVE模式锁,但由于不需要更新字段值,执行时间也是比较短。

如何选择11g上新增字段的方式,看来是有一个比较清晰的方向了。

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

alter table新增字段操作究竟有何影响?(下篇)
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

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

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

原文链接:blog.ouyangsihai.cn >> alter table新增字段操作究竟有何影响?(下篇)


 上一篇
有索引却不用的两个场景 有索引却不用的两个场景
一直有朋友问,是不是表建了索引,一定会使用索引,在RBO时代,访问效率会参考一些规则,优先级高的,认为效率就高,例如索引就比全表扫描效率高,但CBO时代,则会以成本为依据,谁的成本低,谁的效率就高,这样更科学。 建了索引,SQL却未使用索引
下一篇 
修改表字段长度的操作,对业务是否有影响? 修改表字段长度的操作,对业务是否有影响?
前两天测试同学问了一个问题,表中某一个字段,需要改一下长度,对业务是否会有影响? 可能隐约之中,我们觉得没影响,但又好像有影响,究竟有何影响,我们从实验来看最科学。 首先建测试表,NAME字段是VARCHAR2(10),10个字节的字符串类