This is the second of three Workshops on Service Broker. Simple-Talk’s Workbench series are intended to be loaded into SQL Servers Management Studio, read and executed. They are intended to catapault the reader into familiarity with the subject by trying things out. Experiment and generally use them as a starting-off point with an aspect of SQL Server. The actual source code is in a downloadable file at the bottom of the article.<\/p>\n
In the first part of this workbench series, we covered the foundations: Setting up message types, contracts, queues, and services, and sending and waiting for messages. This second part extends on the first. We’ll get in to some of the catalog views that you can query to find out what Service Broker is up to, investigate how Service Broker handles transactions and locking, route our messages across databases, and process messages automatically with stored procedures. <\/p>\n
Once you’ve finished with this second workbench, you will have a complete understanding of all of the most common SSB features that you will use on projects again and again. The few remaining interesting features will be covered in the third installment of this mega-workbench, coming soon to a Simple-Talk near you… <\/p>\n
So with that, let’s jump in!<\/p>\n
\r\n--Set up new DB\r\n--To begin with, we'll create a database to work in.\r\nCREATE DATABASE Simple_Talk_SSB2\r\nGO\r\n\r\nUSE Simple_Talk_SSB2\r\nGO\r\n\r\n--Make sure that SSB is enabled\r\nALTER DATABASE Simple_Talk_SSB2\r\nSET ENABLE_BROKER\r\nWITH ROLLBACK IMMEDIATE\r\nGO\r\n\r\n--Create a master key\r\nCREATE MASTER KEY\r\nENCRYPTION BY PASSWORD = 'onteuhoeu'\r\nGO\r\n<\/pre>\nSetup<\/h3>\n
Get a few basics in place: A message type without validation (meaning that we can send any binary data), a contract based on the message type, a couple of queues, and a couple of services. See the last workbench<\/a> for details on all of this. <\/p>\n
\r\nCREATE MESSAGE TYPE BLOB\r\nVALIDATION = NONE\r\nGO\r\n\r\nCREATE CONTRACT BLOB_Contract\r\n(BLOB SENT BY ANY)\r\nGO\r\n\r\nCREATE QUEUE BLOB_Queue_Init\r\nCREATE QUEUE BLOB_Queue_Target\r\nGO\r\n\r\nCREATE SERVICE BLOB_Service_Init\r\nON QUEUE BLOB_Queue_Init\r\n(BLOB_Contract)\r\n\r\nCREATE SERVICE BLOB_Service_Target\r\nON QUEUE BLOB_Queue_Target\r\n(BLOB_Contract)\r\nGO\r\n<\/pre>\nThe Conversation Endpoints View<\/h3>\n
The
sys.conversation_endpoints<\/code> view is the primary catalog view that you can use to get information about which conversations exist in the current database, and their current status. The view provides quite a bit of information about each conversation, and we can use it to gain some insights into the internals of Service Broker. <\/p>\nSince we have, presumably, just created the database, querying the view at this point should return an empty set of rows: <\/p>\n
\r\nSELECT *\r\nFROM sys.conversation_endpoints\r\nGO\r\n<\/pre>\nAlthough there are almost 30 columns exposed by the view, for the sake of this workbench we’ll consider only a few: <\/p>\n
\n
conversation_handle <\/code><\/dt>\n- Conversation (or dialog) handles were discussed in detail in the previous workbench. This column is effectively the primary key for the view. The view will have one row for each handle generated, whether by an initiator or a target. <\/dd>\n
conversation_id <\/code><\/dt>\n- Each dialog has both an initiator and a target, with different dialog handles. This column is a GUID which relates rows for initiators with the rows for their targets. <\/dd>\n
is_iniator <\/code><\/dt>\n- This is a bit column that will return
1<\/code> if the initiating handle was the one represented in theconversation_handle<\/code> column, or0<\/code> otherwise. <\/dd>\nconversation_group_id <\/code><\/dt>\n- Conversation groups and this column will be discussed in detail shortly. <\/dd>\n
state_desc <\/code><\/dt>\n- Any given side of a dialog, at any given time, can be in any of several states. These include “CONVERSING”, which means that the conversation is open for messages, or one of two “DISCONNECTED” states that indicate that one or both parties have called
END COVERSATION<\/code>. <\/dd>\nfar_service<\/code> andfar_broker_instance <\/code><\/dt>\n- Both of these columns will be discussed when we get into routing. <\/dd>\n<\/dl>\n
To look at some of the data exposed by the view, we’ll first have to start a conversation. <\/p>\n
\r\n--Start a conversation\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Init\r\nTO SERVICE 'BLOB_Service_Target'\r\nON CONTRACT BLOB_Contract\r\nWITH ENCRYPTION=OFF\r\nGO\r\n<\/pre>\nNow that a conversation has been initiated, the following query should return one row: <\/p>\n
\r\nSELECT \r\n conversation_handle,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\n<\/pre>\nRunning this query, we see that for the one row returned,
is_initiator<\/code> is set to 1 – which makes sense, given that we have just initiated a conversation. But why was only one row returned? If you’re following along, you might recall that a dialog has two sides: an initiator and a target. And each of these sides gets their own conversation handle. Furthermore, you might recall that I just mentioned that the conversation handle is effectively the primary key for the Conversation Endpoints view. So wouldn’t two rows make more sense? <\/p>\nThe value of the
state_desc<\/code> column should give you a hint. It will read “STARTED_OUTBOUND”. Initiating a dialog, as it turns out, only starts up the initiator’s end of things. The target’s side of the conversation doesn’t actually exist until a message joins the fray and there is something for a target to receive… <\/p>\n\r\n--Get the conversation handle\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nSELECT @h = conversation_handle\r\nFROM sys.conversation_endpoints\r\n\r\n--Send a message\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Hello Simple-Talk, pt. 2!'))\r\n\r\n--Run the query again\r\nSELECT \r\n conversation_handle,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\nGO\r\n<\/pre>\nNow we see both the initiator and the target, and for both rows the
state_desc<\/code> column’s value is “CONVERSING”. The conversation has started and all is well. Note theconversation_id<\/code> column – its value is the same for both the initiator and the target. Should you ever need to find the target’s conversation handle based on the initiator handle, or the other way around, this column is key. <\/p>\nNow that we’ve seen how a conversation starts, let’s take a peek at what happens when it ends. <\/p>\n
--First, end the target conversation\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\n;RECEIVE @h=conversation_handle\r\nFROM BLOB_Queue_Target\r\n\r\nEND CONVERSATION @h\r\n\r\n--What happened?\r\nSELECT \r\n conversation_handle,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\nGO<\/pre>\nAfter running the preceding batch, you’ll notice that the target’s side of the conversation is now “CLOSED”. The initiator, on the other hand, has now gone into the “DISCONNECTED_INBOUND” state. As mentioned in the previous workbench, a disconnected side of a conversation can only retrieve any remaining messages and then end the conversation itself. So let’s do that. <\/p>\n
\r\n--End the initiator's side\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\n;RECEIVE @h=conversation_handle\r\nFROM BLOB_Queue_Init\r\n\r\nEND CONVERSATION @h\r\n\r\n--What happened?\r\nSELECT \r\n conversation_handle,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\nGO\r\n\r\n\r\n<\/pre>\nNow you might notice something interesting and wholly unexpected. The initiator’s side of the conversation is gone, no longer showing up in the result set for the view. But the target is still there, and still closed. <\/p>\n
As it turns out, the target will sit there in a closed state for “about half an hour,” according to Roger Wolter, the architect behind Service Broker. This is a security precaution, he says in his chapter of “Inside Microsoft SQL Server 2005: T-SQL Programming,” in order to prevent replay attacks. <\/p>\n
That topic is quite beyond the scope of this workbench, but it’s important that you know that the target side does stay around for a while even after the conversation has been closed. We’ll discuss why that’s so important in the next workbench, so stay tuned… <\/p>\n
Conversation Groups<\/h3>\n
As we’ve now observed several times throughout the course of the two workbenches, each time a dialog is started a conversation handle is automatically generated by Service Broker. But what you may not have noticed is that in addition to the conversation handle, there is also a second automatically generated GUID, the conversation group ID: <\/p>\n
\r\n--Start a conversation\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Init\r\nTO SERVICE 'BLOB_Service_Target'\r\nON CONTRACT BLOB_Contract\r\nWITH ENCRYPTION=OFF\r\n\r\nSELECT \r\n conversation_handle,\r\n conversation_group_id,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\nWHERE conversation_handle = @h\r\nGO\r\n\r\n\r\n\r\n<\/pre>\nThe first thing that makes this GUID different than the conversation handle is that it doesn’t have to be auto-generated. You can tell Service Broker which ID to use: <\/p>\n
\r\n--Start a conversation\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Init\r\nTO SERVICE 'BLOB_Service_Target'\r\nON CONTRACT BLOB_Contract\r\nWITH\r\n RELATED_CONVERSATION_GROUP = '0F0F0F0F-0E0E-0D0D-0C0C-0B0B0B0B0B0B',\r\n ENCRYPTION=OFF\r\n\r\nSELECT \r\n conversation_handle,\r\n conversation_group_id,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\nWHERE conversation_handle = @h\r\nGO<\/pre>\nRunning this, you’ll see that the instantly-identifiable GUID I’ve created is used as the
conversation_group_id<\/code>. But that’s just the initiator end of the conversation. What about the target? <\/p>\n\r\nDECLARE @h UNIQUEIDENTIFIER, @i UNIQUEIDENTIFIER\r\n\r\nSELECT \r\n @h = conversation_handle,\r\n @i = conversation_id\r\nFROM sys.conversation_endpoints\r\nWHERE conversation_group_id = '0F0F0F0F-0E0E-0D0D-0C0C-0B0B0B0B0B0B'\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'What''s my group?'))\r\n\r\nSELECT\r\n conversation_handle,\r\n conversation_group_id,\r\n conversation_id,\r\n is_initiator,\r\n state_desc\r\nFROM sys.conversation_endpoints\r\nWHERE conversation_id = @i\r\nGO<\/pre>\nRunning the preceding batch, you’ll see that the target conversation is not enlisted in the same group as the initiator. Given the decoupled nature of initiators and targets we’ve already seen in the world of Service Broker, this should come as no huge shock, but there are some other reasons to keep these separate that I will show you in the section on locking and blocking. <\/p>\n
Taking a Better Look at RECEIVE<\/h3>\n
To see the basic utility of conversation groups, we’ll first have to take a step back and look at the
RECEIVE<\/code> statement in a bit more depth than in the last workbench<\/a>. Last time, we sawRECEIVE<\/code> in its barest form – the statement itself and a column list. The optionalWHERE<\/code> clause allows you to filter based on a given conversation handle or conversation group ID. <\/p>\nThe following batch first uses
RECEIVE<\/code> with a conversation handle, then sends a message back to the initiator, where it is received based on the conversation group ID. <\/p>\n\r\n--First, get the target handle\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nSELECT \r\n @h = conversation_handle\r\nFROM sys.conversation_endpoints\r\nWHERE \r\n conversation_id =\r\n (\r\n SELECT conversation_id\r\n FROM sys.conversation_endpoints\r\n WHERE conversation_group_id = '0F0F0F0F-0E0E-0D0D-0C0C-0B0B0B0B0B0B'\r\n )\r\n AND is_initiator = 0\r\n\r\n--Now, receive the message, and end the conversation\r\n;RECEIVE *\r\nFROM Blob_Queue_Target\r\nWHERE conversation_handle = @h\r\n\r\n;END CONVERSATION @h\r\n\r\n--Now use the conversation group ID to receive \r\n--the end conversation message\r\n;RECEIVE *\r\nFROM Blob_Queue_Init\r\nWHERE conversation_group_id = '0F0F0F0F-0E0E-0D0D-0C0C-0B0B0B0B0B0B'\r\n\r\n--Finally, since we're good Service Broker users, end\r\n--the initiating conversation...\r\nSELECT \r\n @h = conversation_handle\r\nFROM sys.conversation_endpoints\r\nWHERE conversation_group_id = '0F0F0F0F-0E0E-0D0D-0C0C-0B0B0B0B0B0B'\r\n\r\nEND CONVERSATION @h\r\nGO<\/pre>\nNow that we’ve seen the power of the
RECEIVE<\/code> statement’sWHERE<\/code> clause, it should be easy to see how it can be put to good use in conjunction with a conversation group ID. Suppose that for a certain task, you want to start several distinct dialogs, but get back all of the responses using a singleRECEIVE<\/code> statement. SinceRECEIVE<\/code>‘s WHERE clause only supports the equality predicate, you can’t ask for multiple conversations at once. You can instead group all of the conversations using a single conversation group, and use that to get all of the responses. <\/p>\nThis same pattern gets even more powerful if you’re creating multiple dialogs across more than one service or queue. To see what that looks like, we’ll create a second target queue and a corresponding service: <\/p>\n
\r\nCREATE QUEUE BLOB_Queue_Target2\r\n\r\nCREATE SERVICE BLOB_Service_Target2\r\nON QUEUE BLOB_Queue_Target2\r\n(BLOB_Contract)\r\nGO<\/pre>\nDialogs can now be started between the initiator and both queues, using the same conversation group id: <\/p>\n
\r\n--Send to the BLOB_Service_Target\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Init\r\nTO SERVICE 'BLOB_Service_Target'\r\nON CONTRACT BLOB_Contract\r\nWITH\r\n RELATED_CONVERSATION_GROUP = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE',\r\n ENCRYPTION=OFF\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'First Send'))\r\n\r\n\r\n--Send to the BLOB_Service_Target2\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Init\r\nTO SERVICE 'BLOB_Service_Target2'\r\nON CONTRACT BLOB_Contract\r\nWITH\r\n RELATED_CONVERSATION_GROUP = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE',\r\n ENCRYPTION=OFF\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Second Send'))\r\n\r\n\r\n--Get the endpoint data for the conversation group\r\nSELECT\r\n conversation_handle,\r\n conversation_group_id,\r\n conversation_id,\r\n is_initiator,\r\n state_desc,\r\n far_service\r\nFROM sys.conversation_endpoints\r\nWHERE conversation_group_id = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'\r\nGO\r\n<\/pre>\nLooking at the output of the query from the Conversation Endpoints view, you’ll see that I’ve added a new column into the mix:
far_service<\/code>. This column tells us what service the dialog is targeting, and you should see two dialogs, each conversing to different services but both on the same conversation group. <\/p>\nEach of the targets can receive messages as usual, and send them back to the initiator: <\/p>\n
\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\n;RECEIVE @h = conversation_handle\r\nFROM BLOB_Queue_Target\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Back at you from Queue_Target'))\r\n\r\n;RECEIVE @h = conversation_handle\r\nFROM BLOB_Queue_Target2\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Back at you from Queue_Target2'))\r\nGO<\/pre>\nThe initiator can now receive both messages that we’ve sent back, using only the conversation group ID – despite the fact that the messages are on different conversations. <\/p>\n
\r\nRECEIVE *\r\nFROM BLOB_Queue_Init\r\nWHERE conversation_group_id = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'\r\nGO<\/pre>\nConversation Group Locking<\/h3>\n
We’ve now seen the power of conversation groups as a means by which to introduce some logical organization to sets of conversations. But conversation groups play a much bigger role in the overall Service Broker infrastructure. <\/p>\n
Since the Service Broker is a database engine technology and supports all of the same ACID rules as any other data in your database, it should come as no surprise to hear that it uses locking (and, as a consequence, blocking) to achieve isolation. One might, at first glance, assume that the granularity of the locks will be at the conversation or dialog level. But the designers of Service Broker realized that some processes are much more distributed than a single conversation – so locking instead occurs at the granularity of conversation groups. <\/p>\n
If you’ve been following along and running the examples, you should have two conversing dialogs at this point, each with an initiator on the same conversation group. We can use these dialogs to examine the ins and outs of conversation group locking. <\/p>\n
First we’ll send a message from the Target2 service to the initiator: <\/p>\n
\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nSELECT @h = conversation_handle\r\nFROM sys.conversation_endpoints\r\nWHERE \r\n conversation_id = \r\n (\r\n SELECT conversation_id\r\n FROM sys.conversation_endpoints\r\n WHERE far_service = 'BLOB_Service_Target2'\r\n )\r\n AND is_initiator = 0\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Hello again, from Queue_Target2'))\r\nGO<\/pre>\nTo show the effects of locking, open a new SSMS window, begin a transaction and do a
RECEIVE<\/code> for the conversation group: <\/p>\n\r\n--- RUN THIS IN A NEW WINDOW!\r\nBEGIN TRAN\r\n\r\n;RECEIVE *\r\nFROM BLOB_Queue_Init\r\nWHERE conversation_group_id = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'\r\n---\r\n<\/pre>\nBack in this window, let’s try sending a message to the Target service: <\/p>\n
\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nSELECT @h = conversation_handle\r\nFROM sys.conversation_endpoints\r\nWHERE\r\n conversation_group_id = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'\r\n AND far_service = 'BLOB_Service_Target'\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Hi, Queue_Target'))\r\nGO\r\n\r\n--Once you get sick of waiting for this to finish, go ahead\r\n--and hit the Stop button.\r\n\r\n<\/pre>\nAs you can see, we’re blocked, even though we’re sending a message on a different conversation than the one that we’ve received a message on. The net effect is that the entire group represents a single transactional unit – a powerful concept, if you need to do several subtasks as part of some bigger piece of work. <\/p>\n
But now for the cool part. The conversation group that we’re working with is locked, but remember that a dialog has two sides, each of which operates under its own conversation group. This means that even when one side is locked, the other side is free to continue working, and even free to continue sending messages: <\/p>\n
\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nSELECT @h = conversation_handle\r\nFROM sys.conversation_endpoints\r\nWHERE \r\n conversation_id = \r\n (\r\n SELECT conversation_id\r\n FROM sys.conversation_endpoints\r\n WHERE far_service = 'BLOB_Service_Target2'\r\n )\r\n AND is_initiator = 0\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Another hello from Queue_Target2'))\r\nGO<\/pre>\nWe’ve now sent a message. Let’s take a peek and see if it showed up: <\/p>\n
\r\nSELECT *\r\nFROM BLOB_Queue_Init WITH (NOLOCK)\r\nGO\r\n<\/pre>\nRunning the preceding query should return one row, for the message that we just sent. The
NOLOCK<\/code> hint is necessary, in order to get around the locked messages, still held by the transaction in the other window. <\/p>\nWhat do you think will happen if we try to do a second
RECEIVE<\/code> outside the scope of the transaction? <\/p>\n\r\nRECEIVE *\r\nFROM BLOB_Queue_Init\r\nWHERE conversation_group_id = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'\r\n<\/pre>\nIf you’ve just run the preceding query, you might be surprised by the results. We have a message in the queue for the specified conversation group, and the conversation group is locked by the transaction, so shouldn’t we see some blocking rather than an empty result set? <\/p>\n
To help explain this phenomenon, initiate a new conversation, from the Target service to the Init service, and send a message: <\/p>\n
\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Target\r\nTO SERVICE 'BLOB_Service_Init'\r\nON CONTRACT BLOB_Contract\r\nWITH ENCRYPTION=OFF\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Initiating from Target'))\r\n\r\n--What's in the queue now?\r\nSELECT *\r\nFROM BLOB_Queue_Init WITH (NOLOCK)\r\nGO\r\n<\/pre>\nNow there are two messages in the queue, each belonging to different conversation groups. What will be the result, then, of a
RECEIVE<\/code> with noWHERE<\/code> clause? <\/p>\n\r\nRECEIVE *\r\nFROM BLOB_Queue_Init\r\nGO<\/pre>\nThis
RECEIVE<\/code> is not blocked – and that’s a good thing, because we want multiple readers to be able to work with the queue simultaneously. What Service Broker actually does internally as part of theRECEIVE<\/code>, is use the equivalent of aREADPAST<\/code> hint. The locked conversation groups are skipped, thereby greatly increasing concurrency. <\/p>\nThere is one other side-effect of the way Service Broker handles conversation group locking. To prepare, first go back to the other window and commit the transaction: <\/p>\n
\r\n--- RUN THIS IN THE OTHER WINDOW\r\nCOMMIT\r\n---\r\n<\/pre>\nNow start another dialog and send a message to the Init queue: <\/p>\n
\r\nDECLARE @h UNIQUEIDENTIFIER\r\n\r\nBEGIN DIALOG CONVERSATION @h\r\nFROM SERVICE BLOB_Service_Target\r\nTO SERVICE 'BLOB_Service_Init'\r\nON CONTRACT BLOB_Contract\r\nWITH ENCRYPTION=OFF\r\n\r\n;SEND ON CONVERSATION @h\r\nMESSAGE TYPE BLOB\r\n(CONVERT(varbinary, 'Initiating from Target... Again'))\r\n\r\n--What's in the queue now?\r\nSELECT *\r\nFROM BLOB_Queue_Init\r\nGO\r\n<\/pre>\nWe once again have two messages in the queue, both on separate conversation groups. But this time the
NOLOCK<\/code> hint wasn’t needed for theSELECT<\/code> – no transactions are active and neither of the conversation groups are locked. <\/p>\nSo once again we pose an all-important question: In this situation, how should a
RECEIVE<\/code> with noWHERE<\/code> clause behave? <\/p>\n\r\nRECEIVE *\r\nFROM BLOB_Queue_Init\r\nGO\r\n<\/pre>\nRunning the
RECEIVE<\/code>, you’ll see that only a single message is returned. This doesn’t mean thatRECEIVE<\/code> can’t return multiple messages at a time – it can, and as shown in the previous workbench it supports the TOP clause. What we’re actually seeing in this case isRECEIVE<\/code> only returning messages from a single conversation group at a time. <\/p>\nYet again, this is an effort by the Service Broker team to promote high concurrency. If you have a farm of workers, each pulling tasks off of a central Service Broker queue, this single group at a time behavior ensures that each worker takes at most a single task, and at the same time the
READPAST<\/code> guarantees that workers don’t block each other. The result is a high performance and highly concurrent queuing system – exactly what we want from something like Service Broker. <\/p>\nIn the next installment of this workbench I’ll discuss worker farms and considerations for read performance in quite a bit more depth. Until then, you should now have a good feeling for how Service Broker handles locking and blocking. <\/p>\n
Message Activation <\/h3>\n