乐观锁定与悲观锁定 [英] Optimistic vs. Pessimistic locking

问题描述

我了解乐观锁定和悲观锁定之间的区别.现在有人可以向我解释什么时候我一般会使用其中一个吗?

这个问题的答案会根据我是否使用存储过程执行查询而改变吗?

但只是检查一下，乐观的意思是阅读时不要锁定桌子"，悲观的意思是阅读时锁定桌子".

解决方案

乐观锁定是读取记录的策略，记下版本号(执行此操作的其他方法包括日期，时间戳或校验和/哈希)，并在写回记录之前检查版本是否未更改.当您回写记录时，您将过滤版本更新以确保它是原子的. (即，在您检查版本并将记录写入磁盘之间，尚未更新)并一次点击即可更新版本.

如果记录很脏(即与您的记录不同的版本)，您将中止交易，并且用户可以重新启动它.

此策略最适用于不必为会话保留与数据库的连接的大容量系统和三层体系结构.在这种情况下，客户端实际上无法维护数据库锁定，因为连接是从池中获取的，并且您可能不会在从一个访问到另一个访问的访问中使用相同的连接.

悲观锁定是指您锁定记录以独占使用直到您拥有完成了.它具有比乐观锁定更好的完整性，但是需要您谨慎设计应用程序，以避免死锁 .要使用悲观锁定，您需要直接连接到数据库(通常两个层客户端服务器应用程序)或可以独立于连接使用的外部可用交易ID.

在后一种情况下，您使用TxID打开事务，然后使用该ID重新连接. DBMS维护锁，并允许您通过TxID备份会话.这就是使用两阶段提交协议(例如 XA 或 COM +交易).

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."

解决方案

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.

这篇关于乐观锁定与悲观锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！