IN SQL Server is CLR Threadsafe

Question

I have an interface in CLR between SQL Server and the Exchange Web Services to Sync and send emails between applications. In testing this it works(ed) without any problems; we are seeing sporadic issues in the production environment where longer web service task appear to be have overlapped.

My question is very simple and I cant decide from reading the CLR details on MSDN - is CLR Thread Safe or not out of the box.

If not what is the best way of making calls to CLR that are thread safe is to applocking around my procedures or is there a less drastic alternative.

---

Thanks for your responses; we are coming around to this being an EWS / Impersonation issue rather than a SQL Issue. We have done a new set of load tests out of hours on the system and even under massive load (1000x higher than the application has seen so far) we can't see a memory leak / threading issue which is why we are now looking elsewhere..

Answer 1

Don't use in-proc CLR to connect externally, to web services or exchange or whatever. Use an ordinary process, outside SQL Server. You'll see more than just 'sporadic' issues: you'll exhaust the worker pool on CLR events and the SQL Server will freeze.

Answer 2

The answer actually depends on how you write your .Net code AND how you register your Assembly. By default, and by general preference, SQLCLR Assemblies are created as SAFE which means that SQL Server scans the Assembly when it is created (the actual CREATE ASSEMBLY statement) to determine if it conforms to the rules of a SAFE Assembly. A SAFE Assembly does not have any non-read-only instance variables. Meaning, all variables are instance variables and if you do declare a static variable you have to use the "readonly" modifier. This ensures that you are not sharing data across threads.

HOWEVER, you can create a static variable in a Class and modify it but the Assembly must be created as UNSAFE. Trying to create it as SAFE will give you the following error:

CREATE ASSEMBLY failed because method 'MethodName' on type 'ClassName' in safe assembly 'AssemblyName' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Storing to a static variable is not thread safe and hence you must give the Assembly the UNSAFE permission. But outside of this case SQLCLR is thread safe.