SQL Server中CONTEXT_INFO的范围是什么？ [英] What is the scope of CONTEXT_INFO in SQL Server?

问题描述

出于审计/历史记录表的目的，我正在使用CONTEXT_INFO将用户名传递给删除触发器。我想了解CONTEXT_INFO的范围，以及是否要创建潜在的竞争条件。

I am using CONTEXT_INFO to pass a username to a delete trigger for the purposes of an audit/history table. I'm trying to understand the scope of CONTEXT_INFO and if I am creating a potential race condition.

我的每个数据库表都有一个存储的proc来处理删除操作。删除存储的过程将userId作为参数，并将CONTEXT_INFO设置为userId。然后，我的删除触发器获取CONTEXT_INFO，并使用它来更新审核表，该审核表指示谁删除了该行。

Each of my database tables has a stored proc to handle deletes. The delete stored proc takes userId as an parameter, and sets CONTEXT_INFO to the userId. My delete trigger then grabs the CONTEXT_INFO and uses that to update an audit table that indicates who deleted the row(s).

问题是，如果两个删除存储库中的存储库不同用户在同一时间执行，一个存储过程中设置的CONTEXT_INFO可以被另一个存储过程触发的触发器消耗吗？

The question is, if two deletes sprocs from different users are executing at the same time, can CONTEXT_INFO set in one of the sprocs be consumed by the trigger fired by the other sproc?

我已经看过这篇文章 http://msdn.microsoft.com/en-us/library/ms189252.aspx 但我不清楚SQL Server中的会话和批处理的范围，这对本文的帮助至关重要！

I've seen this article http://msdn.microsoft.com/en-us/library/ms189252.aspx but I'm not clear on the scope of sessions and batches in SQL Server which is key to the article being helpful!

我会发布代码，但简短此刻准时。

I'd post code, but short on time at the moment. I'll edit later if this isn't clear enough.

在此先感谢您的帮助。

推荐答案

上下文信息没有范围（就语言变量范围而言），并且绑定到会话生存期。设置后，上下文信息将保持设置的值，直到关闭连接（会话终止）或设置新的值为止。由于在会话上执行总是 顺序的，因此没有并发问题。

Context info has no scope (in the sense of language variables scope) and is bound to the session lifetime. Once set, the context info stay at the value set until the connection is closed (the session terminates) or until a new value is set. Since execution on a session is always sequential, there is no question of concurrency.

如果您在过程中设置了上下文信息，则任何触发器随后在该会话上执行的操作将看到新设置的上下文信息值。如您所建议的那样，在上下文信息中设置用户ID值并在触发器中使用它是上下文信息使用的典型示例，并且就并发而言是绝对安全的，因为基本上没有并发性。如果您计划在存储过程中设置上下文信息，然后由于所述过程中发生的删除而在运行触发器中依赖它，则您的批处理尚未完成，因此，根据链接的文章，您可以检索来自 sys.dm_exec_requests DMV或 CONTEXT_INFO（）函数的圆锥文本信息。它不会在 sys.dm_exec_sessions 中进行推送，只有在退出存储过程并完成发送到服务器的T-SQL批处理中的任何其他调用之后，该事件才会发生（ 请求）。

IF you set the context info in a procedure, any trigger subsequently executed on that session will see the newly set context info value. Setting the user id value in the context info, as you propose, and using it in triggers is the typical example of the context info use and is perfectly safe in regard to concurrency, since basically there is no concurrency to speak of. If you plan to set the context info in a stored procedure and then rely on it in a trigger that runs due to deletes that occur in the said procedure, then your batch did not finish yet so, according to the article you linked, you retrieve the conetxt info from the sys.dm_exec_requests DMV or from the CONTEXT_INFO() function. It will not yet be pushed in sys.dm_exec_sessions, that can only happen after you exit the stored procedure and finish any other call in the T-SQL batch sent to the server (the 'request').

这篇关于SQL Server中CONTEXT_INFO的范围是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！