blocks|key|260118|text|我相信您会希望使用可重复读取，这将锁定记录，第一个选择将得到值，然后它将更新阻塞线程2，直到它完成。因此，在您的示例中，结果为200。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260119|读取未提交将导致两个记录将值设置为100。|260120|读提交可能会有一些有趣的结果，这取决于两个线程的时间.|260121|这里还有一篇关于可重复读的很好的文章，它提供了一个很好的例子|offset|length|260122|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.msdn.com/craigfr/archive/2007/05/09/repeatable-read-isolation-level.aspx^0|0|0|0|8|4|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@$H|V|I|W|1|X]]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

I believe that you would want to use Repeatable read, which would lock the records, the first select would get the value, then it would update blocking thread two until it was completed. Thus an end result of 200 in your example

Read uncommitted would result in both records setting the value to 100.

Read committed might have a bit of an intersting result, depending on timing of the two threads....

Here is a nice article I found about <a href="http://blogs.msdn.com/craigfr/archive/2007/05/09/repeatable-read-isolation-level.aspx" rel="nofollow noreferrer">Repeatable Read</a> as well, that provides a good example

blocks|key|1258913|text|是的，你可能想要可重复阅读。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1258914|我可能会通过乐观锁定来处理这个问题，其中只有在现有值与读取(测试和设置)相同的情况下才会进行更新。如果值不相同，则引发错误。这允许您运行读未提交、无死锁和无数据损坏的操作。|1258915|BEGIN+TRANSACTION
DECLARE+MONEY+@amount
SELECT+Amount+AS+@amount
++FROM+Deposits
++WHERE+UserId+=+123
UPDATE+Deposits
++SET+Amount+=+@amount+%2B+100.0
++WHERE+UserId+=+123+AND+Amount+=+@amount
IF+@@ROWCOUNT+<>+1+BEGIN+ROLLBACK;+RAISERROR(...)+END
ELSE+COMMIT+END|code-block|syntax|javascript|1258916|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Yes, you probably want repeatable read. 

I'd probably handle this via optimistic locking wherein you only update if the existing value is the same as it was when you read (test-and-set). If the value isn't the same, raise an error. This allows you to run read-uncommitted, without deadlocks, and without data corruption.

<pre><code>BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
 FROM Deposits
 WHERE UserId = 123
UPDATE Deposits
 SET Amount = @amount + 100.0
 WHERE UserId = 123 AND Amount = @amount
IF @@ROWCOUNT &lt;&gt; 1 BEGIN ROLLBACK; RAISERROR(...) END
ELSE COMMIT END
</code></pre>

blocks|key|260191|text|其他人已经讨论了使用可重复阅读的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260192|所以我会提出不同的建议.|260193|为什么要使用两个语句而不是像下面这样的一个语句？|260194|UPDATE+Deposits
SET+Amount+=+Amount+%2B+100.0
WHERE+UserId+=+123|code-block|syntax|javascript|260195|而且，你真正的交易不仅仅是一个UserID，对吗？如果没有，那么您就有可能使用比原来预期的更多的记录。|260196|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Others already addressed the issue of using REPEATABLE READ.

So I'll chime in with a different piece of advice...

Why use two statements and not just one statement like the following?

<pre><code>UPDATE Deposits
SET Amount = Amount + 100.0
WHERE UserId = 123
</code></pre>

Also, your real transactions are going off of something more than a UserID, right? If not, you run the risk of working with more records than you originally intended.

blocks|key|896595|text|很好的场景。我决定试一试。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|896596|下面是我的安装脚本：|896597|CREATE+TABLE+Deposits(Amount+Money,+UserID+int)
INSERT+INTO+Deposits+(Amount,+UserID)
SELECT+0.0,+123
--Reset
UPDATE+Deposits
SET+Amount+=+0.00
WHERE+UserID+=+123|code-block|syntax|javascript|896598|这是我的测试脚本。|896599|SET+TRANSACTION+ISOLATION+LEVEL+Serializable
----------------------------------------
--+Part+1
----------------------------------------
BEGIN+TRANSACTION
DECLARE+@amount+MONEY
SET+@amount+=
(
SELECT+Amount
FROM+Deposits
WHERE+UserId+=+123
)
SELECT+@amount+as+Amount
----------------------------------------
--+Part+2
----------------------------------------
DECLARE+@amount+MONEY
SET+@amount+=++*value+from+step+1*
UPDATE+Deposits
SET+Amount+=+@amount+%2B+100.0
WHERE+UserId+=+123
COMMIT
SELECT+*
FROM+Deposits
WHERE+UserID+=+123|896600|我在两个查询分析器窗口中加载了这个测试脚本，并按照问题描述的方式运行每个部分。|896601|所有的读取都发生在任何写入之前，所以所有线程/场景都会将0的值读入@+value。|896602|以下是研究结果：|896603|宣读承诺|896604|1+T1.@Amount+=+0.00
2+T1.@Amount+=+0.00
3+Deposits.Amount+=+100.00
4+Deposits.Amount+=+100.00|896605|读未承诺|896606|1+T1.@Amount+=+0.00
2+T1.@Amount+=+0.00
3+Deposits.Amount+=+100.00
4+Deposits.Amount+=+100.00|896607|可重复读|896608|1+T1.@Amount+=+0.00+(locks+out+changes+by+others+on+Deposit.UserID+=+123)
2+T1.@Amount+=+0.00+(locks+out+changes+by+others+on+Deposit.UserID+=+123)
3+Hangs+until+step+4.+(due+to+lock+in+step+2)
4+Deadlock!
Final+result:+Deposits.Amount+=+100.00|896609|可序列化|896610|1+T1.@Amount+=+0.00+(locks+out+changes+by+others+on+Deposit)
2+T1.@Amount+=+0.00+(locks+out+changes+by+others+on+Deposit)
3+Hangs+until+step+4.+(due+to+lock+in+step+2)
4+Deadlock!
Final+result:+Deposits.Amount+=+100.00|896611|这是每种类型的解释，可以用来通过思想模拟来达到这些结果。|896612|读取提交的和读取未实现的，两者都不锁定其他用户修改后读取的数据。不同之处在于，未提交的读取将允许您查看尚未提交的数据(缺点)，如果有其他人锁定的数据反对读取(向上)，则不会阻止您的读取，这实际上是说了两次相同的话。|offset|length|style|BOLD|896613|可重复读取的Serializable，和都是为了读取而提交的。对于锁定，这两个锁数据已被其他用户的修改读取。不同之处在于，可序列化的块比已读取的行多，它还会阻止插入，从而引入以前不存在的记录。|896614|因此，使用可重复读取，您可以在以后的读取中看到新的记录(称为:幻影记录)。使用可序列化的方法，可以阻止这些记录的创建，直到提交。|896615|上述解释来自于我对这篇msdn文章的解释。|896616|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|5|6|6|0|6|C|K|24|0|0|1S|0|0|L|B|4|0|0^^$0|@$1|2|3|4|5|6|7|1U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|20|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|21|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|22|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|23|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|24|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|F|7|25|8|@]|9|@]|A|$G|H]]|$1|10|3|11|5|6|7|26|8|@]|9|@]|A|$]]|$1|12|3|13|5|F|7|27|8|@]|9|@]|A|$G|H]]|$1|14|3|15|5|6|7|28|8|@]|9|@]|A|$]]|$1|16|3|17|5|F|7|29|8|@]|9|@]|A|$G|H]]|$1|18|3|19|5|6|7|2A|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2B|8|@$1C|2C|1D|2D|1E|1F]|$1C|2E|1D|2F|1E|1F]]|9|@]|A|$]]|$1|1G|3|1H|5|6|7|2G|8|@$1C|2H|1D|2I|1E|1F]|$1C|2J|1D|2K|1E|1F]]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|2L|8|@$1C|2M|1D|2N|1E|1F]]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|2O|8|@$1C|2P|1D|2Q|1E|1F]]|9|@$1C|2R|1D|2S|1|2T]]|A|$]]|$1|1M|3|-4|5|6|7|2U|8|@]|9|@]|A|$]]]|1N|$1O|$5|1P|1Q|1R|A|$1S|1T]]]]

Nice well stated scenario. I decided to test it.

Here's my setup script:

<pre><code>CREATE TABLE Deposits(Amount Money, UserID int)
INSERT INTO Deposits (Amount, UserID)
SELECT 0.0, 123
--Reset
UPDATE Deposits
SET Amount = 0.00
WHERE UserID = 123
</code></pre>

Here's my test script.

<pre><code>SET TRANSACTION ISOLATION LEVEL Serializable
----------------------------------------
-- Part 1
----------------------------------------
BEGIN TRANSACTION
DECLARE @amount MONEY
SET @amount =
(
SELECT Amount
FROM Deposits
WHERE UserId = 123
)
SELECT @amount as Amount
----------------------------------------
-- Part 2
----------------------------------------
DECLARE @amount MONEY
SET @amount = *value from step 1*
UPDATE Deposits
SET Amount = @amount + 100.0
WHERE UserId = 123
COMMIT
SELECT *
FROM Deposits
WHERE UserID = 123
</code></pre>

I loaded up this test script in two query analyzer windows and ran each part as described by the question.

All of the reading happens before any of the writing, so all threads/scenarios will read the value of 0 into @amount.

Here are the results:

Read committed

<pre><code>1 T1.@Amount = 0.00
2 T1.@Amount = 0.00
3 Deposits.Amount = 100.00
4 Deposits.Amount = 100.00
</code></pre>

Read uncommitted

<pre><code>1 T1.@Amount = 0.00
2 T1.@Amount = 0.00
3 Deposits.Amount = 100.00
4 Deposits.Amount = 100.00
</code></pre>

Repeatable Read

<pre><code>1 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
2 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
3 Hangs until step 4. (due to lock in step 2)
4 Deadlock!
Final result: Deposits.Amount = 100.00
</code></pre>

Serializable

<pre><code>1 T1.@Amount = 0.00 (locks out changes by others on Deposit)
2 T1.@Amount = 0.00 (locks out changes by others on Deposit)
3 Hangs until step 4. (due to lock in step 2)
4 Deadlock!
Final result: Deposits.Amount = 100.00
</code></pre>

Here's an explanation of each type which can be used to reach these results through thought simulations.

Read Committed and Read Uncommited, both do not lock the data that was read against modifications by other users. The difference is that read uncommitted will allow you to see data that is not yet committed (downside) and will not block your read if there is data locked by others against reading (upside), which is really saying the same thing twice.

Repeatable Read and Serializable, both behave like read committed for reading. For locking, both lock data which has been read against modification by other users. The difference is that serializable blocks more than the row which has been read, it also blocks inserts that would introduce records that were not present before.

So with repeatable read, you could see new records (termed : phantom records) in later reads. With serializable, you block the creation of those records until you commit.

The above explanations come from my interpretation of this <a href="http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx" rel="nofollow noreferrer">msdn</a> article.

blocks|key|1259078|text|否则，您可以使用锁定提示来避免死锁(万一服务器处于read模式)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1259079|BEGIN+TRANSACTION
DECLARE+MONEY+@amount
SELECT+Amount+AS+@amount
++FROM+Deposits+WITH(UPDLOCK)
++WHERE+UserId+=+123
UPDATE+Deposits
++SET+Amount+=+@amount+%2B+100.0
++WHERE+UserId+=+123
COMMIT|code-block|syntax|javascript|1259080|当然，在这个特定的过程中，单一的语句(比如Kevin发布的)是首选的，并且不会产生副作用，但是在更复杂的情况下，UPDLOCK提示可能变得很方便。|1259081|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Otherwise you can use locking hint to avoid deadlocks (in case you have server in read commited mode):

<pre><code>BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
 FROM Deposits WITH(UPDLOCK)
 WHERE UserId = 123
UPDATE Deposits
 SET Amount = @amount + 100.0
 WHERE UserId = 123
COMMIT
</code></pre>

In this specific procedure of course single statement (like Kevin Fairchild posted) is preferred and doesn't cause side effects, but in more complex situations UPDLOCK hint may become handy.

Consider that I have a transaction:

<pre><code>BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
 FROM Deposits
 WHERE UserId = 123
UPDATE Deposits
 SET Amount = @amount + 100.0
 WHERE UserId = 123
COMMIT
</code></pre>

And it gets executed on 2 threads, in the order:

<ol>
<li>thread 1 - select</li>
<li>thread 2 - select</li>
<li>thread 1 - update</li>
<li>thread 2 - update</li>
</ol>

Assume that before execution Amount is 0.

What will happen in this case in the different settings of SQL Server (read uncommited, read commited, repeatable read, serializable), what will be amount at the end, will there be a deadlock?

How does SQL Server locking work in this scenario?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

假设我有一笔交易：BEGIN TRANSACTIONDECLARE MONEY @amountSELECT Amount AS @amount  FROM Deposits  WHERE UserId = 123UPDATE Deposits  SET Amount = @amount + 100.0  WHERE U...

问在这种情况下，Server锁定是如何工作的？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在这种情况下，Server锁定是如何工作的？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在这种情况下，Server锁定是如何工作的？
EN