|
Home > Archive > BizTalk Server General > May 2005 > BizTalk SQL Adapter creates deadlock in database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BizTalk SQL Adapter creates deadlock in database
|
|
| Ashish Shukla 2005-05-25, 7:48 am |
| Hi Gurus,
I have a problem with BizTalk2004 SQL adapter. We have an orchestration that
receives a file and uploads it into a table in database. There can be number
of files coming in during the day. If 2 or more files come simultaneously, we
get an error saying "Deadlock occured and your process was chosen as deadlock
victim". I searched on the net for this issue and found couple of posts.
Following are the suggestions that I compiled from those posts:
1. Use "Set transaction isolation level read commited".
2. "Insert / update" with "rowlock"
3. "Select" with "nolock".
We tried all the above but it did not solve the problem. Is there a way out
of this?
Thanks a zillion in advance.
Ashish Shukla
| |
| Henry W 2005-05-25, 5:59 pm |
| Hello Ashish,
You may want to either use Trace Flag 1204 or SQL Profiler to help locate
the resource contention.
What is the average of number of rows to be inserted for each inbound file?
Do you have triggers on the destination table?
Do you have other application inserting/updating/select from the table, besides
Biztalk 2004?
Henry
> Hi Gurus,
>
> I have a problem with BizTalk2004 SQL adapter. We have an
> orchestration that receives a file and uploads it into a table in
> database. There can be number of files coming in during the day. If 2
> or more files come simultaneously, we get an error saying "Deadlock
> occured and your process was chosen as deadlock victim". I searched on
> the net for this issue and found couple of posts. Following are the
> suggestions that I compiled from those posts:
>
> 1. Use "Set transaction isolation level read commited".
> 2. "Insert / update" with "rowlock"
> 3. "Select" with "nolock".
> We tried all the above but it did not solve the problem. Is there a
> way out of this?
>
> Thanks a zillion in advance.
>
> Ashish Shukla
>
| |
| Ashish Shukla 2005-05-26, 2:51 am |
| What is the average of number of rows to be inserted for each inbound file?
Answer: Average number of rows inserted / updated is 500.
Do you have triggers on the destination table?
Answer : No tables do not have triggers.
Do you have other application inserting/updating/select from the table,
besides
Biztalk 2004?
Answer : Yes we have web application, accessing the same set of data besides
BizTalk. This is a business requirement and we cannot stop anyone from
accessing the data.
Thanks for the response.
Regards
Ashish Shukla
| |
| Phua Chiu Kiang 2005-05-26, 8:01 am |
| I have also experienced this before. The problem appears to be fixed by
installing BTS2004 SP1.
Phua Chiu Kiang
http://www.pckconsulting.com
"Ashish Shukla" wrote:
> What is the average of number of rows to be inserted for each inbound file?
> Answer: Average number of rows inserted / updated is 500.
>
> Do you have triggers on the destination table?
> Answer : No tables do not have triggers.
>
> Do you have other application inserting/updating/select from the table,
> besides
> Biztalk 2004?
> Answer : Yes we have web application, accessing the same set of data besides
> BizTalk. This is a business requirement and we cannot stop anyone from
> accessing the data.
>
> Thanks for the response.
>
> Regards
> Ashish Shukla
>
|
|
|
|
|