BizTalk Wcf_Custom - SQL Deadlock Issue

Question

I have the DB SP UPDATEClientID as below. Which Takes client ID as parameter.
I'm calling UPDATEClientID SP 50 times in a one second, from WCF Custom Adapter. Then I'm seeing the SQL Deadlock issue.

In my scenario, I have to call UPDATEClientID SP 50 times in one second. How to resolve the SQL Deadlock issue?

CREATE  PROCEDURE [dbo].[UPDATEClientID]
    @ClientID VARCHAR(50) = NULL

AS
BEGIN

    SET NOCOUNT ON;

    UPDATE  CleintDetails
    SET     STATUS = 'Y'
    WHERE   ClientID = @ClientID            

END

Answer 1

Do you really have to call this Stored Procedure 50 time in one second or is it the case that you just happen to call 50 times per second?

Some options:

1. Set Ordered Delivery on the Send Port. This will serialize the requests. It will however be several orders of magnitude slower.
2. Optimize the statement with lock hints, ROWLOCK for example.

Answer 2

stored procedure code is executing under the BizTalk server default transaction level serializable. Change it to read committed.

We can set the transaction level by following statement in your stored proc.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED