Implementing message queue in SQL Server 2000

Hot Topic

How to install management studio tools?
SSIS will not start after SQL Server 2005 SP1 upgrade
loading image dynamically
Many-To-Many Self-Join
SSAS 2005 on Windows XP?
Urgent need for problem of increase in database size for ntext type fields
Data of type SQLBIT always 1??
P.S. Tasks > Export
SQL 2005 and SQL 2000, one Dev server, co-existence
small scale (light weight) SQL search engine (basically a stored procedure) to search through Products Table

SQL Server

How can we deal with unknown member in cubes for data mining?
SQL Server 2005 Pivot Statement Snytax error
SP1 Installer Can't Connect
64 Bit OLE vs. 32 Bit OLE
SQL 2000 and 2005 on same server
ORDER BY clause - newbie question
How to include SSIS into install file?
Neural Network Problem
XML Datatype Usage & Performance
Trouble finding Error Code 40 or 53 in BooksOnline

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

  • Francisco Lopez

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

    Alex

  • JohnReam

    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

  • BamaGal

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

    Alex

  • apander

    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

  • Metaxa777

    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

  • Nik02

    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

  • Millen Tomov

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

    Any other ways

    Alex

  • Mosha Pasumansky

    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

  • GrandmaGG

    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

  • hoogli

    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

  • Damodar

    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

  • iowaporter

    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



  • Vincent Rainardi

    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

  • Event666

    I thought about using the second table but hoped it could be achieved with the single one.
  • Implementing message queue in SQL Server 2000
Answer this question