我有一个用户表Accounts_Users,其中Balance是money类型的,在用户花钱(@Amount)后,应该在用户的存款Balance中减去才对,但是经常有这样的情况,用户的banalce中有150元,花去150元后,竟然账户总额为300元,下面是我的存储过程,请问有什么问题会造成这种情况?SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertAccountsFees]
@Amount money,
@UserID int
AS
DECLARE @CurrBalance money
SELECT @CurrBalance = Balance
FROM Accounts_Users
WHERE UserID = @UserID IF @CurrBalance < @Amount
BEGIN
RETURN 0
ENDELSE
BEGIN
UPDATE Accounts_Users
SET Balance=Balance -@Amount
WHERE UserID=@UserID
END
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertAccountsFees]
@Amount money,
@UserID int
AS
DECLARE @CurrBalance money
SELECT @CurrBalance = Balance
FROM Accounts_Users
WHERE UserID = @UserID IF @CurrBalance < @Amount
BEGIN
RETURN 0
ENDELSE
BEGIN
UPDATE Accounts_Users
SET Balance=Balance -@Amount
WHERE UserID=@UserID
END
create table Accounts_Users(Balance money,UserID int)
insert into Accounts_Users
select 150,1 union
select 150,2
create PROCEDURE [dbo].[InsertAccountsFees]
(@Amount money,
@UserID int)
AS
DECLARE @CurrBalance money
SELECT @CurrBalance = Balance
FROM Accounts_Users
WHERE UserID = @UserID IF @CurrBalance < @Amount
BEGIN
RETURN 0
END
ELSE
BEGIN
UPDATE Accounts_Users
SET Balance=Balance -@Amount
WHERE UserID=@UserID
END
exec InsertAccountsFees 150,1
select * from Accounts_Users
/*
Balance UserID
--------------------- -----------
0.00 1
150.00 2
好吧 我是随便问问的……
没有处理好并发问题?那怎么解决?
是不是表Accounts_Users 有另外的触发器
@Amount money,
@UserID int
AS
DECLARE @CurrBalance money
SELECT @CurrBalance = Balance FROM Accounts_Users WHERE UserID = @UserID
if @CurrBalance > @Amount
begin
UPDATE Accounts_Users SET Balance = Balance - @Amount WHERE UserID = @UserID
end
Create table Accounts_Users (
UserID int,
Balance money
)
Go
insert into Accounts_Users
select 1,150 union all
select 2,200
Go
exec InsertAccountsFees 50,1UserID Balance
----------- ---------------------
1 100.00
2 200.00(2 行受影响)
SET Balance=Balance -@Amount
WHERE UserID=@UserID
这样处理怎么样?如何解锁 ?
或者设置 解锁超时时间
set lock_timeout 30 --30hao可以可以设定
@Amount money,
@UserID int
AS
DECLARE @CurrBalance money
SELECT @CurrBalance = Balance
FROM Accounts_Users
WHERE UserID = @UserID IF @CurrBalance < @Amount
BEGIN
RETURN 0
ENDELSE
BEGIN
BEGIN TRANSACTION
UPDATE Accounts_Users with(TABLOCKX)
SET Balance=Balance -@Amount
WHERE UserID=@UserID
COMMIT TRANSACTION
END