数据库如何通过并发控制保持一致性
文章解释并发事务导致数据损坏的四种方式，并介绍悲观锁、乐观锁、隔离级别等解决方案。
中文处理结果
想象一个银行账户有 100 美元。假设两笔各 10 美元的取款请求同时到达。然而，在两笔取款完成后，账户余额仍然是 90 美元。
从纯技术层面看，两个取款请求都成功完成，没有错误。但账户余额是错误的，剩余余额本应是 80 美元。
单独来看，两个事务都是正确的。每个都读取了正确的余额并执行了正确的计算。然而，这里存在一个 bug。这个 bug 的原因是两笔事务的重叠。
你可能认为这种重叠是罕见情况。但事实并非如此。重叠事务或多或少是数据库运行的正常状态。在任何给定时刻，多个进程都在尝试向数据库写入内容，通常是在同一组记录上。只需其中两个在几毫秒的窗口内发生碰撞，就会出现这类 bug。
那么我们如何处理这类 bug？
这正是本文要尝试回答的问题。以下是本文涵盖的内容：
- 多个事务如何导致数据损坏？
- 数据损坏的 4 种方式
- 处理数据冲突的不同方法
- 提前阻止所有人（悲观锁）
- 事后检查（乐观锁）
- 数据库如何让读写者不再互相等待？
- 通过隔离级别选择正确的数据保护级别
- 严格但不慢：让最安全设置可用的想法
- 总结
多个事务如何导致数据损坏？
阅读更多
原始正文摘录
How Databases Keep Their Sanity with Concurrency Control
Imagine a bank account with $100. Let’s assume that two separate withdrawal requests of $10 each arrive at the same moment. However, after the two withdrawals are made, the account still ends up $90.
On a purely technical level, both withdrawal requests completed successfully. There was no error. But the account balance is wrong. The remaining balance should have been $80.
On its own, both transactions were correct. Each read the correct balance and performed the correct calculations. And yet, there is a bug. The reason for this bug was the overlap between the two transactions.
You might think this type of overlap is a rare condition. However, this is not true. Overlapping transactions are more or less the normal condition in which databases operate. At any given point in time, multiple processes are trying to write something to a database, often on the same set of records. It just takes two of them to collide with each other inside a window of a few milliseconds for these types of bugs to show up.
So how do we handle such bugs?
This is what we are going to try to answer in this article. Here’s what we will cover:
- How does data get corrupted due to multiple transactions?
- 4 ways the data gets corrupted
- Different ways to handle data conflicts
- Block everyone up front (Pessimistic Locking)
- Gamble and check afterwards (Optimistic Locking)
- How databases stopped making readers and writers wait for each other?
- Picking the right level of data protection with isolation levels
- Strict without slow: the idea that made the safest setting usable
- Summary
How Data Gets Corrupted Due to Multiple Transactions?
Read more