锁定以处理并发 - 一个好主意？ [英] Locking to handle concurrency-- a good idea?

问题描述

为了处理并发问题，锁定 - 任何形式的锁定，无论是行，表还是数据库锁定一个很好的解决方案？

In order to handle concurrency issue, is locking-- any form of locking, whether it's row, table or database locking a good solution?

如果不，如何处理并发问题？

推荐答案

如果你相信Oracle，不，这是因为Oracle非常愿意避免它。

If you believe Oracle, no, not at all. That's because Oracle went to great lengths to avoid it.

问题是读者可以阻止作家和作家阻止读者，作家必须等到所有读者完成在一行之前可以写。这会拖延写入过程及其调用者。独家锁（用于写入）持有到交易结束，以防交易必须回滚 - 这将阻止其他交易看到新值，直到交易提交。

The problem is that readers can block writers and writers block readers, and a writer has to wait until all readers have finished with a row before it can write. That delays the writing process and its caller. Exclusive locks (for writing) are held to the end of the transaction, in case the transaction has to be rolled back - this stops other transactions seeing the new value until the transaction commits.

在实践中，如果没有太多争用，锁定通常是正常的，与任何并发编程相同。如果行/页/表的争用太多（没有多少数据库服务器执行整个DB锁定），则会导致事务依次执行而不是同时执行。

In practice locking is generally fine if there isn't too much contention, the same as with any concurrent programming. If there's too much contention for a row/page/table (not many database servers do whole-DB locking), it will cause the transactions to execute in turn rather than concurrently.

Oracle使用行版本，而不是锁定一行来写入它，而是创建一个新版本的行。需要重复读取的读者记住他们读取的行的哪个版本。但是，如果读取器读取的读取器尝试更新已由另一个作者更新的行，因为该事务读取它将会发生错误;这是停止丢失的更新。为了确保你可以更新一行，你必须说SELECT是FOR UPDATE;如果这样做，它需要一个锁 - 一次只能有一个事务可以持有一个行FOR UPDATE，并且冲突的事务必须等待。

Oracle uses row-versioning, where instead of locking a row to write it, a new version of the row is created instead. Readers that need to repeat their reads remember which version of the row they read. However, an error will occur if a reader that's remembering its reads tries to update a row that has been updated by another writer since this transaction read it; this is to stop lost updates. To ensure you can update a row, you have to say that the SELECT is FOR UPDATE; if you do that, it takes a lock - only one transaction can hold a row FOR UPDATE at a time, and a conflicting transaction has to wait.

SQL Server 2005和稍后支持快照隔离，它们是行版本的名称。再次，如果您需要更新刚刚读取的一些数据，则应该要求更新锁 - 在SQL Server中，使用WITH（UPDLOCK）。

SQL Server 2005 and later support Snapshot Isolation, which is their name for row-versioning. Again, you should ask for update locks if you need to update some data you just read - in SQL Server, use WITH (UPDLOCK).

锁定的另一个问题是僵局的可能性。这只是两个交易每个都持有对另一个需求的资源的锁定，或者一般来说，一个交易的周期保持彼此需要进行的锁。数据库服务器通常会检测到这个死锁并杀死其中一个事务，将其滚回来，然后需要重试该操作。任何情况下，您有多个并发事务修改相同的行有潜在的死锁。如果以不同的顺序触摸行，则会发生死锁;执行数据库服务器将使用的顺序非常困难（通常您希望优化器选择最快的顺序，这不一定在不同查询中保持一致）。

A further problem with locking is the likelihood of deadlocks. This is simply where two transactions each hold a lock on a resource the other needs, or in general a cycle of transactions hold locks that each other need to progress. The database server will generally detect this deadlock and kill one of the transactions, rolling it back - you then need to retry the operation. Any situation where you have multiple concurrent transactions modifying the same rows has potential for deadlock. The deadlock will occur if the rows are touched in a different order; it's very hard to enforce the order that the database server will use (generally you want the optimizer to pick the fastest order, which won't necessarily be consistent across different queries).

通常我会建议与线程相同 - 使用锁定，直到您可以证明它们导致可扩展性问题，然后解决如何使最关键的部分无锁。

Generally I would suggest the same as with threading - go with locks until you can prove that they're causing a scalability problem, then work out how to make the most critical sections lock-free.

这篇关于锁定以处理并发 - 一个好主意？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！