Implementing message queue in SQL Server 2000

Hot Topic

Problem: Reporting using Report Model
Excel headers - using SimplePageHeaders deviceinfo setting
Export Problem...very urgent
How can I turn off the encrypted login feature?
How to change the chart scale in my desire intervals?
Regular Expressions in SQL Server 2005
Data Flow limitation?
SQL Update statement
multiple-step ole db error
System.Data.Sql

SQL Server

Multiple devices for a subscription
RDA in SQL Server Express
Simple backup job failing
Easy Reporting
interactive sort in group
AutoClose
AS2005 September CTP Errors when Processing Cube
Loading multiple files
problem with lookup ,is this a bug?
Cannot read the next data row for the data set

Implementing message queue in SQL Server 2000

I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:

[MessageId] [uniqueidentifier] NOT NULL,
[MessageType] [uniqueidentifier] NOT NULL,
[Status] [tinyint] NOT NULL,
[SubmittedTime] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[DispatchedTime] [datetime] NULL,
[CompletedTime] [datetime] NULL,
[MessageData] [image] NULL

This is how I retrieve the next message for processing:

SELECT TOP 1 *
FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus
ORDER BY [StartTime]

and mark it as being processed:

UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId

After message has been processed I delete it from the queue:

DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId

All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.

I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:

SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)

But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this

Thank you,
Alex

Answer this question

Implementing message queue in SQL Server 2000

  • CoolCookie

    I thought about Service Broker. What implications can I run into Performance, scalability How hard is it to learn and implement

    Alex

  • KMLee

    OR, another option that might be better than UPDATE would be to insert the row into a second table within the DELETE trigger.  Then you can delete from that other table when you're done.  If any rows sit in there for too long, you can drop them back into the queue table... I think that would probably work a bit better.  The great thing about the DELETE is that SQL Server will not delete any given row twice -- so you're letting SQL Server handle the locking semantics, instead of trying to fix it with hints, etc.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    What happens if message processing fails in the middle (for instance, app server goes down) The message is lost.

    Any other ways

    Alex

  • Gaurav Khera

    What happens if message processing fails in the middle (for instance, app server goes down) The message is lost.

    Any other ways

    Alex

  • masri

    1. I have an index on [StartTime].
    2. I was experimenting with different locks: ROW, PAGE etc.
    3. I use UPDLOCK so the same message will not be retrieved more than once for processing.
    4. Snapshot isolation level is not available in SQL 2000.
    5. Workload - 5000+ messages in the queue.

    Alex

  • SergeyKokorin

    Different query optimization techniques   There were various updates/changes made that can alter the behavior of some code...
     
    However, if you're using 2005 you should use Service Broker instead.  No reason to reinvent the wheel on that one.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    I just tested the same code with SQL Server 2005 and there was no deadlocks! Can someone confirm/explain this

    Alex

  • Yifung Lin - MSFT

    True.  You could try an UPDATE instead of a DELETE -- add an "in process" flag and update where that flag is not set.  Then, later, come back and actually delete the row.  You'll have to add a process started date or something along those lines so that you can figure out which ones have taken too long (thereby indicating a failure) and need to be re-processed.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    What happens if message processing fails in the middle (for instance, app server goes down) The message is lost.

    Any other ways

    Alex

  • softcore

    2: Add a WHERE clause to your RECEIVE FROM statement, and filter on the SERVICE_CONTRACT_NAME (or ID) column.
     
     
    As for performance, there are various tweaks for that too.  You might want to grab a copy of Roger Wolter's book:
     
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    Scalability could be better, but performance is definitely not. I just ran some tests - Service Broker vs. my queue implementation: on 10000 messages Service Broker took 8min 13sec, my queue - 6min 51sec.

    A couple more questions:
    1. How do I set message priority
    2. If the service is defined with multiple contracts is there a way to receive a message with a specific contract

    Alex

  • tapic

    Scalability could be better, but performance is definitely not. I just ran some tests - Service Broker vs. my queue implementation: on 10000 messages Service Broker took 8min 13sec, my queue - 6min 51sec.

    A couple more questions:
    1. How do I set message priority
    2. If the service is defined with multiple contracts is there a way to receive a message with a specific contract

    Alex

  • Killa

    Alex I have some questions.
    Do you have any indexes defined on your table
    Why do you use ROWLOCK
    Why do you use UPDLOCK
    Is Snapshot Isolation disbled on SQL 2005 on which you have tried this
    And could you also describe your workload



  • A_Martin

    The best way I found to do this in SQL Server 2000 was to actually DELETE the row, and retrieve the data using a trigger -- SELECT the data back from the 'deleted' table in the trigger.
     
    You can use SET ROWCOUNT 1 so that you delete only one row at a time.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:

    [MessageId] [uniqueidentifier] NOT NULL,
    [MessageType] [uniqueidentifier] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [SubmittedTime] [datetime] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [DispatchedTime] [datetime] NULL,
    [CompletedTime] [datetime] NULL,
    [MessageData] [image] NULL

    This is how I retrieve the next message for processing:

    SELECT TOP 1 *
    FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST)
    WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus
    ORDER BY [StartTime]

    and mark it as being processed:

    UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId

    After message has been processed I delete it from the queue:

    DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId

    All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.

    I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:

    SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)

    But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this

    Thank you,
    Alex

  • Richard Timbers

    The documentation says:

    "The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id."

    Looks like 2 is impossible unless I am missing something.

    Alex

  • ASP.NETMSDN.COM

    I thought about using the second table but hoped it could be achieved with the single one.

  • Runamuk

    I just tested the same code with SQL Server 2005 and there was no deadlocks! Can someone confirm/explain this

    Alex

  • Sanguin Developers

    Yes, performance and scalability would certainly be implications -- BETTER performance and scalability :-)
     
    Very easy to learn.  Check out the samples that come with SQL Server; the "hello world" Service Broker sample will show you enough for the scenario you're describing.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    I thought about Service Broker. What implications can I run into Performance, scalability How hard is it to learn and implement

    Alex
  • Implementing message queue in SQL Server 2000
Answer this question