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

问题描述

我理解乐观锁和悲观锁的区别.现在有人可以向我解释一下我一般什么时候会使用任何一种吗?

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 是一个读取记录的策略，记下版本号(其他方法包括日期、时间戳或校验和/哈希)，并在写回记录之前检查版本是否未更改.当您写回记录时，您会过滤版本上的更新以确保它是原子的.(即在您检查版本并将记录写入磁盘之间没有更新)并一次更新版本.

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.

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

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.

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

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屋！