Deadlocks while using SQLAdapter

Deadlocks are often hard to chase down, but usually you should have an SQL expert try. I’m guessing from your post that you are inserting or updating data in an SQL database, and that is causing deadlocks (i.e. your are deadlocking on theuser database, and not the Biztalk databases).

If you are doing async SQL (no response), you can set the retries in the port, and then have a failover to a flat file.

If you are doing sync SQL (request/response), such as calling a stored proc that returns a response from an orch, then you can put the code in a loop, and loop until no error or max retries. Catch a SOAP Exception (see recent posts on how to catch SQL exceptions as SOAP exceptions).

Often, if you just wait a second (Delay shape) and try again, the other SQL task will have finished and you will not get the deadlock. If you have some ETL or long running SQL job that does a lot of locking, you might have bigger issues (and/or need a longer retry interval).

Neal Walters
http://Biztalk-Training.com