Alex 的评论
这很有效,但我觉得一定有更好的方法。我不太了解表/行锁定。我有一个表和一个SP来管理不断增加的交易数量(1,2,3等)每个人:
CREATE TABLE PersonTransaction (
PersonID int NOT NULL,
TransactionID int NOT NULL
)
INSERT INTO PersonTransaction VALUES (1,0), (2,0), (3,0)
CREATE PROCEDURE PersonNewTransaction
@PersonID int
AS
BEGIN
UPDATE PersonTransaction
SET TransactionID=TransactionID+1
WHERE PersonID=@PersonID
SELECT TransactionID
FROM PersonTransaction
WHERE PersonID=@PersonID
END
PersonNewTransaction 1
PersonNewTransaction 3
PersonNewTransaction 1
select * from PersonTransaction
PersonID TransactioID
1 2
2 0
3 1
我应该用事务和SP_getapplock包装SP并结束它吗?还是有更优雅的方法?
UPDATE PersonTransaction
SET TransactionID=TransactionID+1
output inserted.TransactionID
WHERE PersonID=@PersonID