Problem with distributed transactions - multiple threads pop the same message from queue

Question

Hi,

I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().

I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.

My dequeue SP is as follows:

CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
    DECLARE @conversationHandle UNIQUEIDENTIFIER;
    DECLARE @messageTypeName SYSNAME;
    DECLARE @conversationGroupId UNIQUEIDENTIFIER;

        GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
        if (@conversationGroupId is not null)
        BEGIN
            RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
        END

    if @messageTypeName in
        (
            'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
            'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        )
    begin
         end conversation @conversationHandle;
    end
 END

Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?

Answer 1

RECEIVE is a destructive read (is actually a DELETE .. WITH OUTPUT from the queue-table), so is not possible to receive the same message twice, even in the same transaction.  As for the whole issue of using the CLR TransactionScope objects and Commit resulting in deadlock, I'm a bit skeptical. Deadlock involves the acquiring of locks, and since Commit never acquires new locks, Sql Commit cannot deadlock. Deadlock can occur only during the Sql read/write operations, as locks are being aquired. Can you post the actual deadlock graph information?

Answer 2

Hi Remus, thanks for your reply!

I found my problem...

I was using the following :

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
         call my code which dequeues and processess my queue entry here....
}

When i allowed multiple threads to call the above code.... every once in a while they dequeued the same entry.

Once i changed to:

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress))
{
         call my code which dequeues and processess my queue entry here....
}


The deadlocks no longer happen ... i am running up to 50 threads and no problems at all.
In this second case Transaction.Current is null within the braces as I guess the ambient transaction is suppressed. I still dont fully understand whats going on here... but its working.

Answer 3

It is official, the only way I can get the transaction scope to work properly ( without threads locking each other out when doing RECEIVE is if I do the TransactionScopeOption.Suppress ...

If I used Required or RequiresNew, I get thread locks.

Does anyone have any idea why this could be happening? Please ?

Answer 4

anyone have any idea why this could be happening?

Answer 5

I don't know why your thread is locking but System.Transaction which give you the TransactionScope class per the link below is for insert, update and delete because ANSI SQL transaction is a unit of work while System.Transaction transaction is not a unit of work because it performs none atomic transaction. 

http://msdn2.microsoft.com/en-us/library/ms172070.aspx

 

Answer 6

I have the following

            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
            {
                int entryId = mailerDb.DequeueProcessingEntry();

      Console.WriteLine(Thread.CurrentThread.Name, entryId);
      ts.Commit();

            }

When I run it with say 30 threads.... threads on occasions pop the same entryId........ and then an exception ( deadlock ) is thrown on ts.Commit();

If I change from Required to Suppress.... the exception goes away.

My RECEIVE code is as follows:

    DECLARE @conversationHandle UNIQUEIDENTIFIER;
    DECLARE @messageTypeName SYSNAME;

        RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingReceiveQueue;

    IF(@conversationHandle IS NOT NULL)
    BEGIN
        END CONVERSATION @conversationHandle;
    END

Answer 7

(If I change from Required to Suppress.... the exception goes away.)

 

The answer is very simple SQL Server Broker Service expect atomic transaction because suppress means you must run transaction as a unit of work. 

 

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscopeoption.aspx

 

Answer 8

So this is correct behavior?

I run into another problem though...

If I try to nest transactions as in

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress))
{

       using (TransactionScope ts2 = new TransactionScope(TransactionScopeOption.Suppress))
       {

                ts2.Complete()
       }
      
       ts.Complete()
}

And it fails everytime.... it does not rollback, actions stay executed against db ... i probably don't fully understand how the transactionscope works...

Answer 9

You have posted in the System.Transaction and COM+ Transaction forum so you will get another answer but System.Transaction nesting is managed by the TransactionScope class because all your transactions in that block are managed as one unit of work.  I think you need to check out System.Data transactions which performs atomic transaction including nesting if you use Transaction SavePoints just as T-SQL transactions.

Answer 10

Related links..

http://social.technet.microsoft.com/Forums/en-US/sqlservicebroker/thread/2db5bc25-57be-4b7a-ad4e-db2c3c5bc67a/

http://social.msdn.microsoft.com/forums/en-US/sqlservicebroker/thread/eb178071-bac2-4395-944b-b270957d9efb/

http://social.msdn.microsoft.com/forums/en-US/sqlservicebroker/thread/da93e91e-bf50-44fc-b2f0-cde5887e7d34/
http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/41023ce3-53d9-4ede-983b-b9973fa8737a

---

Nakul