Транзакция в MS SQL — как осуществить проверку?

Question

[Алексей Лебедев]

Есть 2 таблицы. Происходит что-то вроде покупки:
users
id_user, cash

items
id, id_user, item

В начале все проверятся цена, проверяется баланс, если можно купить, то затем выполняется такой код.
В конце имеем запрос такой:

BEGIN TRAN BUYITEM;
UPDATE users SET cash = cash-@cash WHERE id = @id;
INSERT INTO items VALUES (@id, @item);
COMMIT TRAN BUYITEM;

Пользователи используют кликеры и деньги уходят в минус. Чего быть не должно.

Пробовал так, не помогает:

BEGIN TRAN BUYITEM;
IF (SELECT cash FROM users where id=@id)<@cash RETURN;
UPDATE users SET cash = cash-@cash WHERE id = @id;
INSERT INTO items VALUES (@id, @item);
COMMIT TRAN BUYITEM;

Еще около 4-х записей логи и статистику.

Answer 1

[Sumor]

Нужно использовать в дополнение к транзакциям механизм блокировок.
Например, для MSSQL www.sql.ru/articles/mssql/2004/04110303advancedloc...

В твоём случае это будет примерно так:

BEGIN TRAN BUYITEM;

Select cash from users (UPDLOCK) WHERE id = @id;

UPDATE users SET cash = cash-@cash WHERE id = @id;
INSERT INTO items VALUES (@id, @item);

COMMIT TRAN BUYITEM;

Пока одна транзакция что-то пишет, считает и обновляет — другие будут ждать её завершения.

Comments

[Алексей Лебедев]

Блокируется только строка или вся таблица?

[Sumor]

Это зависит от типа блокировки и объекта к которому она применяется — нужно ознакомиться с теорией про режимы изоляции и блокировки.

Может быть заблокирована строка, страница, где находятся обновляемые данные, таблица или вся база данных. Блокировки осуществляются автоматически при выполнении определённых действий (например, Update или Delete) или явного указания в запросе Select (UPDLOCK, ROWLOCK). Блокировки блокируют выполнение действий, требующих таких же блокировок из других транзакций. Только они должны работать в режиме изоляции не ниже READ COMMITTED, в твоём случае возможно нужен REPEATABLE READ.

Answer 2

[Anton Spirin]

Что-то я не понял ваших таблиц.
Принимаю их, как:

users(id_user, name, cash)
items(id_item, item, price)
user_item(id_user,id_item)

Так же совершено непонятна архитектура вашего приложения, но если вы не хотите её пересматривать, то создайте "костыль" в виде таблицы транзакций:

transactions([id_tran,id_user,id_item,status)

Как вариант поля статус
0 - открыта транзакция
1 - успешно завершена
2 - ошибка

При "покупке" перед открытием транзакции (ваш BEGIN TRAN BUYITEM) проверяйте поле [status] для пользователя и в мягком варианте не проводите следующие операции, а в жестком сбрасывайте самую первую, наказывая пользователя за "хитрость" (но тут надо хорошо поработать над исключениями).

Кстати, можно будет хранить историю "покупок":

user_item(id_user,id_item,id_tran)

А таблицу транзакций упростить до:

transactions([id_tran,id_user,status)

DISCLAIMER: Всё что я выше написал плохой тон и лучше изменить архитектуру приложения. :)

Comments

[Алексей Лебедев]

BEGIN TRAN BUYITEM;
UPDATE users SET cash = cash-@cash WHERE id = @id and cash>=@cash;
IF @@ROWCOUNT=0 BEGIN ROLLBACK TRAN BUYITEM RETURN END;
INSERT INTO items VALUES (@id, @item);
COMMIT TRAN BUYITEM;

А такой вариант нормальный? Или плохой тон или вообще уязвимый?