SELECT TOP(1) * FROM dbo.Table2 WHERE Status = 1 ORDER BY FileOriginalPriority DESC ,LastUpdate ASC 问题出在这里,多并发的话,这个值可能是相同的,所以导致你的UPDATE也会相同,最后可能产生死锁
ORDER BY 那提示语法问题啊
sorry! 修正如下,create procedure dbo.PopTable2 @r_FileId CHAR(10) = NULL OUTPUT, @r_FileOriginalPriority TINYINT = NULL OUTPUT, @r_Status TINYINT = NULL OUTPUT, @r_LastUpdate SMALLDATETIME = NULL OUTPUT AS SET NOCOUNT ON
UPDATE TOP(1) t SET @r_FileId = FileId, @r_FileOriginalPriority = FileOriginalPriority, @r_Status = Status, @r_LastUpdate = LastUpdate, Status = 12, LastUpdate = GETDATE() from (select top(1) * from dbo.Table2 where Status=1 order by FileOriginalPriority desc,LastUpdate asc) t
RETURN @@ERROR go
sorry! 修正如下,create procedure dbo.PopTable2 @r_FileId CHAR(10) = NULL OUTPUT, @r_FileOriginalPriority TINYINT = NULL OUTPUT, @r_Status TINYINT = NULL OUTPUT, @r_LastUpdate SMALLDATETIME = NULL OUTPUT AS SET NOCOUNT ON
UPDATE TOP(1) t SET @r_FileId = FileId, @r_FileOriginalPriority = FileOriginalPriority, @r_Status = Status, @r_LastUpdate = LastUpdate, Status = 12, LastUpdate = GETDATE() from (select top(1) * from dbo.Table2 where Status=1 order by FileOriginalPriority desc,LastUpdate asc) t
RETURN @@ERROR go
多谢了, 我在你基础上改了。create procedure dbo.PopTable2 @r_FileId CHAR(10) = NULL OUTPUT, @r_FileOriginalPriority TINYINT = NULL OUTPUT, @r_Status TINYINT = NULL OUTPUT, @r_LastUpdate SMALLDATETIME = NULL OUTPUT AS SET NOCOUNT ON
UPDATE a SET @r_FileId = b.FileId, @r_FileOriginalPriority = b.FileOriginalPriority, @r_Status = b.Status, @r_LastUpdate = b.LastUpdate, Status = 12, LastUpdate = GETDATE() from (select top(1) * from dbo.Table2 where Status=1 order by FileOriginalPriority desc,LastUpdate asc ) a inner join dbo.Table2 b on a.FileId=BasicData.FileId
@r_FileId CHAR(10) = NULL OUTPUT,
@r_FileOriginalPriority TINYINT = NULL OUTPUT,
@r_Status TINYINT = NULL OUTPUT,
@r_LastUpdate SMALLDATETIME = NULL OUTPUT
AS
SET NOCOUNT ON
UPDATE TOP(1) Table2
SET @r_FileId = FileId,
@r_FileOriginalPriority = FileOriginalPriority,
@r_Status = Status,
@r_LastUpdate = LastUpdate,
Status = 12,
LastUpdate = GETDATE()
from dbo.Table2
where Status=1
order by FileOriginalPriority desc,LastUpdate asc
RETURN @@ERROR
GO
--等到13:10:00
waitfor time '13:10:00'declare @r_FileId CHAR(10)
declare @r_FileOriginalPriority TINYINT
declare @r_Status TINYINT
declare @r_LastUpdate SMALLDATETIME exec dbo.PopTable2 @r_FileId output,
@r_FileOriginalPriority output,
@r_Status output,
@r_LastUpdateselect @r_FileId ,
@r_FileOriginalPriority ,
@r_Status ,
@r_LastUpdate
WHERE Status = 1
ORDER BY FileOriginalPriority DESC ,LastUpdate ASC
问题出在这里,多并发的话,这个值可能是相同的,所以导致你的UPDATE也会相同,最后可能产生死锁
@r_FileId CHAR(10) = NULL OUTPUT,
@r_FileOriginalPriority TINYINT = NULL OUTPUT,
@r_Status TINYINT = NULL OUTPUT,
@r_LastUpdate SMALLDATETIME = NULL OUTPUT
AS
SET NOCOUNT ON
UPDATE TOP(1) t
SET @r_FileId = FileId,
@r_FileOriginalPriority = FileOriginalPriority,
@r_Status = Status,
@r_LastUpdate = LastUpdate,
Status = 12,
LastUpdate = GETDATE()
from
(select top(1) *
from dbo.Table2
where Status=1
order by FileOriginalPriority desc,LastUpdate asc) t
RETURN @@ERROR
go
@r_FileId CHAR(10) = NULL OUTPUT,
@r_FileOriginalPriority TINYINT = NULL OUTPUT,
@r_Status TINYINT = NULL OUTPUT,
@r_LastUpdate SMALLDATETIME = NULL OUTPUT
AS
SET NOCOUNT ON
UPDATE TOP(1) t
SET @r_FileId = FileId,
@r_FileOriginalPriority = FileOriginalPriority,
@r_Status = Status,
@r_LastUpdate = LastUpdate,
Status = 12,
LastUpdate = GETDATE()
from
(select top(1) *
from dbo.Table2
where Status=1
order by FileOriginalPriority desc,LastUpdate asc) t
RETURN @@ERROR
go
多谢了,
我在你基础上改了。create procedure dbo.PopTable2
@r_FileId CHAR(10) = NULL OUTPUT,
@r_FileOriginalPriority TINYINT = NULL OUTPUT,
@r_Status TINYINT = NULL OUTPUT,
@r_LastUpdate SMALLDATETIME = NULL OUTPUT
AS
SET NOCOUNT ON
UPDATE a
SET @r_FileId = b.FileId,
@r_FileOriginalPriority = b.FileOriginalPriority,
@r_Status = b.Status,
@r_LastUpdate = b.LastUpdate,
Status = 12,
LastUpdate = GETDATE()
from
(select top(1) *
from dbo.Table2
where Status=1
order by FileOriginalPriority desc,LastUpdate asc
) a
inner join dbo.Table2 b on a.FileId=BasicData.FileId
RETURN @@ERROR
go