拍卖行竞拍存储过程，存在什么问题呢？

------------------------------------------------------

--Copyright (c) 1.0

--Author     :保密

--Date       :2010-3-17 16:39:58

--Description:拍卖行的竞标

--游戏里的拍卖行竞拍存储过程，玩家反映一口价竞拍物品，点券扣了，但物品没有收到，目前拍卖行总交易10万多次，

--这种情况出现了11次。大概是什么问题呢？目前多次测试没能重现，有没有牛人能目测出问题在哪儿？==========================================================================================

--业务逻辑如下（所有的扣点加点，调用外部的存储过程，返回接口和调用方法也不一致，汗）：

--IF 点券交易

--BEGIN

--  IF 一口价  

--  BEGIN

--     1.发送给买家物品（发送到买家临时背包）

--     2.如果这个东西之前有另外的玩家竞价过，需要退还上一个玩家的点券

--     3.保存交易日志

--     4.删除拍卖行的物品，删除这条拍卖纪录

--     5.扣买家的点券

--     6.给卖家点券

--  END

--  ELSE 不是一口价，竞价

--  BEGIN     

--     1.退回上一个竞价者的点券

--     2.扣本次竞价者的点券

--  END

--END

--ELSE 游戏币交易

--BEGIN

--  1.退回上一个竞价者的游戏币

--  2.扣本次竞价者的游戏币

--END

--===========================================================================================

-- Return value:

--     0:执行成功

-- 特别说明，由于代码生成器问题，返回值只接收SELECT值，不接收直接RETURN的值，呃~~~不能改变别人就改变自己吧

------------------------------------------------------

ALTER PROCEDURE [dbo].[P_HBAuctionHouse_BidUp]

    @Idx BIGINT, -- 标识 

    @BuyerId varchar(50) ,-- 购买者Id，没有购买者的时候就是空的，有就是购买者的GUID

    @BuyerName varchar(50),--购买者名字

    @BuyPrice int -- 购买价  

AS

BEGIN

    SET XACT_ABORT ON

    BEGIN TRAN BIDUPTRAN

       DECLARE @BuyerUid UNIQUEIDENTIFIER--用户Id，外部传的字符串类型，需要转换成UNIQUEIDENTIFIER

       DECLARE @BidCount INT--竞标次数

       DECLARE @SellerId UNIQUEIDENTIFIER--卖家id

       DECLARE @SellerName VARCHAR(50)--卖家名称

       DECLARE @BeforeBuyerId VARCHAR(50)--上次的竞价者id

       DECLARE @BeforeBuyerName VARCHAR(50) --上次的竞价者NAME

       DECLARE @M INT--上次的购买价

       DECLARE @EndPrice INT--一口价   

       DECLARE @CurrencyType INT --点券还是游戏币     

       DECLARE @Result INT--点券增减返回值

       DECLARE @SRCID VARCHAR(50)--交易号，负的交易ID

       DECLARE @BeforePass9Name varchar(100)--之前买家的C通行证名

       DECLARE @BuyerPass9Name varchar(100)--当前买家的C通行证名

       DECLARE @TBLPAY TABLE(CODE VARCHAR(20))--交易表  

       DECLARE @NOW DATETIME--当前服务器时间

       SET @NOW = GETDATE()    

      

       SET @BidCount = -1--竞标次数

       IF @BuyerId <> ''

           SET @BuyerUid = CAST(@BuyerId AS UNIQUEIDENTIFIER)

       ELSE

           SET @BuyerUid = '00000000-0000-0000-0000-000000000000'

 

       SET @M = 0

   

    BEGIN   TRY

      

       --有无该条记录

       IF NOT EXISTS ( SELECT * FROM  [HB_AuctionHouse] WITH(NOLOCK) WHERE Idx = @Idx  )

       BEGIN

           IF @@TRANCOUNT > 0 ROLLBACK

           SELECT 6400  

           RETURN;      

       END

      

       --获取相关数据

       SELECT @IDX = IDX, @SellerId=SellerId ,@SellerName=SellerName,    @BeforeBuyerId=BuyerId,@BeforeBuyerName=BuyerName, @M=BuyPrice,@EndPrice=SellEndPrice, @BidCount =BidCount,@CurrencyType = CurrencyType

       FROM  [HB_AuctionHouse] WITH(XLOCK) WHERE Idx = @Idx

       IF @M > @BuyPrice

       BEGIN

           IF @@TRANCOUNT > 0 ROLLBACK

           SELECT 6407  

           RETURN;

       END

       IF @SellerId = @BuyerUid

       BEGIN

           IF @@TRANCOUNT > 0 ROLLBACK

           SELECT 6401      

           RETURN;   

       END

       --交易号

       SET @SRCID = CAST( @Idx * -1 AS VARCHAR(18)) + '_' + CAST( @BidCount AS VARCHAR(5))

 

       --允许用户竞标后，再一口价

       IF @BeforeBuyerId = @BuyerId AND @EndPrice <> @BuyPrice

       BEGIN

           IF @@TRANCOUNT > 0 ROLLBACK

           SELECT 6402      

           RETURN;   

       END

       IF @CurrencyType = 2--点券

       BEGIN

           IF @EndPrice = @BuyPrice--如果竞标价等于一口价，那就直接拍定

           BEGIN

              INSERT INTO [HB_TempPackage]--加入临时背包

                     ([ManagerId]

                     ,[ItemId]

                     ,[ItemStrengthLevel]

                     ,[ItemCode]

                     ,[ItemName]

                     ,[Count]

                     ,[ItemType]

                     ,[ImageId]

                     ,[Memo]

                     ,[Status]

                     ,[Rowtime]

                     ,[Increment])

              SELECT @BuyerId

                     ,ItemId

                     ,ItemStrengthLevel

                     ,ItemCode

                     ,ItemName

                     ,[Count]

                     ,[ItemType]

                     ,[ImageId]

                     ,'卖家:'+SellerName+',花费'+CAST(@BuyPrice AS VARCHAR(10))+'G'

                     ,0

                     ,GETDATE(),Increment FROM [HB_AuctionHouse] WHERE Idx = @Idx

             

              --退回上一个竞价者的钱$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$            

               IF @BidCount > 0

              BEGIN

                  --获取上一个买家的Pass9Name

--                DECLARE @BeforePass9Name varchar(100)

                  SELECT @BeforePass9Name = Pass9Name FROM dbo.HB_User U

                  INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId

                  WHERE M.ManagerGuid = @BeforeBuyerId

                  DECLARE @B VARCHAR(50)

                  SET @B = @SRCID+'_B'

                  EXEC P_HbPayAccount_Pay @BeforePass9Name,-1,@B,0,0,@M,@Result

                  IF @Result <> 5200

                  BEGIN                      

                     IF @@TRANCOUNT > 0 ROLLBACK

                     SELECT 6402  

                     RETURN;

                  END

              END

             

             

    --------------给上一个竞价者发一个邮件，通知竞标失败

    --------------通知卖家已经拍卖成功

              --保存交易日志

              INSERT INTO [HB_AuctionHouse_LOG]

              SELECT [Idx] ,    [ItemName] ,  [ItemStrengthLevel] ,

              [ItemId] , [ItemCode] ,  [ItemLevel],  [Count] ,  [ItemType] ,

              [ImageId] ,   [SellerId] ,  [SellerName], @BuyerId , @BuyerName ,

              [SellStartPrice] ,   [SellEndPrice],   [BuyPrice] ,

              [BidCount] ,[CurrencyType] ,[Increment] FROM [HB_AuctionHouse] WHERE Idx = @Idx

 

              --删除拍卖行的物品，删除这条拍卖纪录

              DELETE [HB_AuctionHouse] WHERE Idx = @Idx           

             

              --获取Pass9Name

              SELECT @BuyerPass9Name = Pass9Name FROM dbo.HB_User U (NOLOCK)

              INNER JOIN HB_MANAGER M  (NOLOCK) ON U.UserGuid = M.UserId

              WHERE M.ManagerGuid = @BuyerUid   

 

              --扣买家的点券

              DECLARE @N VARCHAR(50)

              SET @N = @SRCID+'_N'

               INSERT @TBLPAY

              EXEC P_HBPAYACCOUNT_CONSUMEPOINT @BuyerPass9Name,@N,@BuyPrice,@NOW

              IF NOT EXISTS(SELECT 1 FROM @TBLPAY WHERE CODE='5200')

              BEGIN

                  IF @@TRANCOUNT > 0 ROLLBACK

                  SELECT 6402  

                  RETURN;

              END              

 

              --给卖家点券$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

              --获取Pass9Name

              DECLARE @SellerPass9Name varchar(100)

              SELECT @SellerPass9Name = Pass9Name FROM dbo.HB_User U

              INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId

              WHERE M.ManagerGuid = @SellerId              

              DECLARE @S VARCHAR(50)

              SET @S = @SRCID+'_S'

              EXEC P_HbPayAccount_Pay @SellerPass9Name,-1,@S,0,0,@BuyPrice,@Result

              --PRINT @Result

              IF @Result <> 5200

              BEGIN                      

                  IF @@TRANCOUNT > 0 ROLLBACK

                  SELECT 6402  

                  RETURN;

              END   

 

           END

           ELSE---不是一口价

           BEGIN

              IF @BidCount = 0--是否是第一次竞拍

              BEGIN

                  IF @BuyPrice = @M

                  BEGIN     

                         --PRINT 2        

                         UPDATE [dbo].[HB_AuctionHouse] SET

                            [BuyerId] = @BuyerId,

                            BuyerName = @BuyerName,

                            [BuyPrice] = @BuyPrice,

                            [LimitTime] = DATEADD(n,5,[LimitTime]) ,

                            BidCount = BidCount + 1

                         WHERE

                            [Idx] = @Idx 

                         --PRINT 3           

                  END

                  ELSE

                  BEGIN

                         IF @@TRANCOUNT > 0 ROLLBACK

                         SELECT 6406

                         RETURN;

                  END

              END

              ELSE IF @BidCount > 0 --不是第一次竞拍

              BEGIN

                  IF @BuyPrice > @M

                  BEGIN            

                     --退回上一个竞价者的钱$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

                     --获取上一个买家的Pass9Name

                    

                     SELECT @BeforePass9Name = Pass9Name FROM dbo.HB_User U

                     INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId

                     WHERE M.ManagerGuid = @BeforeBuyerId

                    

                     EXEC P_HbPayAccount_Pay @BeforePass9Name,-1,@SRCID,0,0,@M,@Result

                     IF @Result <> 5200

                     BEGIN                      

                         IF @@TRANCOUNT > 0 ROLLBACK

                         SELECT 6402  

                         RETURN;

                     END          

    --------------------给上一个竞价者发一个邮件，通知竞标失败

                        

                     --扣本次竞价者的钱放到后面  

                     UPDATE [dbo].[HB_AuctionHouse] SET

                         [BuyerId] = @BuyerId,

                         BuyerName = @BuyerName,

                         [BuyPrice] = @BuyPrice,

                         [LimitTime] = DATEADD(n,5,[LimitTime]) ,

                         BidCount = BidCount + 1

                     WHERE

                         [Idx] = @Idx AND BuyPrice < @BuyPrice

                  END

                  ELSE

                  BEGIN               

                     IF @@TRANCOUNT > 0 ROLLBACK

                     SELECT 6407

                     RETURN;

                  END

              END

              --对玩家游戏币验证,扣买家的点券$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

              --

              SELECT @BuyerPass9Name = Pass9Name FROM dbo.HB_User U

              INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId

              WHERE M.ManagerGuid = @BuyerUid   

 

                        

             

              INSERT @TBLPAY

              EXEC P_HBPAYACCOUNT_CONSUMEPOINT @BuyerPass9Name,@SRCID,@BuyPrice,@NOW

              IF NOT EXISTS(SELECT 1 FROM @TBLPAY WHERE CODE='5200')

              BEGIN

                  IF @@TRANCOUNT > 0 ROLLBACK

                  SELECT 6402  

                  RETURN;

              END              

           END   

       END

       ELSE--游戏币

       BEGIN

           IF @BidCount = 0--是否是第一次竞拍

           BEGIN

              IF @BuyPrice = @M

              BEGIN     

                     --PRINT 2        

                     UPDATE [dbo].[HB_AuctionHouse] SET

                         [BuyerId] = @BuyerId,

                         BuyerName = @BuyerName,

                         [BuyPrice] = @BuyPrice,

                         [LimitTime] = DATEADD(n,5,[LimitTime]) ,

                         BidCount = BidCount + 1

                     WHERE  [Idx] = @Idx 

                     --PRINT 3           

              END

              ELSE

              BEGIN

                     IF @@TRANCOUNT > 0 ROLLBACK

                     SELECT 6406  

                     RETURN;                 

              END

           END

           ELSE IF @BidCount > 0 --不是第一次竞拍

           BEGIN

              IF @BuyPrice > @M

              BEGIN            

                  --退回上一个竞价者的钱

                  UPDATE HB_ManagerProperty SET  

                     [Money] = [Money] + @M

                  WHERE ManagerId = CAST(@BeforeBuyerId AS UNIQUEIDENTIFIER)

--------------------给上一个竞价者发一个邮件，通知竞标失败

                    

                  --扣本次竞价者的钱放到后面  

                  UPDATE [dbo].[HB_AuctionHouse] SET

                     [BuyerId] = @BuyerId,

                     BuyerName = @BuyerName,

                     [BuyPrice] = @BuyPrice,

                     [LimitTime] = DATEADD(n,5,[LimitTime]) ,

                     BidCount = BidCount + 1

                  WHERE

                     [Idx] = @Idx AND BuyPrice < @BuyPrice

              END

              ELSE

              BEGIN               

                  IF @@TRANCOUNT > 0 ROLLBACK

                  SELECT 6407

                  RETURN;

              END

           END

           --对玩家游戏币验证

            IF EXISTS (SELECT * FROM HB_ManagerProperty WHERE ManagerId = @BuyerUid AND [Money] >= @BuyPrice)

            BEGIN

               UPDATE  HB_ManagerProperty

               SET [Money] = [Money] - @BuyPrice

               WHERE ManagerId = @BuyerUid

             

            END

            ELSE--没有足够的游戏币

            BEGIN

              IF @@TRANCOUNT > 0 ROLLBACK

              SELECT 6408

              RETURN;

            END

       END   

      

    END   TRY

    BEGIN CATCH

       IF @@TRANCOUNT > 0 ROLLBACK TRAN      

    END CATCH

    IF @@TRANCOUNT > 0

    COMMIT TRAN BIDUPTRAN

   

END

SELECT 0

RETURN 0