存储过程如下:
ALTER PROCEDURE [dbo].[proCicPagination]
(
@TableName nvarchar(200)
,@OrderBy nvarchar(200) = ' id '
,@FieldList nvarchar(200) = '*'
,@Filter nvarchar(500)
,@StartIndex int = 1
,@PageSize int = 1
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecSql nvarchar(max) -- 要执行的ql
DECLARE @where varchar(200)
DECLARE @EndIndex int
SET @EndIndex = @startIndex+@pageSize-1
if len(@Filter) = 0
set @where = ''
else
set @where = ' where '+@Filter
SET @ExecSql = 'WITH RowList AS ( '
+'SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+' )AS Row, '+ @FieldList
+' from '+@TableName + ' WITH(NOLOCK) ' + @where +' ) '
+' SELECT ' + @FieldList + ' FROM RowList WITH(NOLOCK) WHERE Row between '+convert(varchar,@startIndex)+' and '+convert(varchar,@EndIndex)
print @ExecSql
EXEC (@ExecSql) -- 返回结果集
END我已经加上了 WITH(NOLOCK)
为什么还是会出现
事务(进程 ID 127)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
ALTER PROCEDURE [dbo].[proCicPagination]
(
@TableName nvarchar(200)
,@OrderBy nvarchar(200) = ' id '
,@FieldList nvarchar(200) = '*'
,@Filter nvarchar(500)
,@StartIndex int = 1
,@PageSize int = 1
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecSql nvarchar(max) -- 要执行的ql
DECLARE @where varchar(200)
DECLARE @EndIndex int
SET @EndIndex = @startIndex+@pageSize-1
if len(@Filter) = 0
set @where = ''
else
set @where = ' where '+@Filter
SET @ExecSql = 'WITH RowList AS ( '
+'SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+' )AS Row, '+ @FieldList
+' from '+@TableName + ' WITH(NOLOCK) ' + @where +' ) '
+' SELECT ' + @FieldList + ' FROM RowList WITH(NOLOCK) WHERE Row between '+convert(varchar,@startIndex)+' and '+convert(varchar,@EndIndex)
print @ExecSql
EXEC (@ExecSql) -- 返回结果集
END我已经加上了 WITH(NOLOCK)
为什么还是会出现
事务(进程 ID 127)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
但是你的存储过程有row_number()是可能会引起问题的,你去掉试试