set CONTEXT_INFO select context_info(), locking

Imagine a Table T that is used as follows:

At time T0: SPID 1: begin transaction ... insert into T(...) values(...); ... commit;

At time T1 SPID 2: begin transaction select * from T where C=cval; ... commit;

Assume transaction isolation level set to serializable. Also Assume that SPID1 manages to take TABLOCKX on T prior to time T1 and also that SPID1 is doing a long transaction eventually making SPID2 waiting for TABLOCKX on T to be released.

Now, consider the same scenario using CONTEXT_INFO instead of T

At time T0: SPID 1: begin transaction ... SET CONTEXT_INFO=0xFF; ... end;

At time T1 SPID 2: begin transaction select context_info(); ... end;

Again, assume a transaction isolation level set to serializable.

I know CONTEXT_INFO is per connection. I do not assume SPID 1 and SPID 2 are communicating using CONTEXT_INFO. They are completely independent, assume they are using CONTEXT_INFO for separate purposes simultaniuosly.

Now, the select is performed without any wait due to a TABLOCKX. As a matter of fact I havn't been able to provoke any type of wait due to locking using CONTEXT_INFO.

This is fine with me since I need this behaviour. My problem is that even though CONTEXT_INFO should be a memory variable in sql server in a "SPID-represenation", it seems to be stored in a table called master.dbo.sysprocesses on MSSQL2000 (select context_info() will not work here), and in sys.sysprocesses on later releases of MSSQL Server.

Aren't these tables to act like all other tables with regards to locking or are these tables just 开发者_开发百科windows to variables not obeying the same rules as other tables?

Another thing that strikes me is that a TAB-type, IS-mode lock on spt_values is taken upcon set context_info and KEY-type, RangeS-S mode-locks upon read of sysprocesses. But what the real consequences of this is I have no grasp of since it's an internal object.

Please enlighten me.

Regards, Jens Nordenbro

• I was not clear enough, I know CONTEXT_INFO is per connection. I do not assume SPID 1 and SPID 2 are communicating using CONTEXT_INFO. They are completely independent, assume they are using CONTEXT_INFO for separate purposes. I just dread they need the same lock-resources. That's what the real question is about.
• What do you mean by HINT?

• CONTEXT_INFO is per connection
• sysprocesses is not a real table and you can't use a HINT (eg WITH TABLOCKX)

Edit, after other answer by OP:

sysprocesses does not have locks or contention that will affect your code. That is, your use of CONTEXT_INFO will be unaffected by whatever goes on internally with sysprocesses.

As for "real table"... it's a "fake table" as per "TableIsFake" test of OBJECTPROPERTY:

Table is not real. It is materialized internally on demand by the SQL Server Database Engine.