在SQL CLR线程缓存 [英] Multithreaded caching in SQL CLR
问题描述
有没有注册为不安全的多线程缓存机制，将在SQL CLR函数工作，而无需组装？
由于还描述的在这个职位，只需使用锁定语句将抛出一个异常的安全组装：
System.Security.HostProtectionException：
试图执行由CLR主机禁止的操作。
受保护的资源（仅适用于完全信任）是：所有
所要求的资源是：同步，ExternalThreading
我想我的任何功能调用都使用相同的内部缓存，在一个线程安全的方式，使许多业务可以做缓存中读取，同时写入。本质 - 我需要一个 ConcurrentDictionary ，将在SQLCLR工作安全的大会。不幸的是，使用 ConcurrentDictionary 本身给出上述相同的异常。
有没有内置在SQLCLR或SQL东西服务器来处理呢？还是我误解SQLCLR的线程模型？
我已阅读，就像我可以找到有关SQLCLR的安全限制。特别是，下面的文章可能有助于理解我所说的：
- 的SQL服务器CLR集成第1部分：安全
- 的Deploy/Use组件需要使用CLR和T-SQL 不安全/外部访问
这代码最终会被分发到其他库的一部分，所以我真的不希望被需要运行为不安全。
这是我正在考虑（低于富豪在评论中长大的）一种选择是从SQLCLR代码和使用中接触到tempdb中的作为高速缓存来代替。 不过我不太清楚究竟是如何做到这一点。我也不能肯定这是否是为内存缓存为附近的任何地方高性能 查看下面的更新。
我感兴趣的可能是其他任何可用的替代品。谢谢你。
示例
下面的代码使用一个静态的并发字典作为一个缓存和访问通过SQL CLR用户定义函数的高速缓存。向函数的所有呼叫都将具有相同的缓存工作。但是，这不会工作，除非大会注册为不安全。
<预类=郎-CS prettyprint-覆盖>
公共类UserDefinedFunctions
{
私人静态只读ConcurrentDictionary<字符串，字符串>缓存=
新ConcurrentDictionary<字符串，字符串>（）;
[SqlFunction]
公共静态的SqlString GetFromCache（字符串键）
{
字符串值;
如果（Cache.TryGetValue（键，超时值））
返回新的SqlString（值）;
返回SqlString.Null;
}
的[SqlProcedure]
公共静态无效AddToCache（字符串键，字符串值）
{
Cache.TryAdd（键，值）;
}
}
这些都是在一个名为
<预类=郎-SQL prettyprint-覆盖>
CREATE FUNCTION [DBO]。[GetFromCache]（@键为nvarchar（4000））
RETURNS为nvarchar（4000）WITH EXECUTE AS CALLER
AS外部NAME [SqlClrTest]。[SqlClrTest.UserDefinedFunctions]。[GetFromCache]
移动到
CREATE PROCEDURE [DBO]。[AddToCache]（@键为nvarchar（4000），@value为nvarchar（4000））
WITH EXECUTE AS CALLER
AS外部NAME [SqlClrTest]。[SqlClrTest.UserDefinedFunctions]。[AddToCache]
GO
然后他们在数据库中使用这样的：
<预类=郎-SQL prettyprint-覆盖>
EXEC dbo.AddToCache'富'，'酒吧'
选择dbo.GetFromCache（'富'）
更新
我想通了，如何从SQLCLR使用访问数据库上下文连接。该代码在这个要点同时显示了 ConcurrentDictionary 方法，而tempdb的方法。然后我跑了一些测试，从客户端统计（平均10次试验）测得的结果如下：
并发字典高速缓存
万写入：363ms
万阅读次数：81ms
TempDB的缓存
万写入：3546ms
万阅读次数：1199ms
这样抛出使用tempdb数据库表的想法。难道真的没有别的我可以尝试？
我添加了一个评论，说类似的东西，但我要去把它放在这儿作为一个答案，而不是，因为我觉得它可能需要一些背景知识。
ConcurrentDictionary ，你'已经正确地指出，需要不安全，因为它使用线程同步原语甚至超出了锁定最终 - 这明确要求访问较低级别的操作系统资源，因此需要SQL托管环境之外的代码钓鱼
因此，唯一的办法就可以得到不需要的溶液不安全，就是用一种不使用任何锁或其他线程同步原语。但是，如果基础结构是一个.NET 词典然后在多个线程共享它的唯一真正安全的方法是使用锁定或 Interlocked.CompareExchange （见的此处）与自旋等待。我似乎无法找到对后者是否在 SAFE 权限集所允许的任何信息，但我的猜测是，事实并非如此。
我还可以质疑施加基于CLR的解决这个问题的一个数据库引擎，其索引和 - 查找能力很可能是远远超过任何托管CLR溶液内的有效性。
Are there any multithreaded caching mechanisms that will work in a SQL CLR function without requiring the assembly to be registered as "unsafe"?
As also described in this post, simply using a lock statement will throw an exception on a safe assembly:
System.Security.HostProtectionException:
Attempted to perform an operation that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading
I want any calls to my functions to all use the same internal cache, in a thread-safe manner so that many operations can do cache reads and writes simultaneously. Essentially - I need a ConcurrentDictionary that will work in a SQLCLR "safe" assembly. Unfortunately, using ConcurrentDictionary itself gives the same exception as above.
Is there something built-in to SQLCLR or SQL Server to handle this? Or am I misunderstanding the threading model of SQLCLR?
I have read as much as I can find about the security restrictions of SQLCLR. In particular, the following articles may be useful to understand what I am talking about:
- SQL Server CLR Integration Part 1: Security
- Deploy/Use assemblies which require Unsafe/External Access with CLR and T-SQL
This code will ultimately be part of a library that is distributed to others, so I really don't want to be required to run it as "unsafe".
One option that I am considering (brought up in comments below by Spender) is to reach out to tempdb from within the SQLCLR code and use that as a cache instead. But I'm not quite sure exactly how to do that. I'm also not sure if it will be anywhere near as performant as an in-memory cache. See update below.
I am interested in any other alternatives that might be available. Thanks.
Example
The code below uses a static concurrent dictionary as a cache and accesses that cache via SQL CLR user-defined functions. All calls to the functions will work with the same cache. But this will not work unless the assembly is registered as "unsafe".
public class UserDefinedFunctions
{
private static readonly ConcurrentDictionary<string,string> Cache =
new ConcurrentDictionary<string, string>();
[SqlFunction]
public static SqlString GetFromCache(string key)
{
string value;
if (Cache.TryGetValue(key, out value))
return new SqlString(value);
return SqlString.Null;
}
[SqlProcedure]
public static void AddToCache(string key, string value)
{
Cache.TryAdd(key, value);
}
}
These are in an assembly called SqlClrTest, and and use the following SQL wrappers:
CREATE FUNCTION [dbo].[GetFromCache](@key nvarchar(4000))
RETURNS nvarchar(4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlClrTest].[SqlClrTest.UserDefinedFunctions].[GetFromCache]
GO
CREATE PROCEDURE [dbo].[AddToCache](@key nvarchar(4000), @value nvarchar(4000))
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlClrTest].[SqlClrTest.UserDefinedFunctions].[AddToCache]
GO
Then they are used in the database like this:
EXEC dbo.AddToCache 'foo', 'bar'
SELECT dbo.GetFromCache('foo')
UPDATE
I figured out how to access the database from SQLCLR using the Context Connection. The code in this Gist shows both the ConcurrentDictionary approach, and the tempdb approach. I then ran some tests, with the following results measured from client statistics (average of 10 trials):
Concurrent Dictionary Cache
10,000 Writes: 363ms
10,000 Reads : 81ms
TempDB Cache
10,000 Writes: 3546ms
10,000 Reads : 1199ms
So that throws out the idea of using a tempdb table. Is there really nothing else I can try?
I've added a comment that says something similar, but I'm going to put it here as an answer instead, because I think it might need some background.
ConcurrentDictionary, as you've correctly pointed out, requires UNSAFE ultimately because it uses thread synchronisation primitives beyond even lock - this explicitly requires access to lower-level OS resources, and therefore requires the code fishing outside of the SQL hosting environment.
So the only way you can get a solution that doesn't require UNSAFE, is to use one which doesn't use any locks or other thread synchronisation primitives. However, if the underlying structure is a .Net Dictionary then the only truly safe way to share it across multiple threads is to use Lock or an Interlocked.CompareExchange (see here) with a spin wait. I can't seem to find any information on whether the latter is allowed under the SAFE permission set, but my guess is that it's not.
I'd also be questioning the validity of applying a CLR-based solution to this problem inside a database engine, whose indexing-and-lookup capability is likely to be far in excess of any hosted CLR solution.
这篇关于在SQL CLR线程缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
