oracle 11.2.0.1
不明白为什么delete会阻塞insert,下面是步骤:
session 424:
app@HZ> SELECT sid FROM v$mystat WHERE rownum = 1;
SID
----------
424
app@HZ> desc collect_data
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
AD_ID NUMBER
SITE_ID NUMBER
ACTIVATIVE_USER NUMBER
START_TIME NUMBER
N_DATA_TYPE NUMBER
ACTIVITY_USER NUMBER
CHILD_CHANNEL NUMBER
VISIT_TIMES NUMBER
app@HZ> select INDEX_NAME ,INDEX_TYPE ,UNIQUENESS from user_indexes where TABLE_NAME='COLLECT_DATA';
INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
COLLECT_DATA_TIME_IBDX FUNCTION-BASED BITMAP NONUNIQUE
COLLECT_DATA_ID NORMAL UNIQUE
app@HZ> col COLUMN_EXPRESSION for a40
app@HZ> select INDEX_NAME ,COLUMN_EXPRESSION from user_ind_expressions where TABLE_NAME='COLLECT_DATA';
INDEX_NAME COLUMN_EXPRESSION
------------------------------ ----------------------------------------
COLLECT_DATA_TIME_IBDX TO_CHAR("STATED_TIME",'yyyymmdd')
表 collect_data.id为主键
删除部分行,并未提交
app@HZ> DELETE FROM collect_data WHERE to_char(stated_time ,'yyyymmdd') = '20131016' AND n_data_type is not null and n_data_type <> 1 ;
91 rows deleted.
session 11:
app@HZ> SELECT sid FROM v$mystat WHERE rownum = 1;
SID
----------
11
使用sequence插入值
app@HZ> INSERT INTO collect_data
(id ,ad_id ,site_id ,child_channel ,stated_time ,created_time ,n_data_type ,
activative_user ,activity_user ,activity_user_new ,income ,income_interact ,
run_type, internet_type, mobile_brand, easouplus_version, region)
5 SELECT collect_data_seq.nextval ,t.* from testa t;
一直在这里等待,testa表里只有91条数据
查看lock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ------------------- ---------- ---------- ----------
11 TX 720929 8413800 None 4 18 0
11 TM 86024 0 Row Exclusive 0 18 0
424 TM 86024 0 Row Exclusive 0 22 0
11 TX 786436 3246450 Exclusive 0 18 0
424 TX 720929 8413800 Exclusive 0 22 1
5 rows selected
session11在表collect_data上等待4号锁。
疑问:这里的等待应该是表collect_data上有主键导致,oracle为了保证唯一性,所以让session11等待,对于id列删除的值
和插入的值都是由序列collect_data_seq生成的是不会重复的,不应该被阻塞才对,请教大侠们。