锁定数据库
您好，我正在尝试查看是什么锁定了数据库，并发现了两种类型的锁定。乐观锁定和悲观锁定。我在 Wiki 上找到了一些文章，但我想了解更多！有人可以向我解释一下这些锁定吗？我们应该只在需要独占访问某些东西时才使用锁定吗？只有当我们使用事务时才会发生锁定？ 提前致谢。
凯文
Hi I'm trying to see what's locking the database and found 2 types of locking. Optimistic and Pessimistic Locking. I found some articles on Wiki but I would like to know more ! Can someone explain me about those locking ? We should only use locking when we need exclusive access to something? Locking only happens when we use transaction?
Thanks in advance.
Kevin
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
乐观锁根本就不是锁。
它的工作原理是在您开始进行更改之前记录系统所处的状态，然后继续进行这些更改，并假设（乐观地）没有其他人愿意进行冲突的更新。正如您即将自动提交这些更改一样，您将检查其他人是否同时更新了相同的数据。在这种情况下，您的提交会失败。
Subversion 例如使用乐观锁定。当您尝试提交时，您必须处理任何冲突，但在此之前，您可以在工作副本上执行任何您想要的操作。
悲观锁与真实锁一起使用。假设会出现争用，您可以在接触之前锁定要更新的所有内容。其他人都必须等待您提交或回滚。
当使用支持事务的关系数据库时，数据库通常会在内部处理锁定（例如当您发出 UPDATE 语句时），因此对于正常的在线处理，您不需要自己处理此问题。仅当您想要进行维护工作或大批量工作时，您有时才需要锁定表。
您需要它来防止其他会话的操作冲突。一般来说，这意味着更新。读取数据通常可以同时进行。
是的。您将在进行交易时积累锁，并在交易结束时释放所有锁。请注意，自动提交模式下的单个 SQL 命令本身仍然是一个事务。
Optimistic locking is no locking at all.
It works by noting the state the system was in before you started making your changes, and then going ahead and just making those changes, assuming (optimistically) that no one else will want to make conflicting updates. Just as you are about to atomically commit those changes, you would check if in the mean-time someone else has also updated the same data. In which case, your commit fails.
Subversion for example using optimistic locking. When you try to commit, you have to handle any conflicts, but before that, you can do on your working copy whatever you want.
Pessimistic locks work with real locks. Assuming that there will be contention, you lock everything you want to update before touching it. Everyone else will have to wait for you to commit or rollback.
When using a relational database with transaction support, the database usually takes care of locking internally (such as when you issue an UPDATE statement), so for normal online processing you do not need to handle this yourself. Only if you want to do maintenance work or large batches do you sometimes want to lock down tables.
You need it to prevent conflicting operations from other sessions. In general, this means updates. Reading data can normally go on concurrently.
Yes. You will accumulate locks while proceeding with your transaction, releasing all of them at the end of it. Note that a single SQL command in auto-commit mode is still a transaction by itself.
事务隔离级别还指定锁定行为。 BOL指的是：事务隔离级别控制：
读取数据时是否加锁，以及请求什么类型的锁。
读锁保持多长时间。
读取操作是否引用由另一个事务修改的行：
阻塞，直到释放该行上的排他锁。
检索语句或事务启动时存在的行的已提交版本。
读取未提交的数据修改。
默认级别为：
读未提交（最低级别，其中事务隔离仅足以确保不读取物理损坏的数据）
读已提交（数据库引擎默认级别）
可重复读
可串行化（最高级别，其中事务彼此完全隔离）
Transactions isolation levels also specify the locking behaviour. BOL refers:Transaction isolation levels control:
Whether locks are taken when data is read, and what type of locks are requested.
How long the read locks are held.
Whether a read operation referencing rows modified by another transaction:
Blocks until the exclusive lock on the row is freed.
Retrieves the committed version of the row that existed at the time the statement or transaction started.
Reads the uncommitted data modification.
The default levels are:
Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
Read committed (Database Engine default level)
Repeatable read
Serializable (the highest level, where transactions are completely isolated from one another)