468,121 Members | 1,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,121 developers. It's quick & easy.

Problem of UNION ALL view & Update of master table row in Paralel Execution

1
Hello Everyone,

I have a little different problem and I though anyone might give me any idea that what is going wrong.

I have a view which consists of 3 tables linked by UNION ALL, overall all the three tables contains around 50 million, 2 million & 3 million respectively.
Here is the view looks like:
Create view test as
Select * from tab1 // contains 50 million rows
Union all
Select * from tab2 // contains 50 million rows
Union all
Select * from tab3 // contains 50 million rows

This view is used to get the appropriate ID existing in any of the including table. Now the problem area is that when we query the view, it takes around 4 minutes to execute and in parallel if we try to update any row from tab1, it gives us timeout error:
update tab1 set process_bitmap = process_bitmap where trans_id = 21159815
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001

We cant afford to increase the timeout to 5 minutes. Only 20-40 sec is acceptable to us. Kindly suggest us some solution.

Also let me know that why the row is being locked due to the select operation, because the view actually selects the row & shows it.

So it is very surprising for us that what is going wrong and where? If we need to tune some configurations then kindly suggest.

Thanking you in advance for your anticipation.

Regards,
Omer Saeed Khan
Mar 27 '08 #1
0 1597

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by kalamos | last post: by
6 posts views Thread by Eugene | last post: by
7 posts views Thread by urban.widmark | last post: by
reply views Thread by Stewart Midwinter | last post: by
5 posts views Thread by hubmei75 | last post: by
3 posts views Thread by Juan Antonio Villa | last post: by
6 posts views Thread by PW | last post: by
18 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.