Transaction in MS SQL - how to check?

Question

SQL

There are 2 tables. There is something like a purchase:
users
id_user, cash
items
id, id_user, item
At the beginning, the price is checked, the balance is checked, if it is possible to buy, then the following code is executed.
At the end we have a query like this:

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

Users use clickers and the money goes into the red. What shouldn't be.
Tried like this, doesn't help:

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

About 4 more records logs and statistics.

Answer 1

It is necessary to use a locking mechanism in addition to transactions.
For example, for MSSQL www.sql.ru/articles/mssql/2004/04110303advancedloc...
In your case it will be something like this:

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;

While one transaction writes, reads and updates something, others will wait for its completion.

Answer 2

Something I did not understand your tables.
I take them like:

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

The architecture of your application is also completely incomprehensible, but if you do not want to revise it, then create a "crutch" in the form of a table of transactions:
As an option, the status field
0 - transaction is open
1 - successfully completed
2 - error
When "purchasing" before opening a transaction (your BEGIN TRAN BUYITEM ) check the [status] field for the user and do not perform the following operations in the soft version, and reset the very first one in the hard version, punishing the user for the "cunning" (but here you need to work hard on exceptions).
By the way, it will be possible to store the history of "purchases":
And simplify the transaction table to:
DISCLAIMER: All that I wrote above is bad tone and it is better to change the architecture of the application. :