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

最新推荐文章于 2022-10-21 11:51:13 发布

bisal(Chen Liu)

最新推荐文章于 2022-10-21 11:51:13 发布

阅读量6.2k

点赞数 2

分类专栏：个人心得 Oracle 技术之我见

本文链接：https://blog.csdn.net/bisal/article/details/49182025

版权

技术之我见同时被 3 个专栏收录

291 篇文章 4 订阅

订阅专栏

Oracle

251 篇文章 6 订阅

订阅专栏

个人心得

237 篇文章 8 订阅

订阅专栏

没想到距此篇博文的上半部分发表(http://blog.csdn.net/bisal/article/details/45418303)已经有半年的时间，上篇博文是5月小长假的时候，在开往杭州的高铁上完成的，话说第二天就有了我的小baby:)，难道写博客还有助孕的效果？需要的朋友不妨一试，哈哈，归根结底，还是需要作为IT从业者的我们，紧张工作之余，要有放松的安排，不仅是身体上的放松，还要有精神、心灵上的放松，俗话说得好“天空飘来五个字，那都不是事，是事也就烦一会，一会就没事”。

有点扯远了，说正事儿，作为本篇博文的内容，主要包含两个方面：
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来说，除了读操作外，禁止其他任何操作。

显然，从锁的强弱看，EXCLUSIVE>SHARE>ROW EXCLUSIVE>ROW 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(Chen Liu)

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
alter table新增字段操作究竟有何影响？(下篇)

没想到距此篇博文的上半部分发表(http://blog.csdn.net/bisal/article/details/45418303)已经有半年的时间，上篇博文是5月小长假的时候，在开往杭州的高铁上完成的，话说第二天就有了我的小baby:)，难道写博客还有助孕的效果？需要的朋友不妨一试，哈哈，归根结底，还是需要作为IT从业者的我们，紧张工作之余，要有放松的安排，不仅是身体上的放松，还要有精神、心灵
复制链接

扫一扫