游标,效率,事务,并发处理,忘高高手参与讨论解答!多谢!经典在加分

Question

有一存储过程用来做计算,中间采用游标,整个过程运行时间长,数据库检测有4个表出现TAB类型的锁, 模式为IX,SCH_S;一个用户运行其间,其他用户运行出现阻塞.
现列出改存储过程的语法:望有兴趣者联系指教[email protected] msn:[email protected] 在线等待declare c1 cursor for
select bh from temp_bh with(nolock) where userid = @xuserid 
--表temp_bh(userid,bh)两个字段;同时多人访问(删除,插入,无更新),数据<=10W
--不会出现同时访问同一行的情况;
--疑问,此出是否有问题,需要加什么限定语句?
open c1
fetch next from c1 into @xbh
while @@fetch_status = 0
begin
   while rq1<=rq2 --日期循环
      begin 
         --中间有很长的一段代码
         --1:此段代码中含有另外的游标
         --2:多处更新同一个表
         --3:频繁使用 
         -- begin transaction t1
         -- update insert sql
         -- commit transaction t1
         --4:嵌套有另外的存储过程      end
   fetch next from c1 into @xbh
endclose c1
deallocate c1如有需要可以提供数据库

Answer 1

出现阻塞时，用sp_wholock检测一下锁定了哪些表和资源。
CREATE PROCEDURE dbo.sp_wholock
AS--create procedure sp_WhoLock with ENCRYPTION as
-------------------------------------------------------------
--   一、根据sp_who改编产生SPID对应的用户#who表
------------------------------------------------------------- if (object_id('tmp_dbuser') is not null)
drop table tmp_dbuser if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses declare @loginame sysname 
set @loginame= NULL
set nocount on

declare @retcode int

declare  @sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare @charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11) select @retcode = 0      -- 0=good ,1=bad. --------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select  @spidlow  = 0 ,@spidhigh = 32767 IF (@loginame IS     NULL)  --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED -- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
begin
select @sidlow  = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end

IF (lower(@loginame) IN ('active'))  --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
begin
select @spidlow   = convert(int, @loginame) ,@spidhigh  = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN

LABEL_17PARM1EDITED:--------------------  Capture consistent sysprocesses.  ------------------- SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame)) as loginname
,spid as 'spid_sort'
 ,substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
INTO    #tb1_sysprocesses
from master.dbo.sysprocesses   (nolock)--------Screen out any rows? IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses 
where   lower(status)  = 'sleeping' and 
upper(cmd)    IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
                                  ) and 
blocked = 0--------Prepare to dynamically optimize column widths. Select
@charsidlow     = convert(varchar(85),@sidlow)
,@charsidhigh    = convert(varchar(85),@sidhigh)
,@charspidlow     = convert(varchar,@spidlow)
,@charspidhigh    = convert(varchar,@spidhigh) SELECT
@charMaxLenLoginName =convert( varchar,isnull( max( datalength(loginname)) ,5))
,@charMaxLenDBName =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6))
,@charMaxLenCPUTime =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7))
,@charMaxLenDiskIO=convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6))
,@charMaxLenCommand =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7))
,@charMaxLenHostName =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)) ,@charMaxLenProgramName =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11))
,@charMaxLenLastBatch =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
from #tb1_sysprocesses
where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
spid >= @spidlow and spid <= @spidhigh--------Output the report.EXECUTE(
'
SET nocount offSELECT
             SPID          = convert(char(5),spid)            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
      into tmp_dbuser            --Added line by 王建军
      from  #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      where spid >= ' + @charspidlow  + '
      and    spid <= ' + @charspidhigh + '      -- (Seems always auto sorted.)   order by spid_sortSET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
             sid >= ' + @charsidlow  + '
      and    sid <= ' + @charsidhigh + '
      and
**************/LABEL_86RETURN: if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses -----------------------------------------------------------
-- 显示锁定资源 选自sp_lock系统存储过程
-----------------------------------------------------------
select  lock.spid,
who.dbname as 数据库,
object_name(lock.objId) as 被锁定表,
lock.type as 锁类型,
lock.mode as 锁模式,
lock.status as 锁状态,
who.hostname as 用户主机,
who.login as 登录名,
who.programname as 应用程序,
who.status as 用户状态,
lock.indid,
lock.resource
from
(
select  convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from  master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where   master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
) lock,tmp_dbuser who
where lock.spid=who.spid and  who.dbname=db_name() and lock.objid>0
order by lock.spid if (object_id('tmp_dbuser') is not null)
            drop table tmp_dbuser
GO

Answer 2

适当使用commit释放源，减少竞争
具体情况得看你和业务逻辑了