Click here to Skip to main content
15,114,302 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 21 May 2019

Stats

2.2K views

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

Rate me:
Please Sign up or sign in to vote.
2.00/5 (1 vote)
21 May 2019CPOL2 min read
When using SqlDependency, is it possible that a detected change leads to 0 rows in the SqlDependency results?

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:

id

notification
_types_id

parent
_business
_id
business
_types_id
business
_id
facilities
_id
users
_id
client
_guid
date_
created
date
_received
507422 1 11642 8 134023 1 2 2100174291 2019-05-20 14:09:31.340 2019-05-20 14:09:31.340
507421 1 11642 8 134022 1 2 2100174291 2019-05-20 14:09:31.217 2019-05-20 14:09:31.217

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Herman<T>.Instance
Software Developer (Senior)
Netherlands Netherlands
I started in the eightees with Basic and QuickBasic. When the ninetees started dBase was my thing. Close to the millenium years I swapped to C++ and since 2003 it is only programming in c#.

There is one thing about me.
Not that kind of OO guy and definately not an ORM-fan. I know a database to well to give the importance of data out in the hands of a tool.

Comments and Discussions

 
Questionstrange i am not sure about that Pin
Bluemods - MOD APK30-Aug-19 4:01
professionalBluemods - MOD APK30-Aug-19 4:01 
AnswerRe: strange i am not sure about that Pin
Herman<T>.Instance7-Nov-19 1:48
MemberHerman<T>.Instance7-Nov-19 1:48 
QuestionConfused 😕 Pin
Safe Milli23-May-19 20:37
MemberSafe Milli23-May-19 20:37 
AnswerRe: Confused &#128533; Pin
Herman<T>.Instance23-May-19 22:34
MemberHerman<T>.Instance23-May-19 22:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.