What is the scope of CONTEXT_INFO in SQL Server?

Question

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.

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).

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?

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.

Thanks in advance for any help.

Answer 1

I've used this exact method for auditing at one client site and they've been using it heavily for close to 6 months now with no problems.

The context information is scoped to the current connection for the current batch and any batches that start after the current batch has completed. Two users in your environment would either not be on the same connection, or if there is connection sharing they would still have their own values if they overlapped at all. If one came after the other then the second one would overwrite the first, but it would have been done with it by then anyway. At least this is my understanding of how it works. You can look up MARS (Multiple Active Result Sets) for more information on it.

Answer 2

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.

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').