The NServiceBus documentation lists a benefit of SQL transport as:
Queues support competing consumers (multiple instances of same endpoint feeding off of same queue) so there is no need for distributor in order to scale out the processing
http://docs.particular.net/nservicebus/sqlserver/design
Who does NServiceBus prevent a message from being handled by multiple consumers if multiple consumers have subscribed to the same queue?
Does NServiceBus lock the entire table until the message is handled? Or is the message marked as 'being processed' ??
The SQL Transport uses very specific lock hints in order to lock a row and cause other competing threads to ignore any row currently locked.
From NServiceBus.SqlServer 2.2.0 (current version at the time I'm writing this) the SQL used, but reformatted by me, is:
WITH message AS
(
SELECT TOP(1) *
FROM [{Schema}].[{Queue}] WITH (UPDLOCK, READPAST, ROWLOCK)
ORDER BY [RowVersion] ASC
)
DELETE FROM message
OUTPUT deleted.Id, deleted.CorrelationId, deleted.ReplyToAddress,
deleted.Recoverable, deleted.Expires, deleted.Headers, deleted.Body;
It uses a Common Table Expression to limit the source data to the one row to return, then uses the following lock hints:
UPDLOCK - Hold the data under lock with intent to update it.READPAST - Ignore locked rows and fetch the next unlocked one.ROWLOCK - Force row-level locks and don't escalate to page locks or table locks.By executing the entire thing as a delete but then outputting the data about to be deleted, we can read the data, and if the transaction commits, the row is removed. Otherwise, if the transaction rolls back, then the lock is released and the row waits to be picked up by the next competing consumer.