I am trying to insert the data into the Wallets table of a SQL Server database. There are can be many requests at the same time so due to the sensitive info I have to use transactions.
我试图将数据插入SQL Server数据库的Wallets表中。由于我必须使用交易的敏感信息，因此可以同时有许多请求。
The work flow is the following:
工作流程如下：
-
read the amount of the user's wallet
读取用户钱包的金额
-
insert the new record based on the previously received data
根据以前接收的数据插入新记录
I tried different isolation levels but in all the cases the transaction blocks the whole table, not just the record I am working with. Even ReadUncommitted or RepeatableRead block the whole table.
我尝试了不同的隔离级别，但在所有情况下，事务都会阻塞整个表，而不仅仅是我正在使用的记录。甚至ReadUncommitted或RepeatableRead也会阻塞整个表。
Is there a way to block only the records I am working with?
有没有办法只阻止我正在使用的记录？
Let me detail: I don't use any indexes in the table
让我详细说明：我不使用表中的任何索引
The workflow (translating C# into SQL) is the following:
工作流（将C＃转换为SQL）如下：
1) Select * from Balance 2) Insert ... INTO Balance
1）从余额2中选择*）插入... INTO余额
3 个解决方案
#1
1
UPDLOCK is used when you want to lock a row or rows during a select statement for a future update statement
如果要在select语句期间为将来的更新语句锁定一行或多行，则使用UPDLOCK
Ttransaction-1 :
Ttransaction-1：
BEGIN TRANSACTION
SELECT * FROM dbo.Test WITH (UPDLOCK) /*read the amount of the user's wallet*/
/* update the record on same transaction that were selected in previous select statement */
COMMIT TRANSACTION
Ttransaction-2 :
Ttransaction-2：
BEGIN TRANSACTION
/* insert a new row in table is allowed as we have taken UPDLOCK, that only prevents updating the same record in other transaction */
COMMIT TRANSACTION
#2
1
It isn't possible to handle lock escalation process (row - page - table - database). Unfortunately, It makes automatically. But you can get some positive effects if:
无法处理锁升级过程（行 - 页 - 表 - 数据库）。不幸的是，它自动生成。但如果符合以下条件，您可以获得一些积
-
reduce the amount of data which used in queries
减少查询中使用的数据量
-
optimize queries by hints, indexes etc
通过提示，索引等优化查询
For INSERT INTO TABLE hint with (rowlock) can improve performance.
对于INSERT INTO TABLE，使用（rowlock）提示可以提高性能。
Also, select statement use shared (S/IS) lock types which don't allow any update of data, but doesn't block reading.
此外，select语句使用共享（S / IS）锁定类型，这些类型不允许任何数据更新，但不阻止读取。
#3
1
you should use optimistic locking. That will only lock the current row. Not whole table.
你应该使用乐观锁定。这只会锁定当前行。不是整张桌子。
You can read below links for more reference :-
您可以阅读以下链接以获取更多参考： -
乐观锁定
乐观并发
#1
1
UPDLOCK is used when you want to lock a row or rows during a select statement for a future update statement
如果要在select语句期间为将来的更新语句锁定一行或多行，则使用UPDLOCK
Ttransaction-1 :
Ttransaction-1：
BEGIN TRANSACTION
SELECT * FROM dbo.Test WITH (UPDLOCK) /*read the amount of the user's wallet*/
/* update the record on same transaction that were selected in previous select statement */
COMMIT TRANSACTION
Ttransaction-2 :
Ttransaction-2：
BEGIN TRANSACTION
/* insert a new row in table is allowed as we have taken UPDLOCK, that only prevents updating the same record in other transaction */
COMMIT TRANSACTION
#2
1
It isn't possible to handle lock escalation process (row - page - table - database). Unfortunately, It makes automatically. But you can get some positive effects if:
无法处理锁升级过程（行 - 页 - 表 - 数据库）。不幸的是，它自动生成。但如果符合以下条件，您可以获得一些积
-
reduce the amount of data which used in queries
减少查询中使用的数据量
-
optimize queries by hints, indexes etc
通过提示，索引等优化查询
For INSERT INTO TABLE hint with (rowlock) can improve performance.
对于INSERT INTO TABLE，使用（rowlock）提示可以提高性能。
Also, select statement use shared (S/IS) lock types which don't allow any update of data, but doesn't block reading.
此外，select语句使用共享（S / IS）锁定类型，这些类型不允许任何数据更新，但不阻止读取。
#3
1
you should use optimistic locking. That will only lock the current row. Not whole table.
你应该使用乐观锁定。这只会锁定当前行。不是整张桌子。
You can read below links for more reference :-
您可以阅读以下链接以获取更多参考： -
乐观锁定
乐观并发