Answer the question
In order to leave comments, you need to log in
What is the best transaction option in MS SQL?
There are 3 tables.
1) A user who has money.
users: id(int), cash(int(>=0))
example 10, 1000
User 10 has 1000 rubles. Use space constraint cash>0
2) Store with items:
shop: id(int), count
example: 5, 20
Item store number 5 has 20 items
3) User purchases
items: uid(int), item(int), count(int)
example 10, 5, 3
User number 10, 3 pieces of item number 5.
The task is to realize the purchase of the item. 2 things are important:
1) reliable
2) fast
Options:
1)
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE users SET [email protected] WHERE [email protected];
UPDATE items SET count=count+1 WHERE [email protected] and [email protected];
IF @@ROWCOUNT=0
INSERT INTO items VALUES (@uid, @item, 1);
UPDATE shop SET count=count-1 WHERE [email protected]m;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
UPDATE users SET [email protected] WHERE [email protected] and cash>[email protected];
IF @@ROWCOUNT=0
ROLLBACK TRANSACTION;
UPDATE items SET count=count+1 WHERE [email protected] and [email protected];
IF @@ROWCOUNT=0
INSERT INTO items VALUES (@uid, @item, 1);
UPDATE shop SET count=count-1 WHERE [email protected];
COMMIT TRANSACTION;
Answer the question
In order to leave comments, you need to log in
declare @diff int
, @count int
select @diff = cash - @price
from users
where id = @uid
select @count = count
from shop
where item = @item
if (@count>0) and (@diff > 0)
begin
begin transaction
update users
set [email protected]
where [email protected]
if exists (select top 1 from items where uid = @uid and item=@item)
update items
set count=count+1
where [email protected] and item=@item;
else
insert into items
select @uid, @item, 1;
update shop
set count=count-1
where item=@item;
if @@error != 0
rollback
commit
end
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question