IN SQL Server is CLR Threadsafe

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

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.

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.

The main issue that you see with SQL CLR code is running out of memory, which leads to an AppDomain reset. This is equivalent to OS crash from the perspective of your code. When using the SQLCLR, you are using a separate memory pool managed by SQL Server which is much smaller and less flexible than you are used to. I've been told that the SQLCLR team is working on this issue.

One important note: if you do get a SQLCLR AppDomain reset, the stability of your server in other respects should not be affected. The SQLCLR procedure that crashed will simply return a TSQL error to the caller.

Having done much independant testing on CLR now (deliberately trying to make it fail) if your CLR code is written properly -nicely declare variables with initialisation values it appears that the CLR is thread safe.