乐观锁与悲观锁
我了解乐观锁定和悲观锁定之间的区别。 现在，有人可以向我解释一下我一般什么时候会使用其中任何一个吗？
这个问题的答案是否会根据我是否使用存储过程来执行查询而改变？
但只是检查一下，乐观意味着“在读取时不锁定表”，悲观意味着“在读取时锁定表”。
I understand the differences between optimistic and pessimistic locking. Now, could someone explain to me when I would use either one in general?
And does the answer to this question change depending on whether or not I'm using a stored procedure to perform the query?
But just to check, optimistic means "don't lock the table while reading" and pessimistic means "lock the table while reading."
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（13）
乐观锁定是一种策略，您可以读取记录，记下版本号（其他方法可以是否涉及日期、时间戳或校验和/哈希）并在写回记录之前检查版本是否未更改。 当您写回记录时，您会过滤版本的更新以确保它是原子的。 （即在您检查版本和将记录写入磁盘之间尚未更新）并一键更新版本。
如果记录是脏的（即与您的版本不同），您将中止事务并且用户可以重新启动它。
此策略最适用于大容量系统和三层架构，在这些系统和三层架构中，您不必为会话维护与数据库的连接。 在这种情况下，客户端实际上无法维护数据库锁，因为连接是从池中获取的，并且您可能不会在一次访问到下一次访问中使用相同的连接。
悲观锁定是指您将记录锁定为独占使用，直到您使用完毕为止。 它比乐观锁定具有更好的完整性，但要求您谨慎对待应用程序设计以避免死锁 。 要使用悲观锁定，您需要直接连接到数据库（通常是在两个层客户端服务器应用程序）或可以独立于连接使用的外部可用事务ID。
在后一种情况下，您使用 TxID 打开事务，然后使用该 ID 重新连接。 DBMS 维护锁定并允许您通过 TxID 恢复会话。 这就是使用两阶段提交协议（例如 XA 或 < a href="http://msdn.microsoft.com/en-us/library/ms687120(VS.85).aspx" rel="noreferrer">COM+ 事务）有效。
Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.
This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.
Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.
In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID. This is how distributed transactions using two-phase commit protocols (such as XA or COM+ Transactions) work.
处理冲突时，你有两种选择：
现在，让我们考虑以下丢失更新异常：
丢失更新异常可能发生在已提交读隔离级别中。
在上图中我们可以看到，Alice 相信她可以从她的
账户中提取 40，但没有意识到 Bob 刚刚更改了账户余额，现在该账户中只剩下 20 了。悲观锁定
悲观锁定通过在帐户上获取共享或读取锁定来实现此目标，从而防止 Bob 更改帐户。
在上图中，Alice 和 Bob 都将在两个用户都已读取的
account表行上获取读锁。 当使用可重复读取或可序列化时，数据库会在 SQL Server 上获取这些锁。由于 Alice 和 Bob 都读取了 PK 值为
1的account，因此在其中一个用户释放读锁之前，他们都无法更改它。 这是因为写操作需要获取写/排它锁，而共享/读锁会阻止写/排它锁。只有在 Alice 提交事务并且释放
account行上的读锁后，BobUPDATE才会恢复并应用更改。 在 Alice 释放读锁之前，Bob 的 UPDATE 会阻塞。乐观锁定
乐观锁定允许发生冲突，但会在版本发生更改时应用 Alice 的 UPDATE 时检测到冲突。
这次，我们多了一个
版本列。 每次执行 UPDATE 或 DELETE 时，version列都会递增，并且它还用在 UPDATE 和 DELETE 语句的 WHERE 子句中。 为此，我们需要在执行 UPDATE 或 DELETE 之前发出 SELECT 并读取当前版本，否则，我们将不知道要传递给 WHERE 子句或递增什么版本值。应用程序级事务
关系数据库系统出现于 70 年代末 80 年代初，当时客户端通常通过终端连接到大型机。 这就是为什么我们仍然看到数据库系统定义诸如 SESSION 设置之类的术语。
如今，通过互联网，我们不再在同一个数据库事务的上下文中执行读取和写入，ACID 已不再足够。
例如，考虑以下用例：
如果没有乐观锁定，即使数据库事务使用可序列化，也无法捕获此丢失更新。 这是因为读取和写入是在单独的 HTTP 请求中执行的，因此是在不同的数据库事务上执行的。
因此，乐观锁定可以帮助您防止丢失更新，即使在使用也包含用户思考时间的应用程序级事务时也是如此。
结论
乐观锁定是一种非常有用的技术，即使在使用不太严格的隔离级别（如已提交读）或在后续数据库事务中执行读取和写入时，它也能正常工作。
乐观锁定的缺点是数据访问框架在捕获 OptimisticLockException 时将触发回滚，因此会丢失当前执行的事务之前所做的所有工作。
争用越多，冲突就越多，交易中止的可能性就越大。 对于数据库系统来说，回滚的成本可能很高，因为它需要恢复所有当前挂起的更改，这些更改可能涉及表行和索引记录。
因此，当冲突频繁发生时，悲观锁可能更合适，因为它减少了回滚事务的机会。
When dealing with conflicts, you have two options:
Now, let's consider the following Lost Update anomaly:
The Lost Update anomaly can happen in the Read Committed isolation level.
In the diagram above we can see that Alice believes she can withdraw 40 from her
accountbut does not realize that Bob has just changed the account balance, and now there are only 20 left in this account.Pessimistic Locking
Pessimistic locking achieves this goal by taking a shared or read lock on the account so Bob is prevented from changing the account.
In the diagram above, both Alice and Bob will acquire a read lock on the
accounttable row that both users have read. The database acquires these locks on SQL Server when using Repeatable Read or Serializable.Because both Alice and Bob have read the
accountwith the PK value of1, neither of them can change it until one user releases the read lock. This is because a write operation requires a write/exclusive lock acquisition, and shared/read locks prevent write/exclusive locks.Only after Alice has committed her transaction and the read lock was released on the
accountrow, BobUPDATEwill resume and apply the change. Until Alice releases the read lock, Bob's UPDATE blocks.Optimistic Locking
Optimistic Locking allows the conflict to occur but detects it upon applying Alice's UPDATE as the version has changed.
This time, we have an additional
versioncolumn. Theversioncolumn is incremented every time an UPDATE or DELETE is executed, and it is also used in the WHERE clause of the UPDATE and DELETE statements. For this to work, we need to issue the SELECT and read the currentversionprior to executing the UPDATE or DELETE, as otherwise, we would not know what version value to pass to the WHERE clause or to increment.Application-level transactions
Relational database systems have emerged in the late 70's early 80's when a client would, typically, connect to a mainframe via a terminal. That's why we still see database systems define terms such as SESSION setting.
Nowadays, over the Internet, we no longer execute reads and writes in the context of the same database transaction, and ACID is no longer sufficient.
For instance, consider the following use case:
Without optimistic locking, there is no way this Lost Update would have been caught even if the database transactions used Serializable. This is because reads and writes are executed in separate HTTP requests, hence on different database transactions.
So, optimistic locking can help you prevent Lost Updates even when using application-level transactions that incorporate the user-think time as well.
Conclusion
Optimistic locking is a very useful technique, and it works just fine even when using less-strict isolation levels, like Read Committed, or when reads and writes are executed in subsequent database transactions.
The downside of optimistic locking is that a rollback will be triggered by the data access framework upon catching an
OptimisticLockException, therefore losing all the work we've done previously by the currently executing transaction.The more contention, the more conflicts, and the greater the chance of aborting transactions. Rollbacks can be costly for the database system as it needs to revert all current pending changes which might involve both table rows and index records.
For this reason, pessimistic locking might be more suitable when conflicts happen frequently, as it reduces the chance of rolling back transactions.
当您预计不会发生很多冲突时，可以使用乐观锁定。 执行正常操作的成本较低，但如果确实发生冲突，您将支付更高的价格来解决冲突，因为交易会中止。
当预期发生冲突时使用悲观锁定。 违反同步的事务将被简单地阻止。
要选择适当的锁定机制，您必须估计读取和写入的数量并进行相应的规划。
Optimistic locking is used when you don't expect many collisions. It costs less to do a normal operation but if the collision DOES occur you would pay a higher price to resolve it as the transaction is aborted.
Pessimistic locking is used when a collision is anticipated. The transactions which would violate synchronization are simply blocked.
To select proper locking mechanism you have to estimate the amount of reads and writes and plan accordingly.
乐观的假设是，当你阅读它时，一切都不会改变。
悲观主义者假设某件事会发生，因此锁定它。
如果数据完全读取并不重要，请使用乐观模式。 您可能会得到奇怪的“脏”读 - 但它不太可能导致死锁等。
大多数 Web 应用程序都可以处理脏读 - 在极少数情况下，数据与下一次重新加载的数据不完全相符。
对于精确的数据操作（如许多金融交易），请使用悲观。 准确读取数据非常重要，没有未显示的更改 - 额外的锁定开销是值得的。
哦，Microsoft SQL Server 默认采用页面锁定 - 基本上是您正在阅读的行以及两侧的一些行。 行锁定更准确，但速度慢得多。 通常值得将事务设置为读提交或无锁，以避免读取时出现死锁。
Optimistic assumes that nothing's going to change while you're reading it.
Pessimistic assumes that something will and so locks it.
If it's not essential that the data is perfectly read use optimistic. You might get the odd 'dirty' read - but it's far less likely to result in deadlocks and the like.
Most web applications are fine with dirty reads - on the rare occasion the data doesn't exactly tally the next reload does.
For exact data operations (like in many financial transactions) use pessimistic. It's essential that the data is accurately read, with no un-shown changes - the extra locking overhead is worth it.
Oh, and Microsoft SQL server defaults to page locking - basically the row you're reading and a few either side. Row locking is more accurate but much slower. It's often worth setting your transactions to read-committed or no-lock to avoid deadlocks while reading.
我会想到另一种情况，悲观锁定是更好的选择。
对于乐观锁定，数据修改的每个参与者都必须同意使用这种锁定。 但是，如果有人修改数据而不关心版本列，这将破坏乐观锁定的整个想法。
I would think of one more case when pessimistic locking would be a better choice.
For optimistic locking every participant in data modification must agree in using this kind of locking. But if someone modifies the data without taking care about the version column, this will spoil the whole idea of the optimistic locking.
基本上有两个最受欢迎的答案。 第一个基本上说
另一个答案是
或者
正如本页所述。
我创建了我的答案来解释“保持连接”与“低冲突”之间的关系。
要了解哪种策略最适合您，不要考虑数据库每秒的事务数，而要考虑单个事务的持续时间。 通常，您打开交易、执行操作并关闭交易。 这是 ANSI 所考虑的一个简短的、经典的事务，并且很好地摆脱了锁定。 但是，如何实现许多客户同时预订相同房间/座位的门票预订系统呢？
您浏览报价，填写包含大量可用选项和当前价格的表格。 这需要花费大量时间，并且选项可能会过时，从您开始填写表格到按下“我同意”按钮之间的所有价格均无效，因为您访问的数据没有锁定，并且其他更敏捷的人已经干扰更改所有价格，您需要以新价格重新启动。
相反，您可以在阅读时锁定所有选项。 这是悲观的情景。 你知道为什么它很糟糕。 您的系统可能会被一个小丑搞砸，他只是简单地开始预订并开始吸烟。 在他完成之前没有人可以保留任何东西。 你的现金流降至零。 这就是为什么在现实中采取乐观保留的态度。 那些磨蹭太久的人不得不以更高的价格重新开始预订。
在这种乐观的方法中，您必须记录您读取的所有数据（如我的重复读取）并进行提交指出您的数据版本（我想以您在此报价中显示的价格购买股票，而不是当前价格）。 此时，将创建 ANSI 事务，该事务会锁定数据库、检查是否没有任何更改并提交/中止您的操作。 IMO，这是 MVCC 的有效模拟，它也与 Optimistic CC 相关联，并且还假设您的如果中止，事务将重新启动，即您将进行新的预订。 这里的交易涉及人类用户的决策。
我还不太了解如何手动实现 MVCC，但我认为具有重新启动选项的长时间运行事务是理解该主题的关键。 如果我有什么地方错了请纠正我。 我的回答是由 这个 Alex Kuznecov 激发的章。
There are basically two most popular answers. The first one basically says
Another answer is
or
As it is put on this page.
I created my answer to explain how "keep connection" is related to "low collisions".
To understand which strategy is best for you, think not about the Transactions Per Second your DB has but the duration of a single transaction. Normally, you open trasnaction, performa operation and close the transaction. This is a short, classical transaction ANSI had in mind and fine to get away with locking. But, how do you implement a ticket reservation system where many clients reserve the same rooms/seats at the same time?
You browse the offers, fill in the form with lots of available options and current prices. It takes a lot of time and options can become obsolete, all the prices invalid between you started to fill the form and press "I agree" button because there was no lock on the data you have accessed and somebody else, more agile, has intefered changing all the prices and you need to restart with new prices.
You could lock all the options as you read them, instead. This is pessimistic scenario. You see why it sucks. Your system can be brought down by a single clown who simply starts a reservation and goes smoking. Nobody can reserve anything before he finishes. Your cash flow drops to zero. That is why, optimistic reservations are used in reality. Those who dawdle too long have to restart their reservation at higher prices.
In this optimistic approach you have to record all the data that you read (as in mine Repeated Read) and come to the commit point with your version of data (I want to buy shares at the price you displayed in this quote, not current price). At this point, ANSI transaction is created, which locks the DB, checks if nothing is changed and commits/aborts your operation. IMO, this is effective emulation of MVCC, which is also associated with Optimistic CC and also assumes that your transaction restarts in case of abort, that is you will make a new reservation. A transaction here involves a human user decisions.
I am far from understanding how to implement the MVCC manually but I think that long-running transactions with option of restart is the key to understanding the subject. Correct me if I am wrong anywhere. My answer was motivated by this Alex Kuznecov chapter.
在大多数情况下，乐观锁定效率更高，性能更高。 在悲观锁定和乐观锁定之间进行选择时，请考虑以下因素：
如果存在大量更新且更新较多，则悲观锁定很有用。
用户尝试同时更新数据的机会相对较高
时间。 例如，如果每个操作可以更新大量
一次记录（银行可能会为每个记录添加利息收入）
帐户在每个月末），并且有两个应用程序正在运行
如果同时进行这样的操作，就会产生冲突。
悲观锁定也更适合包含频繁更新的小表的应用程序。 在这些所谓的热点的情况下，冲突的可能性非常大，乐观锁定会浪费精力回滚冲突事务。
如果发生冲突的可能性非常大，乐观锁定非常有用
低 – 记录较多，但用户相对较少，或者更新很少且主要是读类型操作。
In most cases, optimistic locking is more efficient and offers higher performance. When choosing between pessimistic and optimistic locking, consider the following:
Pessimistic locking is useful if there are a lot of updates and
relatively high chances of users trying to update data at the same
time. For example, if each operation can update a large number of
records at a time (the bank might add interest earnings to every
account at the end of each month), and two applications are running
such operations at the same time, they will have conflicts.
Pessimistic locking is also more appropriate in applications that contain small tables that are frequently updated. In the case of these so-called hotspots, conflicts are so probable that optimistic locking wastes effort in rolling back conflicting transactions.
Optimistic locking is useful if the possibility for conflicts is very
low – there are many records but relatively few users, or very few updates and mostly read-type operations.
假设在电子商务应用程序中，用户想要下订单。 该代码将由多个线程执行。 在悲观锁定中，当我们从数据库获取数据时，我们会锁定它，这样其他线程就无法修改它。 我们处理数据，更新数据，然后提交数据。 之后，我们释放锁。 这里的锁定持续时间很长，我们从一开始就锁定了数据库记录直到提交。
在
乐观锁定中，我们在不锁定的情况下获取数据并处理数据。 所以多个线程可以同时执行到目前为止的代码。 这会加快速度。 当我们更新时，我们锁定数据。 我们必须验证没有其他线程更新该记录。 例如，如果库存中有 100 件商品，我们必须将其更新为 99（因为您的代码可能是quantity=queantity-1），但如果另一个线程已经使用了 1，则它应该是 98。这里有竞争条件。 在这种情况下，我们重新启动线程，以便从头开始执行相同的代码。 但这是一个昂贵的操作，你已经结束了然后又重新开始。 如果我们有一些竞争条件，那不会有什么大不了的，如果竞争条件很高，就会有很多线程需要重新启动。 我们可能会循环运行。 在竞争条件较高的情况下，我们应该使用“悲观锁定”Let's say in an ecommerce app, a user wants to place an order. This code will get executed by multiple threads. In
pessimistic locking, when we get the data from the DB, we lock it so no other thread can modify it. We process the data, update the data, and then commit the data. After that, we release the lock. Locking duration is long here, we have locked the database record from the beginning till committing.In
optimistic locking, we get the data and process the data without locking. So multiple threads can execute the code so far concurrently. This will speed up. While we update, we lock the data. We have to verify that no other thread updated that record. For example, If we had 100 items in inventory and we have to update it to 99 (because your code might bequantity=queantity-1) but if another thread already used 1 it should be 98. We hadrace conditionhere. In this case, we restart the thread so we execute the same code from the beginning. But this is an expensive operation, you already came to end but then restart. if we had a few race conditions, that would not be a big deal, If the race condition was high, there would be a lot of threads to restart. We might run in a loop. In the race condition is high, we should be using `pessimistic locking乐观锁定的一个用例是让您的应用程序使用数据库来允许您的线程/主机之一“声明”一项任务。 这是一项经常对我派上用场的技术。
我能想到的最好的例子是使用数据库实现的任务队列，其中多个线程同时声明任务。 如果任务的状态为“可用”、“已声明”、“已完成”，则数据库查询可以显示类似“Set status='Claimed' where status='Available'”的内容。如果多个线程尝试以这种方式更改状态，除了第一个线程之外的所有线程都会因为脏数据而失败。
请注意，这是一个仅涉及乐观锁定的用例，因此，作为“当您预计不会发生很多冲突时使用乐观锁定”的替代方法，也可以使用它。您预计会发生冲突，但希望只有一笔交易成功。
One use case for optimistic locking is to have your application use the database to allow one of your threads / hosts to 'claim' a task. This is a technique that has come in handy for me on a regular basis.
The best example I can think of is for a task queue implemented using a database, with multiple threads claiming tasks concurrently. If a task has status 'Available', 'Claimed', 'Completed', a db query can say something like "Set status='Claimed' where status='Available'. If multiple threads try to change the status in this way, all but the first thread will fail because of dirty data.
Note that this is a use case involving only optimistic locking. So as an alternative to saying "Optimistic locking is used when you don't expect many collisions", it can also be used where you expect collisions but want exactly one transaction to succeed.
上面已经说了很多关于乐观锁和悲观锁的好话。
需要考虑的重要一点如下：
当使用乐观锁定时，我们需要注意应用程序如何从这些故障中恢复。
特别是在异步消息驱动架构中，这可能会导致消息处理无序或更新丢失。
需要仔细考虑失败场景。
Lot of good things have been said above about optimistic and pessimistic locking.
One important point to consider is as follows:
When using optimistic locking, we need to cautious of the fact that how will application recover from these failures.
Specially in asynchronous message driven architectures, this can lead of out of order message processing or lost updates.
Failures scenarios need to be thought through.
乐观锁定意味着读取行时不使用独占锁，因此丢失更新或写入倾斜< /strong> 不会被阻止。 因此，请使用乐观锁定：
悲观锁定意味着读取行时使用排它锁因此丢失更新或写入倾斜 被阻止。 因此，请使用悲观锁定：
在MySQL和PostgreSQL中，您可以将独占锁与
SELECT FOR UPDATE结合使用。您可以检查我对丢失更新的回答并编写倾斜示例具有乐观锁定（不带
SELECT FOR UPDATE）和悲观锁定（带SELECT FOR UPDATE）在MySQL中。Optimistic locking means exclusive lock is not used when reading a row so lost update or write skew is not prevented. So, use optimistic locking:
Pessimistic locking means exclusive lock is used when reading a row so lost update or write skew is prevented. So, use pessimistic locking:
In MySQL and PostgreSQL, you can use exclusive lock with
SELECT FOR UPDATE.You can check my answer of the lost update and write skew examples with optimistic locking(without
SELECT FOR UPDATE) and pessimistic locking(withSELECT FOR UPDATE)in MySQL.更实际的是，当更新分布式系统时，数据库中的乐观锁定可能不足以提供分布式系统所有部分所需的一致性。
例如，在AWS上构建的应用程序中，通常在数据库（例如DynamoDB）和存储（例如S3）中都有数据。 如果更新同时涉及 DynamoDB 和 S3，DynamoDB 中的乐观锁定仍可能导致 S3 中的数据不一致。 在这种类型的情况下，使用 DynamoDB 中保存的悲观锁可能更安全，直到 S3 更新完成。 事实上，AWS 提供了锁定用于此目的的库。
On a more practical note, when updating a distributed system, optimistic locking in the DB may be inadequate to provide the consistency needed across all parts of the distributed system.
For example, in applications built on AWS, it is common to have data in both a DB (e.g. DynamoDB) and a storage (e.g. S3). If an update touches both DynamoDB and S3, an optimistic locking in DynamoDB could still leave the data in S3 inconsistent. In this type of cases, it is probably safer to use a pessimistic lock that is held in DynamoDB until the S3 update is finished. In fact, AWS provides a locking library for this purpose.
乐观锁定：仅当更改提交到数据库时才锁定记录。
悲观锁定：记录在编辑时被锁定。
注意：在两种数据锁定模型中，锁定在更改提交到数据库后释放。
Optimistic locking : where a record is locked only when changes are committed to the database.
Pessimistic locking : where a record is locked while it is edited.
Note : In both data-locking models, the lock is released after the changes are committed to the database.