公司LCS开通了对外和MSN通讯的供能，可老板要求员工只能和经过审批的人员通讯。跟踪Sql Server后发现解决之道，现分享给大家。
先在LCS的数据库 rtc 增加一个Table 如mycontact, 只有一列 UserAtHost ,类型为
nvarchar 450,将公司允许的联络人汇入，然后修改名为
RtcpEnsureResourceExists 的 procedure 。以
下是修改过的，其中红色部分是新增的语句,xxx.com.cn是公司的网域名
---------------------------------------------------------------------------
--
--
CREATE procedure RtcpEnsureResourceExists
@_UserAtHost nvarchar(4000),
@_ResourceId ResourceId output
as
set nocount on
declare @Error int
declare @_contact nvarchar(4000)
--
-- Check to see if the resource already exists; if not, insert it.
--
select @_ResourceId = ResourceId
from Resource
where UserAtHost = @_UserAtHost
select @_contact=UserAtHost
from mycontact
where UserAtHost = @_UserAtHost
if ((@_ResourceId is null) and (@_contact is not null)) or (@_UserAtHost like '%xxx.com.cn') begin
--
-- Deadlock possibility:
-- Unfortunately we have the potential for deadlock here. If two
-- connections have both acquired the shared lock on the row due
-- to the query above, and the row does not exist, they both
-- will try to escalate their shared lock to an exclusive lock in
-- order to perform the following insert. Preventing this by
-- using with (updlock) on the select would not be a good thing
-- however. This is because the update lock would be held for the
-- duration of the transaction (because we run at a high enough
-- isolation level) thus preventing anyone from even selecting the
-- row until our transaction was completed. We explicitly choose
-- the possibility of deadlock over the reduced concurrency. Note
-- that the possibility of deadlock is extremely rare -- two
-- connections need to be ensuring that the same row exists at
-- the same time when it doesn't exist.
--
insert Resource (UserAtHost) values (@_UserAtHost)
set @Error = @@error
if (@Error = 0) begin
set @_ResourceId = scope_identity()
return 0
end
--
-- Unique key violation - row already exists, so select it again.
--
else if (@Error = 2627) begin
select @_ResourceId = ResourceId
from Resource
where UserAtHost = @_UserAtHost
if (@_ResourceId is null) begin
exec dbo.DbRaiseError @@procid, 50120, @_UserAtHost
return -1
end
else begin
--
-- Raise an error to indicate that Unique key violation was
-- handled.
--
exec dbo.DbRaiseError @@procid, 50002
return 0
end
end
--
-- Some other problem with inserting the row.
--
else begin
exec dbo.DbRaiseError @@procid, 50001
return -1
end
end
GO
posted on 2006-10-13 12:44
Kevensun 阅读(215)
评论(0) 编辑 收藏 引用