这样更新是否会出现并发问题

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  
   ;WITH cte AS
    (
    SELECT TOP(1) * FROM dbo.Table2  
        WHERE Status = 1
        ORDER BY FileOriginalPriority DESC ,LastUpdate ASC
    )  
    UPDATE    cte
   SET    @r_FileId               = FileId,
  @r_FileOriginalPriority = FileOriginalPriority, 
  @r_Status               = Status,
  @r_LastUpdate           = LastUpdate,
  Status                  = 12,  
              LastUpdate              = GETDATE()
   RETURN @@ERROR
GO

N个想同的程序执行这个存储过程是否会存在2个程序取到想同的fileId? 并发
[解决办法]
呵呵，没有数据。

所以你自己试试，开多个会话，运行下面的代码，看看结果：

--等到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_LastUpdate

select @r_FileId ,
       @r_FileOriginalPriority ,
       @r_Status ,
       @r_LastUpdate

[解决办法]
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