Geeks With Blogs

@gvdwiele
  • gvdwiele @kpellegr P/E GOOG: 23,28, ARMH 77,39, TSM 17,36, QCOM 18,36. maw mobile bubble is een feit. Dan zet ik mijn geld liever in op AAPL. about 694 days ago

El Grego BizTalk blog
The isolation setting is fixed and is SERIALIZABLE. So beware that even your most basic receive locations that only execute those very simple SQL statements such as 'select * from tablename' can generate locks. BTW, this is a general misconception: usually people think that select-statements never lock resources. Nothing is less true of course.
 
While having a shared lock on a range of keys, in addition to the keys themselves having locked, no records can be inserted. 
Here's a sample to demonstrate the effects of the SERIALIZABLE isolation setting:
 
Open your SQL Query analyzer
Open 2 seperate query windows to the local Pubs database
Copy & paste these samples statements:
 
Window A:
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
Select * from authors where contract = 1
GO
 
Window B:
 
BEGIN TRAN
INSERT INTO [pubs].[dbo].[authors]([au_id],[au_lname], [au_fname],  [contract])
VALUES('666-66-6666','Grego', 'El', 1)
GO
 
Now, if you try to execute both queries you will see that the second query will always be blocked by the first, irrespective of the order. You can immediately unblock the process by typing, selecting and executing 'ROLLBACK TRAN' in the blocking transaction's window. Now repeat this test and replace 'SERIALIZABLE' with 'READ COMMITTED' and you will see that when you first start batch A, you can still execute Batch B simultaneously (which wasn't so with the SERIALIZABLE level).
 
Here are the isolationlevel-enumerator values from system.data:
 
[Flags]
public enum IsolationLevel
{
      // Fields
      Chaos = 0x10,
      ReadCommitted = 0x1000,
      ReadUncommitted = 0x100,
      RepeatableRead = 0x10000,
      Serializable = 0x100000,
      Unspecified = -1
}
 
The BizTalk SQL adapter always uses 0x100000. You can check this by viewing the requested locks in Enterprise Manager:
 
 
Now, I believe the above will rarely be a problem in real-life. You should only expect performance problems when you have a lot of transactions and a lot of simultaneous lock requests for the same heavy resource while having a bad database-design (having no or having the wrong indexes). You should also know that lock waits are perfectly normal: a simple wait for a lock is different from a deadlock. The waiting process will get the lock anyway when the process that's holding the lock completes.
If you use SERIALIZABLE my best advice is to tune you sql statements for performance (also having correct db-design, normalization, the right indexes,...) in order to make your select statement execute as fast as possible .
 
**UPDATED**
 
Is there a solution?
  • If you try to add the 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' to a select-statement based receive location then you may have no issues at design time. But when you try to add a RL based on this SQL statement in the  BizTalk Explorer you will get a 'The SQL statement must be either a select or an exec' error.
  • You can add ‘With (Readcommitted)’ to the tables in the select, or data modification statement. 'Select * from authors with (Readcommitted) where contract = 1'. This will override the default Serializeable isolation level, and keep the number of locked records to a minimum. Credits go to Dirk Gubbels from Microsoft...
SQL Server locking experts' comments are very welcome...
Posted on Thursday, November 25, 2004 4:35 PM BizTalk - EAI - B2B | Back to top


Comments on this post: The BizTalk SQL Server Adapter isolation level

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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.

Left by Alan on Nov 26, 2004 1:25 PM

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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
Left by JB on Nov 27, 2004 11:39 PM

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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...
Left by Grego on Nov 28, 2004 11:02 AM

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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.
Left by Michael G. Emmons on Mar 23, 2005 4:17 PM

# Deadlocks en el adaptador de SQL
Requesting Gravatar...
Información básica que tiene que saber del adaptador la pueden encontrar aquí. Sin embargo un problema...
Left by BizTalkLATAM's Blog on Nov 02, 2005 8:23 AM

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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?
Left by kvix on Feb 08, 2006 7:10 AM

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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 ?
Left by Piers on Jun 05, 2006 11:26 AM

# re: The BizTalk SQL Server Adapter isolation level
Requesting Gravatar...
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
Left by hung on Jun 15, 2007 6:38 PM

Your comment:
 (will show your gravatar)


Copyright © Gregory Van de Wiele | Powered by: GeeksWithBlogs.net | Join free