Deadlock on merge (oracle) - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Deadlock on merge (oracle)

Deadlock on merge (oracle)# Database - 数据库

c*t2008-06-09 07:06

1 楼

Two processes to merge data into same target/physical table:
A -> T; B -> T
SizeOf(A): 10K; SizeOf(B): 30K; SizeOf(T): 3 Million
As the tow processes running in parallel, it always cause deadlock:
Merge into T using A/B,
I am thinking to merge&commit A/B record by record to eliminate deadlock. Is
there any better way to avoid deadlock?

c*t2008-06-09 07:06

2 楼

Tried A->T; B-A->T hopefully it will help

Is

【在 c**t 的大作中提到】

: Two processes to merge data into same target/physical table:
: A -> T; B -> T
: SizeOf(A): 10K; SizeOf(B): 30K; SizeOf(T): 3 Million
: As the tow processes running in parallel, it always cause deadlock:
: Merge into T using A/B,
: I am thinking to merge&commit A/B record by record to eliminate deadlock. Is
: there any better way to avoid deadlock?

c*d2008-06-09 07:06

3 楼

不应该呀？A->T，B->T 都是insert吗？
出现了deadlock错误提示？

c*t2008-06-09 07:06

4 楼

No; Merge into T using A on (T.KEY=A.KEY) WHEN NOT MATCHED THEN INSERT ...
WHEN
MATCHED THEN SET ...

【在 c*****d 的大作中提到】

: 不应该呀？A->T，B->T 都是insert吗？
: 出现了deadlock错误提示？

c*d2008-06-09 07:06

5 楼

如果T,A,B结构相同，可以这样做
Merge into T using A on (T.KEY=A.KEY)
WHEN NOT MATCHED
THEN INSERT ...
WHEN MATCHED
THEN DELETE T INSERT A ...

【在 c**t 的大作中提到】

: No; Merge into T using A on (T.KEY=A.KEY) WHEN NOT MATCHED THEN INSERT ...
: WHEN
: MATCHED THEN SET ...

B*g2008-06-09 07:06

6 楼

where is B?

【在 c*****d 的大作中提到】

: 如果T,A,B结构相同，可以这样做
: Merge into T using A on (T.KEY=A.KEY)
: WHEN NOT MATCHED
: THEN INSERT ...
: WHEN MATCHED
: THEN DELETE T INSERT A ...

B*g2008-06-09 07:06

7 楼

what is "WHEN MATCHED THEN SET"? you mean update?

【在 c**t 的大作中提到】

: No; Merge into T using A on (T.KEY=A.KEY) WHEN NOT MATCHED THEN INSERT ...
: WHEN
: MATCHED THEN SET ...

c*t2008-06-09 07:06

8 楼

same merge... just replace A with B

【在 B*****g 的大作中提到】

: where is B?

c*t2008-06-09 07:06

9 楼

yes

【在 B*****g 的大作中提到】

: what is "WHEN MATCHED THEN SET"? you mean update?

c*d2008-06-09 07:06

10 楼

B和A的做法一样
因为没有update操作，所以B->T, A->T同时操作也不会出现deadlock

【在 B*****g 的大作中提到】

: where is B?

c*t2008-06-09 07:06

11 楼

no both B and A have update/insert, their merge codes are almost identical.

【在 c*****d 的大作中提到】

: B和A的做法一样
: 因为没有update操作，所以B->T, A->T同时操作也不会出现deadlock

c*d2008-06-09 07:06

12 楼

???没看懂你的意思
我的意思是，在A->T, B->T的操作中，使用delete+insert替换update
所以A->T, B->T的操作可以同时进行，不会出现deadlock

【在 c**t 的大作中提到】

: no both B and A have update/insert, their merge codes are almost identical.

B*g2008-06-09 07:06

13 楼

ding. let me try try, I am thinking if matched, but only need to update some
columns not all, how to do delete+insert. hehe, I guess delete+insert will
not work on 9i, DD 9i.

【在 c*****d 的大作中提到】

: ???没看懂你的意思
: 我的意思是，在A->T, B->T的操作中，使用delete+insert替换update
: 所以A->T, B->T的操作可以同时进行，不会出现deadlock

B*g2008-06-09 07:06

14 楼

how to write delete in merge? can not make it on 9i

some
will

【在 B*****g 的大作中提到】

: ding. let me try try, I am thinking if matched, but only need to update some
: columns not all, how to do delete+insert. hehe, I guess delete+insert will
: not work on 9i, DD 9i.

c*t2008-06-09 07:06

15 楼

no delete. Here is the pseduo scripts:
package ETL
function load_feed1
return LOAD.IMPORT('A')
end;
function load_feed2
return LOAD.IMPORT('B')
end
end ETL
package LOAD
function IMPORT(externalTable as varchar2) return number
MERGE INTO targetTable x using externalTable y
on x.key = y.key
WHEN MATCHED THEN UPDATE
set field = y.field
WHEN NOT MATCHED THEN INSERT
(field1, field2) values (y.field1, y.field2);

【在 c*****d 的大作中提到】

: ???没看懂你的意思
: 我的意思是，在A->T, B->T的操作中，使用delete+insert替换update
: 所以A->T, B->T的操作可以同时进行，不会出现deadlock

c*t2008-06-09 07:06

16 楼

there are two apps running in parallel:
App1, every m minutes, select ETL.load_feed1 from dual;
App2, every n minutes, select ETL.load_feed2 from dual;
Assuming m > n; when app1 update/insert targetTable, app2 comes in and does
same thing. Now app1 will see different state of targetTable, deadlock
happens.

【在 c**t 的大作中提到】

: no delete. Here is the pseduo scripts:
: package ETL
: function load_feed1
: return LOAD.IMPORT('A')
: end;
: function load_feed2
: return LOAD.IMPORT('B')
: end
: end ETL
: package LOAD

B*g2008-06-09 07:06

17 楼

I believe he means not use "update set ..." but use "delete .... insert ...."

【在 c**t 的大作中提到】

: no delete. Here is the pseduo scripts:
: package ETL
: function load_feed1
: return LOAD.IMPORT('A')
: end;
: function load_feed2
: return LOAD.IMPORT('B')
: end
: end ETL
: package LOAD

c*t2008-06-09 07:06

18 楼

no delete isn't an option. The data might being viewed at the same time.

.."

【在 B*****g 的大作中提到】

: I believe he means not use "update set ..." but use "delete .... insert ...."

w*r2008-06-09 07:06

19 楼

Running two upsert operation in parallel is almost guaranteed to have dead
lock. You might only want to run the merge in serialized way, rather than
launch two merge in parallel.

【在 c**t 的大作中提到】

: no delete. Here is the pseduo scripts:
: package ETL
: function load_feed1
: return LOAD.IMPORT('A')
: end;
: function load_feed2
: return LOAD.IMPORT('B')
: end
: end ETL
: package LOAD

B*g2008-06-09 07:06

20 楼

delete immediately follow by insert?

【在 c**t 的大作中提到】

: no delete isn't an option. The data might being viewed at the same time.
:
: .."

B*g2008-06-09 07:06

21 楼

is it same as Merge T with (A union all B)?

【在 w*r 的大作中提到】

: Running two upsert operation in parallel is almost guaranteed to have dead
: lock. You might only want to run the merge in serialized way, rather than
: launch two merge in parallel.

c*t2008-06-09 07:06

22 楼

tried:
merge into T using A
and
merge into T using B-A
still hit deadlock;

merge into T using AUB should fix that, but it's too time consuming. Where A
is fairly small, current hour data; B is historical-future data

【在 w*r 的大作中提到】

: Running two upsert operation in parallel is almost guaranteed to have dead
: lock. You might only want to run the merge in serialized way, rather than
: launch two merge in parallel.

B*g2008-06-09 07:06

23 楼

how about create a job queue? I can only think do it in java, do not know
oracle. Don't know if chain in oracle will work. hehe, just guess.

A

【在 c**t 的大作中提到】

: tried:
: merge into T using A
: and
: merge into T using B-A
: still hit deadlock;
:
: merge into T using AUB should fix that, but it's too time consuming. Where A
: is fairly small, current hour data; B is historical-future data

w*r2008-06-09 07:06

24 楼

if A is much smaller, then union will increase overhead to build spool on
the fly. Just make two jobs
sequential.

【在 B*****g 的大作中提到】

: how about create a job queue? I can only think do it in java, do not know
: oracle. Don't know if chain in oracle will work. hehe, just guess.
:
: A

B*g2008-06-09 07:06

25 楼

人家说的是多少分钟run一次，不是一个run完了另一个run。
我先这样搞，先搞一个queue（就一个table也行）。
schedual A 隔一定时间check queue， job A在queue，啥都不做，不在，加入尾巴
pending。
schedual B 隔一定时间check queue， job B在queue，啥都不做，不在，加入尾巴
pending。
schedual C 隔一定时间check queue，有job process，啥都不做；没有，但有pending
，take first one， update pending to process，run， when job complete，
delete job from queue。
其实就10k A， 30K B， union all 没几秒钟

【在 w*r 的大作中提到】

: if A is much smaller, then union will increase overhead to build spool on
: the fly. Just make two jobs
: sequential.

c*t2008-06-09 07:06

26 楼

这么说吧， 10K merge 到 3M table, 大概７秒；３０Ｋ要１５秒左右．时间还是长了些

pending

【在 B*****g 的大作中提到】

: 人家说的是多少分钟run一次，不是一个run完了另一个run。
: 我先这样搞，先搞一个queue（就一个table也行）。
: schedual A 隔一定时间check queue， job A在queue，啥都不做，不在，加入尾巴
: pending。
: schedual B 隔一定时间check queue， job B在queue，啥都不做，不在，加入尾巴
: pending。
: schedual C 隔一定时间check queue，有job process，啥都不做；没有，但有pending
: ，take first one， update pending to process，run， when job complete，
: delete job from queue。
: 其实就10k A， 30K B， union all 没几秒钟