A
A
Alexey Lebedev2014-09-30 11:40:32
SQL
Alexey Lebedev, 2014-09-30 11:40:32

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;

This is based on restrictions.
2)
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;

Here is the check in UPDATE.
3) Other options?
I would like to hear a reasoned answer.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander, 2014-09-30
@Papagatto

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 question

Ask a Question

731 491 924 answers to any question