El Grego

I have noticed this a lot using SQL Solicit response adapters. All things go fine, untill you dump a load of messages at the same time. This causes a lot of transaction deadlocks. I solved this by using:
'SET TRANSACTION ISOLATION LEVEL READ COMMITTED'
in the stored procdurecalled by the adapter. This usually works, but sometimes I needed to play around with the SQL in the SP too.

Just found an interesting post by Florin Lazar @ http://blogs.msdn.com/florinlazar/archive/2003/12/10/42686.aspx
He has a whole post-category dedicated to MSDTC @ http://blogs.msdn.com/florinlazar/category/2125.aspx

Here's an excerpt:
'The MSDTC transaction will always carry the same isolation level which is the one that was initially specified when the transaction was created. It cannot be changed later. Some resource managers allow you to change the isolation level. For instance, the isolation level that SQL Server is using on a connection can be changed using "SET TRANSACTION ISOLATION LEVEL". Changing the isolation level at statement level inside SQL Server or any other resource manager is invisible to MSDTC. This is why mixing MSDTC transaction isolation levels with resource manager statement level isolation levels is not recommended.'

If you check the comments you can see that Shawn Smith already had complaints about the previous version of BizTalk and the SQL adapter (AIC those days), more than a year ago...

You could try applying the hint: WITH (NOLOCK) to the select statement.


e.g.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
Select * from authors with (nolock) where contract = 1
GO

JB: thanks for sharing the NOLOCK option...

Last Friday I received already a couple of interesting e-mails from nice and smart MS experts (Dirk Gubbels, Leo Olivers and Stephane Bouillon of course) . They gave me this extra information:

'You can add ‘With (Readcommitted)’ to the tables in the select, or data modification statement. This will override the default Serializeable isolation level, and keep the number of locked records to a minimum.

And in SQL server 2005, we’ll have a brand new Isolation level called ‘Snapshot’ that will provide a way to read data even when someone else has locked it.'

I will have to update the article to include 'Select * from authors with (Readcommitted) where contract = 1' as a solution because a lot of people never read the comments. Stay tuned...

For this and various other reasons I think I've come to the conclusion that it is almost always better to create a data access layer component for all DB interactions and not us the Biztalk SQL Adapter at all.

Información básica que tiene que saber del adaptador la pueden encontrar aquí. Sin embargo un problema...

Regarding "You can add ‘With (Readcommitted)’ to the tables in the select, or data modification statement. This will override the default Serializeable isolation level, and keep the number of locked records to a minimum" - have you managed to get this to work? If I add a (READCOMMITTED, READPAST) hint to my SQL adapter query then I get a runtime error "You can only specify the
READPAST lock in the READ COMMITTED or REPEATABLE READ isolation
levels". This implies to me that this hasn't overridden the serializable state?

After extensive research into the problem I have come to the following conclusions.

Option 1.
It IS possible to change the isolation level default for the SQL Adapter but you will do it for the whole of Biztalk. > Go to component services, find the "Microsoft.BizTalk.Adapter.SQL" package, modify the transaction level each of the components that the package contains (right click transaction tab).
You need to test this with your Biztalk Soln. It worked ok With mine for most things when I changed it to "Read Committed".
This was fine for me however... that was BTS 2006 not sure about previous versions.

Option 2.
Although option 1 will work, depending on the SQL Code being called (if it is a proc updating or inserting into more than one table with a FK relationship your still going to get deadlocks due to the multi-threaded processing of BizTalk).

The two solutions are
a. Remove the relationship (works sometimes but obviously not a great idea)

b. Serialize all SQL communication through the port, by setting the port to "ORDERD" right click port > Advanced Options > Select ORDERD. Basically the calls through the port will be Serialized thus reducing the chance of two threads accessing the same table same time creating deadlocks.

I've spent some time on this, but I am still not happy with the options, hopefully there is another ?

There is a SUPER solution to this SQL adapter isolation level.

Let me walk through the whole business requirement.
1. Sure you can use tricks inside the SP, in the component per machine as suggested in this thread. Those are good tricks but most often in those stored procedures, there is the SELECT and also UPDATE statement to flag the record(status = done). As you can imagine, there is a need to do temp table and table variable to create a data structure
that biztalk needs. Business logics FORCES you to have a lot of UPDATE, a lot of TEMP table, what do you do? UPDATE and temp table always locks sql table.
If there are 10 SQL receive locations that work on the same set of tables, how do you avoid the locks? the update?
I always run to this problem, CustomersA needs to send some edi file out based on a sql receive location1, CustomersB needs to send some other edi file out based on a sql receive location2.
Both sql receive location call same/similar store procedures that select and update the same/similar set of sql table.
As you are aware, on the sql receive location, you can pass in a parameter. So this is very possible to have CustomerA and CustomerB to call sp_DoBussiness @CustomerName

2. The solution1: Don't query sql tables directly. Set up 2 other database with the same set of table.
Source database --->through sql job or dts---> DatabaseA(a clone). Note: Only move CompanyA data into this databaseA
Source database --->through sql job or dts---> DatabaseA(a clone). Note: Only move CompanyB data into this databaseB

The 2 actions diagram above can be done inside a sql job. Basically, this is "multicast" data from Source database to CompanyA, CompanyB, etc..
Sound tedious but very easy to do.

From there, SQL adapter query
each CompanyA, CompanyB and never lock anything up because each company data already in its own database universe.

3. Solution2 (I only do this for simple set of data because it is has a timing/latency for the receive location config).
Don't mulicast data. Instead, in this case we working as usually but we "schedule" the receive locations through another table.
Here is the structure of SCHEDULE_RECEIVE_LOCATION table:
Company A Timestamp(9:20 AM)
CompanyB Timestamp(9:30 am)

We will only have one stored procedure, this will "loop through" that SCHEDULE_RECEIVE_LOCATION table, pick a company value out, run the for xml statement to give to biztalk, update the Timestamp.

Think of it like a queue for the receive locations. Never locks up that way because the call is serialized.
I don't like this method that much because if you have huge list of company you will make the stored procedure call 1 company at a time and this is low throughput (not parrellel call). I fix this by putting poll while data found = true.

Do solution1, you wont' regret it.
Multicast database out to many other database is the way to go.
Many customer has sub-company/division. Therefore, use solution1 to multicast between customers.
For each customer, if they have division and division value has to drive/appear in the business logic/data, use solution2.

Solution2 works behind of solution 1 is the BEST idea.
Send me the email if you need more explanation.
hunglukenguyen@yahoo.com