SSRS Code Shared Variables and Simultaneous Report Execution

Question

We have some SSRS reports that are failing when two of them are executed very close together.

I've found out that if two instances of an SSRS report run at the same time, any Code variables declared at the class level (not inside a function) can collide. I suspect this may be the cause of our report failures and I'm working up a potential fix.

The reason we're using the Code portion of SSRS at all is for things like custom group and page header calculation. The code is called from expressions in TextBoxes and returns what the current label should be. The code needs to maintain state to remember what the last header value was in order return it when unknown or to store the new header value for reuse.

Note: here are my resources for the variable collision problem:

The MSDN SSRS Forum:

Because this uses static variables, if two people run the report at the exact same moment, there's a slim chance one will smash the other's variable state (In SQL 2000, this could occasionally happen due to two users paginating through the same report at the same time, not just due to exactly simultaneous executions). If you need to be 100% certain to avoid this, you can make each of the shared variables a hash table based on user ID (Globals!UserID).

Embedded Code in Reporting Services:

... if multiple users are executing the report with this code at the same time, both reports will be changing the same Count field (that is why it is a shared field). You don’t want to debug these sorts of interactions – stick to shared functions using only local variables (variables passed ByVal or declared in the function body).

I guess the idea is that on the report generation server, the report is loaded and the Code module is a static class. If a second clients ask for the same report as another quickly enough, it connects to the same instance of that static class. (You're welcome to correct my description if I'm getting this wrong.)

So, I was proceeding with the idea of using a hash table to keep things isolated. I was planning on the hash key being an internal report parameter called InstanceID with default =Guid.NewGuid().ToString().

Part way through my research into this, though, I found that it is even more complicated because Hashtables aren't thread-safe, according to Maintaining State in Reporting Services.

That writer has code similar to what I was developing, only the whole thread-safe thing is completely outside my experience. It's going to take me hours to research all this and put together sensible code that I can be confident of and that performs well.

So before I go too much farther, I'm wondering if anyone else has already been down this path and could give me some advice. Here's the code I have so far:

Private Shared Data As New System.Collections.Hashtable()

Public Shared Function Initialize() As String
   If Not Data.ContainsKey(Parameters!InstanceID.Value) Then
      Data.Add(Parameters!InstanceID.Value, New System.Collections.Hashtable())
   End If
   LetValue("SomethingCount", 0)
   Return ""
End Function

Private Shared Function GetValue(ByVal Name As String) As Object
   Return Data.Item(Parameters!InstanceID.Value).Item(Name)
End Function

Private Shared Sub LetValue(ByVal Name As String, ByVal Value As Object)
   Dim V As System.Collections.Hashtable = Data.Item(Parameters!InstanceID.Value)
   If Not V.ContainsKey(Name) Then
      V.Add(Name, Value)
   Else
      V.Item(Name) = Value
   End If
End Sub

Public Shared Function SomethingCount() As Long
   SomethingCount = GetValue("SomethingCount") + 1
   LetValue("SomethingCount", SomethingCount)
End Function

1. My biggest concern here is thread safety. I might be able to figure out the rest of the questions below, but I am not experienced with this and I know it is an area that it is EASY to go wrong in. The link above uses the method Dim _sht as System.Collections.Hashtable = System.Collections.Hashtable.Synchronized(_hashtable). Is that best? What about Mutex? Semaphore? I have no experience in this.

2. I think the namespace System.Collections for Hashtable is correct, but I'm having trouble adding System.Collections as a reference in my report to try to cure my current error of "Could not load file or assembly 'System.Collections'". When I browse to add the reference, it's not an available component to select.

3. I just confirmed that I can call code from a parameter's default value expression, so I'll put my Initialize code there. I also just found out about the OnInit procedure, but this has its own gotchas to research and work around: the Parameters collection may not be referenced from the OnInit method during parameter initialization.

4. I'm unsure about declaring the Data variable as New, perhaps it should be only be instantiated in the initializer if not already done (but I worry about race conditions because of the delay between the check that it's empty and the instantiation of it).

5. I also have a question about the Shared keyword. Is it necessary in all cases? I get errors if I leave it off function declarations, but it appears to work when I leave it off the variable declaration. Testing multiple simultaneous report executions is difficult... Could someone explain what Shared means specifically in the context of SSRS Code?

6. Is there a better way to initialize variables? Should I provide a second parameter to the GetValue function which is the default value to use if it finds that the variable doesn't exist in the hashtable yet?

7. Is it better to have nested Hashtables as I chose in my implementation, or to concatenate my InstanceID with the variable name to have a flat hashtable?

I'd really appreciate guidance, ideas and/or critiques on any aspect of what I've presented here.

Thank you!

Erik

Solution

Your code looks fine. For thread safety only the root (shared) hashtable Data needs to be synchronised. If you want to avoid using your InstanceID you could use Globals.ExecutionTime and User.UserID concatenated.

Basically I think you just want to change to initialize like this:

Private Shared Data As System.Collections.Hashtable 

If Data Is Nothing Then
   Set Data = Hashtable.Synchronized(New System.Collections.Hashtable())
End If

The contained hashtables should only be used by one thread at a time anyway, but if in doubt, you could synchronize them too.