Is MS SQL Server Service Broker Responding to Read Uncommitted Data?

Introduction

In this tip, I show how the Service Broker PostEventQueryNotification fires its trigger while the changed or inserted row does not show up in the result datatable.

Background

In some applications @ my work, we use Service Broker in combination with SqlDependency objects to notify all connected clients about changes/adds in entities. When using high load, we started to miss entities in the results. Thanks to logging, we found a cause.

The Use Case

We have a SQL Server 2014 database in read committed mode.

We use a SqlDependency query with a parameter value. We are only interested in table data which are newer than a given datetime. When processing this data, we take the newest datetime value from the resultset and set the SqlDependency again with that datatime as parameter value.

One application loads the contents of an Excel file and that results into 1,000 records being written in the database. Each record is stored as metadata in a separate table to which the SqlDependency is set up.

In the logging, we see this happening:

2019-05-20 16:09:31,254 [37] INFO Services.Notification.DBConnector SqlNotificationEventArgs: Info: Insert. Source: Data. Type: Change
2019-05-20 16:09:31,312 [37] INFO Services.Notification.DBConnector Nr rows in DepedencyTable: 0
2019-05-20 16:09:31,371 [39] INFO Services.Notification.DBConnector SqlNotificationEventArgs: Info: Insert. Source: Data. Type: Change
2019-05-20 16:09:31,396 [39] INFO Services.Notification.DBConnector Nr rows in DepedencyTable: 2

In normal cases, we see:

2019-05-20 16:09:31,153 [39] INFO Services.Notification.DBConnector SqlNotificationEventArgs: Info: Insert. Source: Data. Type: Change
2019-05-20 16:09:31,178 [39] INFO Services.Notification.DBConnector Nr rows in DepedencyTable: 1

That triggered us.

In the code, we saw we updated the datetime value with DateTime.UtcNow when no rows appeared in the resultset and that is how we missed the entity in the beginning.

So we changed the code to not update the datetime value and set up the SqlDependency and that is how the entity returned in the application.

One question remained. Why does the SqlDependency event get fired with no records as result?

In the datatable, we see the next 2 rows:

This means the SqlDependency event is fired @.254 according to our logging but record is available @.340 in the database, which leads to the conclusion that the PostNotificationQueryEvent of the Service Broker is fired before the record is committed in the table. So be aware of this feature.

Points of Interest

Without logging, we never would have found the cause of this.

We tested the application, database and notifying service on the same machine. So time differences between different computers are no issue in this test.

History

• Version 1.0: Initial tip