SQL 查询通知并不总是适用于横向扩展设置 (SQL Server) [英] SQL Query Notifications do not always work in scaleout setup (SQL Server)

查看：73
本文介绍了SQL 查询通知并不总是适用于横向扩展设置 (SQL Server)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

SQL 查询通知在我们的环境中并不总是有效.它们似乎工作了一段时间，然后我们在 SqlDependency.Start(...) 方法调用上得到一个 ArgumentException(用于模拟的无效令牌 - 它不能被复制)异常，并且 SqlMessageBus 回退到使用轮询机制.之后，直到 SQL 消息总线被释放后，才会尝试 SQL 查询通知.

SQL Query Notifications do not always work in our environment. They seem to work for a little while, and then we get an ArgumentException (Invalid token for impersonation - it cannot be duplicated) exception on SqlDependency.Start(...) method call, and SqlMessageBus falls back to using polling mechanism. After that, SQL Query Notifications are not attempted until the SQL message bus has been disposed.

看起来，SqlMessageBus 并不总是看到 SqlDependency 已经启动并尝试再次启动它.

It seems that, SqlMessageBus doesn't always see that SqlDependency has already been started and tries to start it again.

在日志中(见下文)，我看到以下事件序列:

In the log (see below), I see the following sequence of events:

  • 启动 SQL 通知侦听器
  • SQL 通知侦听器已启动
  • 触发 SQL 通知更改
  • 启动 SQL 通知侦听器
  • SQL 通知侦听器已在运行
  • 设置 SQL 通知
  • 触发 SQL 通知更改
  • SQL 通知超时
  • 再次开始接收循环以处理更新
  • 启动 SQL 通知侦听器
  • 启动 SQL 通知侦听器时出错

环境:

  • .NET 4.5.2
  • Windows 7 上的 WinForms 客户端
  • 在 Windows Server 2012 R2 上的 IIS 8.5 中托管的 2 个应用服务器(使用 Windows 身份验证和模拟)
  • 负载均衡器
  • SignalR 2.2
  • SQL Server 2008 R2 SP3(使用 SQL Server 身份验证)

以下是捕获的日志的摘录.如果需要，我可以提供完整的日志.

The following is an excerpt from a captured log. I can supply the full log if needed.

...
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Starting SQL notification listener
    DateTime=2015-02-06T18:47:37.3054459Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SQL notification listener started
    DateTime=2015-02-06T18:47:37.3679593Z
...
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SqlReceiver last payload ID=0, new payload ID=1
    DateTime=2015-02-06T18:47:38.1805287Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Updated receive reader initial payload ID parameter=1
    DateTime=2015-02-06T18:47:38.1805287Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Payload 1 containing 1 message(s) received
    DateTime=2015-02-06T18:47:38.1805287Z
SignalR.ScaleoutMessageBus Information: 0 : OnReceived(0, 1, 1)
    DateTime=2015-02-06T18:47:38.1805287Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : 1 records received
    DateTime=2015-02-06T18:47:38.1961135Z
...
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : No records received while setting up SQL notification
    DateTime=2015-02-06T18:47:47.1184053Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Receive loop exiting
    DateTime=2015-02-06T18:47:47.1184053Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : SqlReceiver.Receive returned
    DateTime=2015-02-06T18:47:47.1184053Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : SQL notification change fired
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SQL notification details: Type=Change, Source=Data, Info=Update
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : Starting receive loop again to process updates
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Starting SQL notification listener
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SQL notification listener was already running
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Created DbCommand: CommandType=Text, CommandText=SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId, Parameters= [Name=PayloadId, Value=1]
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SqlReceiver last payload ID=1, new payload ID=2
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Updated receive reader initial payload ID parameter=2
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Payload 2 containing 1 message(s) received
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.ScaleoutMessageBus Information: 0 : OnReceived(0, 2, 1)
    DateTime=2015-02-06T18:48:13.1196355Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : 1 records received
    DateTime=2015-02-06T18:48:13.1196355Z
...
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Setting up SQL notification
    DateTime=2015-02-06T18:48:27.8859474Z
SignalR.SqlMessageBus Verbose: 0 : Created DbCommand: CommandType=Text, CommandText=SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId, Parameters= [Name=PayloadId, Value=3]
    DateTime=2015-02-06T18:48:27.8859474Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : No records received while setting up SQL notification
    DateTime=2015-02-06T18:48:27.8859474Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Receive loop exiting
    DateTime=2015-02-06T18:48:27.8859474Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : SQL notification change fired
    DateTime=2015-02-06T18:49:27.9200020Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SQL notification timed out
    DateTime=2015-02-06T18:49:27.9200020Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : Starting receive loop again to process updates
    DateTime=2015-02-06T18:49:27.9200020Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Starting SQL notification listener
    DateTime=2015-02-06T18:49:27.9200020Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SQL notification listener was already running
    DateTime=2015-02-06T18:49:27.9200020Z
...
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Setting up SQL notification
    DateTime=2015-02-06T18:50:51.8614352Z
SignalR.SqlMessageBus Verbose: 0 : Created DbCommand: CommandType=Text, CommandText=SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR].[Messages_0] WHERE [PayloadId] > @PayloadId, Parameters= [Name=PayloadId, Value=3]
    DateTime=2015-02-06T18:50:51.8614352Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : No records received while setting up SQL notification
    DateTime=2015-02-06T18:50:51.8614352Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Receive loop exiting
    DateTime=2015-02-06T18:50:51.8614352Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : SQL notification change fired
    DateTime=2015-02-06T18:51:58.0520336Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : SQL notification timed out
    DateTime=2015-02-06T18:51:58.0520336Z
SignalR.SqlMessageBus Information: 0 : Stream 0 : Starting receive loop again to process updates
    DateTime=2015-02-06T18:51:58.0520336Z
SignalR.SqlMessageBus Verbose: 0 : Stream 0 : Starting SQL notification listener
    DateTime=2015-02-06T18:51:58.0520336Z
SignalR.SqlMessageBus Error: 0 : Stream 0 : Error starting SQL notification listener: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Invalid token for impersonation - it cannot be duplicated.
   at System.Security.Principal.WindowsIdentity.CreateFromToken(IntPtr userToken)
   at System.Security.Principal.WindowsIdentity..ctor(SerializationInfo info)
   --- End of inner exception stack trace ---

Server stack trace: 
   at System.RuntimeMethodHandle.SerializationInvoke(IRuntimeMethodInfo method, Object target, SerializationInfo info, StreamingContext& context)
   at System.Runtime.Serialization.ObjectManager.CompleteISerializableObject(Object obj, SerializationInfo info, StreamingContext context)
   at System.Runtime.Serialization.ObjectManager.FixupSpecialObject(ObjectHolder holder)
   at System.Runtime.Serialization.ObjectManager.DoFixups()
   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(HeaderHandler handler, __BinaryParser serParser, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)
   at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(Stream serializationStream, HeaderHandler handler, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)
   at System.Runtime.Remoting.Channels.CrossAppDomainSerializer.DeserializeObject(MemoryStream stm)
   at System.Runtime.Remoting.Messaging.SmuggledMethodCallMessage.FixupForNewAppDomain()
   at System.Runtime.Remoting.Channels.CrossAppDomainSink.DoDispatch(Byte[] reqStmBuff, SmuggledMethodCallMessage smuggledMcm, SmuggledMethodReturnMessage& smuggledMrm)
   at System.Runtime.Remoting.Channels.CrossAppDomainSink.DoTransitionDispatchCallback(Object[] args)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart)
   at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)
   at Microsoft.AspNet.SignalR.SqlServer.ObservableDbOperation.StartSqlDependencyListener()
    DateTime=2015-02-06T18:51:58.0520336Z
...

有人见过这个问题吗?

我也在 GitHub 上的 SignalR 问题跟踪器中发布了这个问题

推荐答案

HttpContext.Current.User.Identity 为空时通常会抛出异常Invalid token for impersonation - it cannot be duplicated"在当前线程中，也许您应该在 SqlDependency.Start() 方法之前/之后检查它.

The exception "Invalid token for impersonation - it cannot be duplicated" is usually thrown when HttpContext.Current.User.Identity is empty in the current thread, maybe you should check it before / after the SqlDependency.Start() method.

HttpContext.Current 不会自然地通过 TPL(异步/等待方法)隐式创建的线程进行传播，也许您应该看看这一边.如果您的服务器端应用程序是 asp.net，请尝试在您的 应用设置.

HttpContext.Current don't naturally propagate over implicitly created threads by TPL (async / await methods), maybe you should have a look on this side. If your server-side app is asp.net, try to set aspnet:UseTaskFriendlySynchronizationContext to true in your appSettings.

这篇关于SQL 查询通知并不总是适用于横向扩展设置 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆