|
Home > Archive > BizTalk Server Applications Integration > November 2004 > Sql Deadlock
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]
|
|
|
| Hi All,
I have a SQL Deadlock Issue as lots of inbound messages are trying to access
Database
How can i limit the number of concurrent running Orchestartions so that i
can serialize one orchestartion instance at a time
| |
| Alan Smith 2004-11-02, 5:48 pm |
| Hi,
Do you have any more details on your setup? BizTalk 2004, or 2002? Are you
using the SQL adapter? etc.
I have had this problem with the SQL Adapter in 2004 when calling a stored
procedure. I solved it by reducing the transaction isolation level in the SP,
don't know if this is an option in your project.
Regards,
Alan
"Kallu" wrote:
> Hi All,
>
> I have a SQL Deadlock Issue as lots of inbound messages are trying to access
> Database
> How can i limit the number of concurrent running Orchestartions so that i
> can serialize one orchestartion instance at a time
>
| |
|
| Hi Alan,
I am using BizTalk 2004 with Sql Adapter for Inserting Xml Messages
My scenario
Each xml message is inserted in DB,prior to it a Sql check is made
whether the record already exits in DB ,if so mark them as duplicate while
inserting the message
If i have more than 50 messages at a time i see lots of Sql Deadlocks
Regards,
"Alan Smith" wrote:
[vbcol=seagreen]
> Hi,
>
> Do you have any more details on your setup? BizTalk 2004, or 2002? Are you
> using the SQL adapter? etc.
>
> I have had this problem with the SQL Adapter in 2004 when calling a stored
> procedure. I solved it by reducing the transaction isolation level in the SP,
> don't know if this is an option in your project.
>
> Regards,
>
> Alan
>
> "Kallu" wrote:
>
| |
| Alan Smith 2004-11-03, 5:48 pm |
| Hi Kallu,
That's exactly the problem i had.
Try reducing the transaction isolation level of your stored procedure, add
this line of code:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
(The default level is SERIALIZABLE).
You will have to check that your data intgrity will remain intact, dropping
to REPEATABLE READ will allow phantom reads. There's good documentation on
this in the SQL Server books on line.
Hope this helps,
Regards,
Alan
"Kallu" wrote:
[vbcol=seagreen]
> Hi Alan,
>
> I am using BizTalk 2004 with Sql Adapter for Inserting Xml Messages
>
> My scenario
> Each xml message is inserted in DB,prior to it a Sql check is made
> whether the record already exits in DB ,if so mark them as duplicate while
> inserting the message
> If i have more than 50 messages at a time i see lots of Sql Deadlocks
>
>
> Regards,
>
>
> "Alan Smith" wrote:
>
| |
|
| Hi Alan,
Thanks for the Reply
Changing the Isolation Level of SP didnt remove Deadlocks
One of Posts mentioned about Uniform Sequential Convoys,I have tested it so
that i can have only one Instance of Orchestartion at a time.
The process is working fine but i see Messages in HAT even after
completing the Inserts.I am using loop condition set to always true.
Any Suggestions?
Regards,
"Alan Smith" wrote:
[vbcol=seagreen]
> Hi Kallu,
>
> That's exactly the problem i had.
>
> Try reducing the transaction isolation level of your stored procedure, add
> this line of code:
>
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
>
> (The default level is SERIALIZABLE).
>
> You will have to check that your data intgrity will remain intact, dropping
> to REPEATABLE READ will allow phantom reads. There's good documentation on
> this in the SQL Server books on line.
>
> Hope this helps,
>
> Regards,
>
> Alan
>
> "Kallu" wrote:
>
|
|
|
|
|