Is the following scenario possible in SQL Server using a single active connection?
SQL Server中使用单个活动连接可以使用以下方案吗？
Inside a READCOMMITED transaction I need to update one table without locking it. For example each time I execute a statement I increase a field in that table. This operation doesn't need to be rolled back in case the transaction fails. Also, this update shouldn't block other concurrent users which try to update the same position.
在READCOMMITED事务中，我需要更新一个表而不锁定它。例如，每次执行语句时，我都会在该表中增加一个字段。如果事务失败，则不需要回滚此操作。此外，此更新不应阻止尝试更新相同位置的其他并发用户。
2 个解决方案
#1
1
You cannot perform an Update without putting locks on the table. This is to allow the Transaction isolation level of OTHER transactions to control whether they can "see" the changes made in the update. You can change the Transaction Isolation Level for the update, but this only affects the update session, (where you set it) controlling whether the update can "see" changes made in other sessions...
如果不对表进行锁定，则无法执行更新。这是为了允许OTHER事务的事务隔离级别控制是否可以“看到”更新中所做的更改。您可以更改更新的事务隔离级别，但这仅影响更新会话（您设置它）控制更新是否可以“查看”在其他会话中所做的更改...
If you want OTHER Sql statements to be able to see the stuff you're doing in this update, as though it was not locked, you have to change the transaction isolation level on those other txs to Read Uncommited. (Careful about this... This isolation level can allow numerous inconsistencies into your database.)
如果您希望OTHER Sql语句能够在此更新中看到您正在执行的操作，就好像它没有被锁定一样，您必须将其他tx上的事务隔离级别更改为Read Uncommited。 （小心这个......这个隔离级别可能会导致数据库出现大量不一致。）
#2
1
You can't not lock a table during a write.
在写入期间，您无法锁定表。
Do you mean that you want to write to a table, but then have the exclusive lock not persisted until commit/rollback?
你的意思是你想写一个表，但是在提交/回滚之前不能保持独占锁吗？
If so, put the value(s) you need into a table variable (which is unaffected by rollback/commit) and defer the write to after the main transaction. Then, do a single update to push the value from the table variable.
如果是这样，请将您需要的值放入表变量（不受回滚/提交影响）并将写入推迟到主事务之后。然后，执行单个更新以从表变量中推送值。
Edit: SQL 2008 workaround, with other ideas later
编辑：SQL 2008解决方法，稍后会有其他想法
#1
1
You cannot perform an Update without putting locks on the table. This is to allow the Transaction isolation level of OTHER transactions to control whether they can "see" the changes made in the update. You can change the Transaction Isolation Level for the update, but this only affects the update session, (where you set it) controlling whether the update can "see" changes made in other sessions...
如果不对表进行锁定，则无法执行更新。这是为了允许OTHER事务的事务隔离级别控制是否可以“看到”更新中所做的更改。您可以更改更新的事务隔离级别，但这仅影响更新会话（您设置它）控制更新是否可以“查看”在其他会话中所做的更改...
If you want OTHER Sql statements to be able to see the stuff you're doing in this update, as though it was not locked, you have to change the transaction isolation level on those other txs to Read Uncommited. (Careful about this... This isolation level can allow numerous inconsistencies into your database.)
如果您希望OTHER Sql语句能够在此更新中看到您正在执行的操作，就好像它没有被锁定一样，您必须将其他tx上的事务隔离级别更改为Read Uncommited。 （小心这个......这个隔离级别可能会导致数据库出现大量不一致。）
#2
1
You can't not lock a table during a write.
在写入期间，您无法锁定表。
Do you mean that you want to write to a table, but then have the exclusive lock not persisted until commit/rollback?
你的意思是你想写一个表，但是在提交/回滚之前不能保持独占锁吗？
If so, put the value(s) you need into a table variable (which is unaffected by rollback/commit) and defer the write to after the main transaction. Then, do a single update to push the value from the table variable.
如果是这样，请将您需要的值放入表变量（不受回滚/提交影响）并将写入推迟到主事务之后。然后，执行单个更新以从表变量中推送值。
Edit: SQL 2008 workaround, with other ideas later
编辑：SQL 2008解决方法，稍后会有其他想法