SQL deadlock and Ordered Delivery

[msnews.microsoft.com]

In a BTS2006 application, I am calling a SQL Server proc from an
orchestration through a Static Solicit/Response Send Port. Within a
transaction, the stored procedure updates a table and returns an aggregated
select of that table back to the orchestration. Occasionally, a fair number
of deadlocks will occur from which BizTalk usually recovers within the 3
retries.

To avoid the deadlocks, I tried using Ordered Delivery on the Send Port's
Advanced Transport Options dialog to throttle back the SQL calls. This works
like a champ at the cost of some throughput - no big deal. There is one
strange side effect. After all of the messages have been successfully
processed and the app is idle again, a single active service instance of the
send port remains in the admin console. There are no messages associated
with it.

Is this expected? Have I configured or coded something incorrectly?

Thanks!

Frank

[Frank Hofstaedter]

[MRLee]

Hi Frank!

When I used 'Ordered Delivery' I met the same thing.(Active Instance, but
process already done)
So I think that's not your fault.

And, I have three question about deadlock.
1. Did you tunning proc?
2. After procedure works, Is there another job working ? (just like 'Trigger
or Scheduled job')
3. How about test after turn off 'Messaging or Orchestration Tracking' option?

Thanks!
Lee

"msnews.microsoft.com"님이 작성한 내용:

[Frank Hofstaedter]

1. Yes, the proc is tuned.
2. No other tasks run or are triggered
3. I'll try it.

I think the answer may have something to do with how BizTalk internally
handles Ordered Delivery on a Send Port. Based on things I've stumbled
across on various websites, it appears as if internal correlation sets are
created to match up the request/response in an ordered fashion, in essence a
convoy.

I was hoping a Microsoft resource could comment on this.This is our first
production BizTalk deployment and we are trying to develop production
support procedures for our operations staff. It would be nice to know what
is "normal" and what is not for long running active service instances in the
Admin Console.

Thanks!

"MRLee" <MR...@discussions.microsoft.com> wrote in message
news:00C3ABB1-62AE-4BDB...@microsoft.com...

> Hi Frank!
>
> When I used 'Ordered Delivery' I met the same thing.(Active Instance, but
> process already done)
> So I think that's not your fault.
>
> And, I have three question about deadlock.
> 1. Did you tunning proc?
> 2. After procedure works, Is there another job working ? (just like
> 'Trigger
> or Scheduled job')
> 3. How about test after turn off 'Messaging or Orchestration Tracking'
> option?
>
> Thanks!
> Lee
>
>
>

> "msnews.microsoft.com"?? ??? ??:

[Lee Graber [MSFT]]

:) Okay, apparently I should blog about this topic since I do agree it can
be confusing. Even I saw it once and forgot what was happening and was
thrown for a moment.

OrderedDelivery sendports are the practical equivalent of a looping, never
ending orchestration. What happens underneath the cover is that a singleton
service instance of that sendport is created (using some convoy tricks) and
all messages for that sendport are routed to this instance. As such, it
never goes away. We could have tried to bulid some type of timeout
mechanism, but it would not have been "complete". Hence, when you use an
ordered delivery sendport, you get an active (or dehydrated if you shutdown
the process) instance which exists forever until you terminate it via the
MMC. We are considering how to make the ordered delivery more flexible so
you can have an instance for each stream and not have to order all messages
if they are not all related and then to control the lifetime of those
sendport instances in a more deterministic fashion.

HTH
Lee

This posting is provided "AS IS" with no warranties, and confers no rights.

Connected Systems Division Team
--------------------
>>From: "Frank Hofstaedter" <fhofst...@hotmail.com>
>>References: <e6GT9Yhx...@TK2MSFTNGP02.phx.gbl>
<00C3ABB1-62AE-4BDB...@microsoft.com>
>>Subject: Re: SQL deadlock and Ordered Delivery
>>Date: Wed, 23 Aug 2006 07:41:27 -0400
>>Lines: 72
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <OTFpTkqx...@TK2MSFTNGP05.phx.gbl>
>>Newsgroups: microsoft.public.biztalk.general
>>NNTP-Posting-Host: 208-39-156-98.isp.comcastbusiness.net 208.39.156.98
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.biztalk.general:40251
>>X-Tomcat-NG: microsoft.public.biztalk.general

[Frank Hofstaedter]

Understanding that this is expected behavior and not a coding or
configuration error helps a lot!

Thanks,
Frank

"Lee Graber [MSFT]" <le...@microsoft.com> wrote in message
news:%232eYTn5...@TK2MSFTNGXA01.phx.gbl...

[Reena]

Hi Frank,

I am experiencing the same - have you found out what causes it?

R

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Jan Eliasen]

On Fri, 18 May 2007 08:59:23 GMT, "Reena"<reena_...@yahoo.com>
wrote:

>I am experiencing the same - have you found out what causes it?

What does your SP look like?

--
eliasen, representing himself and not the company he works for.

Private blog: http://blog.eliasen.dk

Private email: j...@eliasen.dk