Reusing dialogs causes blocking

I was looking at a means of reusing dialogs.

The attempt I tried was looking up an existing dialog in the conversation_endpoints.

However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.

select top 1 conversation_handle

from sys.conversation_endpoints ce

join sys.services s on s.service_id = ce.service_id

join sys.service_contracts c on c.service_contract_id = ce.service_contract_id

where s.name = 'jobStats'

and ce.far_service = 'jobStats'

and (ce.far_broker_instance = @.targetBroker OR @.targetBroker = 'CURRENT DATABASE')

and ce.state IN ('SO','CO')

and ce.is_initiator = 1

and (ce.conversation_group_id = @.conversation_group_id )--or @.conversation_group_id is null)

and c.name = @.contractName

Even a R/O scan will block behind an X lock in read-commited mode. Use the READPAST hint to skip locked records.

HTH,
~ Remus

|||Having a moment, Tony R pointed out the use of readpast