上周上线碰见的ORA-00054错误回放
上周上线碰见的ORA-00054错误回放
上周应用上线，有一个数据库脚本，包含改字段长度等操作，执行过程中，现象就是有些改字段成功了，有些执行出错，报了ORA-00054的错误。了解一下原理，就能对这个错误，有比较深入的理解了。
首先，我们模拟下报错过程，创建测试表，session 1执行update语句，但不提交，session 2执行alter table变更name字段长度，此时立即报错ORA-00054，
SQL> create table tbl_lock(id number, name varchar2(10)); Table created. SQL> select * from tbl_lock; ?? ?ID NAME ---------- ---------- ?? ? 1 a ?? ? 2 b session 1： SQL> update tbl_lock set name='c' where id=1; 1 rows updated. session 2： SQL> alter table tbl_lock modify name varchar2(5); alter table tbl_lock modify name varchar2(5) ??????????? * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
我们看下报错，ORA-00054，提示的就是资源繁忙，因为设置了NOWAIT参数，或者超时，才返回这个错误，
看下此时的锁信息，其中14309是从dba_objects中根据object_name='TBL_LOCK'检索得出的，如下显示，TBL_LOCK表上有一个TM表锁，
表锁，又叫TM锁，当交易执行DML语句的时候，会拥有此锁。目的就是为了阻止此时有其他的进程正在执行DDL，修改表结构，
A?table lock, also called a?TM lock, is acquired by a transaction when a table is modified by an?INSERT,?UPDATE,?DELETE,?MERGE,?SELECT?with the?FORUPDATE?clause, or?LOCK?TABLE?statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.
结论：
至此，开始的问题，就可以解释清楚了，上线过程中，执行alter table改表的字段长度，但由于有些表，此时碰巧有业务操作，对数据做了DML，交易尚未提交，因此由于TM锁未释放，导致alter table这条DDL语句执行报错，对于alter table执行时尚未有DML未commit操作的表，自然就可以执行成功了。
解决方法：
就是等一会再执行，只要出现真空期，没有业务操作，就可以执行成功了，毕竟alter table改字段长度，需要改数据字典信息，对于表结构的变更，何时执行时间，会和表数据量有关，何时则无关，以前写了几篇小文章，不同的场景，有一些不同的结论，可以参考，
针对ORA-00054这问题，可以再了解一些。
从11g开始，出现了一个新的参数，
这个参数可以session级别设置，作用就是可以控制一条DDL语句等待一个DML锁释放的时间，默认值是0，表示NOWAIT，最大值是1000000秒，大约11.5天，如果在设置的时间之内，仍未获取DDL锁，则抛出异常错误，错误号就是ORA-00054，
DDL_LOCK_TIMEOUT?specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of?NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock. If a lock is not acquired before the timeout period expires, then an error is returned.
上面的实验中，DDL_LOCK_TIMEOUT默认值是0，因此执行alter table会立即报错，
设置参数值，改为10妙，执行alter table，确实SQL等待了10秒，才返回了ORA-00054错误，
SQL> alter session set ddl_lock_timeout=10; Session altered. Elapsed: 00:00:00.00 SQL> alter table tbl_lock modify name varchar2(5); alter table tbl_lock modify name varchar2(5) ??????????? * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:00:10.00
惜分飞文章(http://www.xifenfei.com/2012/07/oracle-11g%E7%9A%84ddl_lock_timeout%E5%8F%82%E6%95%B0.html)介绍了这个参数的作用，
ddl_lock_timeout可以在一定程度上解决因为我们不清楚这个表是否有dml操作而导致ddl操作不能进行的情况，从一定程度上减少了自己去尝试ddl操作，或者查询相关视图然后找出相关会话，然后kill掉对应数据的情况，可以说是在修改表结构的时候一个很不错的新特性。
11g之前，DDL操作，碰见TM锁，是直接报错，11g则用这参数，通过设置等待时间，可以避免一些DDL语句重复执行，例如开始碰见的问题，如果设置了DDL_LOCK_TIMEOUT，可能等待一会就会执行成功，而不需要我们手工再执行。
但这参数有一个问题，就是对于alter table加字段操作，是不起作用，无论ddl_lock_timeout设置为0还是非0，
SQL> alter table tbl_lock add sex varchar2(1); 会一直处于hang 直到人为中止 c^Calter table tbl_lock add sex varchar2(1) * ERROR at line 1: ORA-01013: user requested cancel of current operation
但是alter table删除字段、drop table删除表操作，可以生效，
SQL> alter table tbl_lock drop column name; alter table tbl_lock drop column name ??????????? * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> drop table tbl_lock; drop table tbl_lock ?????????? * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
MOS(Alter Table Add Column Command Hangs With Wait Event 'blocking txn id for DDL' (文档 ID 1553725.1))这篇文章，同样说明了这一个问题，由于11g中，alter table add column操作，没有被DDL排他锁覆盖，因此不受DDL_LOCK_TIMEOUT参数的控制，更不会抛出ORA-00054错误，而是出于hang，
In 11g, ?ALTER TABLE ADD COLUMN is not covered by an exclusive ddl lock; therefore,?it will not wait for the specified time in DDL_LOCK_TIMEOUT parameter and it will not raise the ORA-00054 error.
《DDL_LOCK_TIMEOUT Behavior in 11G (文档 ID 779569.1)》介绍了这个参数。
11.1.0.6版本，有人开了《Bug 7707888 : DDL_LOCK_TIMEOUT IS NOT WORKING AS EXPECTED》这个bug，此版本中，若有seesion执行DML未提交，此时alter table add column可以执行，但是drop table可以执行。
总结：
1. DDL_LOCK_TIMEOUT是11g新参数，对于一些频繁DML的表，若需要结构变更，可以设置非0，一定程度上，可以避免人为重新执行，自动找出真空期，执行完成DDL语句。
2. alter table加字段操作，不受DDL_LOCK_TIMEOUT控制，需要人为控制。