结论
- SQL Server CLR是一个多线程运行的环境。
- 静态变量等线程安全问题，在CLR环境中存在。
摘录《Professional SQL Server™ 2005 CLR Programming》
SQL Server 2005 hosts the CLR in a “sandbox”-like environment in-process to itself, as you can see Figure 1-2. When a user requests a SQL CLR object for the first time, SQL Server will load the .NET execution engine mscoree.dll (which is the CLR) into memory.
实验
CLR代码
public partial class ProcessThread
{
public static int static_i=0;
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString PID()
{
return Process.GetCurrentProcess().Id.ToString();
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString TID()
{
return AppDomain.GetCurrentThreadId().ToString()+ ":"+
System.Threading.Thread.CurrentThread.ManagedThreadId.ToString();
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString StaticI()
{
return (static_i++).ToString();
}
};
安装CLR的SQL代码
CREATE ASSEMBLY sqlclr_test
FROM 'C:\SQLCLR\bin\Release\sqlclr.dll'
WITH permission_set = UNSAFE;
GO
create function fn_processid () returns nvarchar(max)
as external name sqlclr_test.ProcessThread.PID
GO
create function fn_threadid () returns nvarchar(max)
as external name sqlclr_test.ProcessThread.TID
GO
create function fn_statici () returns nvarchar(max)
as external name sqlclr_test.ProcessThread.StaticI
GO
测试用查询命令
select
dbo.fn_processid() as [ProcessID],
dbo.fn_threadid() as [ThreadID],
dbo.fn_statici() as [StaticI]
返回结果
| ProcessID |
ThreadID |
StaticI |
| 1608 |
5820:4 |
11 |
系统进程截图 (Process Explorer)
现象
- 每次执行查询命令，得到的ThreadID会发生变化，在SQL Server的进程包含的线程池中轮转。
- 每次执行查询命令，不论是否同一个线程，StaticI的值会增加。
- SQL Server的用户进程（登录），与所执行的线程编号几乎没有关系。
Filed by
charlie
at 10:56 pm under ChemDeposit, chemoinformatics
1 Comment
