Rebus.SqlServer may behave incorrectly on databases with READ_COMMITTED_SNAPSHOT option set to ON

[karelg]

We are currently using Rebus with Rebus.SqlServer transport on a database where _READ_COMMITTEDSNAPSHOT option is set to ON. Everything works fine so long we are using only single process for consuming events, but things will broke as soon as we try to add additional processes. We think the problem is related to ROWLOCK and READPAST hints, that Rebus.SqlServer relies on dequeuing messages, but unfortunately are not working correctly with SNAPSHOT isolation level, that is used now instead of Rebus.SqlServer's default READ COMMITED isolation level (cause of _READ_COMMITTEDSNAPSHOT ON database option). One possible solution to this problem might be to add an additional table hint READCOMMITTEDLOCK to the Rebus.SqlServer dequeue SQL command. You can read more about READCOMMITTEDLOCK table hint at Microsofts own documentation:

Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using locking. The Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

[mookid8000]

Rebus.SqlServer 5.0.0-b9 uses the READCOMMITTEDLOCK lock hint, which (at least on my machine) seems to work properly, also when running the database with snapshot isolation enabled.

Thanks for reporting this issue 👍