Things to consider with WCF-SQL

Suppose you have a SQL Server database that contains information you need to poll from BizTalk Server. The database contains events that you need to process one by one, with the oldest events first, but without there being any real in-order delivery demands. One way to start polling these would be to have a stored procedure that looks like this:

declare @Id int

select top(1) @Id=Id from MyTable Where MyCondition = 23

update MyTable set Processed = 1 where Id = @Id

select Id, MyData from MyTable where Id = 1

The first thing to take note of is WCF-SQL Transaction Level and SQL Server locking.

Let’s do that before we even bring BizTalk into the picture.

What we can see from this is that while this procedure triggers…

One of the ways to reduce locks is to reduce transaction isolation level. This is especially important if this database is not used by BizTalk alone, but might be a very active OLTP database in itself; a backend data store to a multi-user application. The WCF-SQL adapter will default to the Serializable isolation level. You can read more about isolation levels here. In short this means that a Shared/Read lock will be placed on all data read, and an exclusive/write lock on all data changed. Another procedure can read the same data if it has a read lock on it, but cannot read the data that has a write lock on it. And another procedure cannot update any data that has a lock on it.