读取未提交的mvcc数据库
假设我想以读提交模式（在 postgres 中）执行以下事务。
T1: r(A) -> w(A)
T2: r(A) -> w(A)
如果按以下顺序调用操作：
r1(A)->r2(A)->w1(A)->c1->w2(A)->c2
我认为 T2 必须在 r(A) 处等待。因为T1会在第一次读取时为A设置排他锁，因为它想稍后再写入。但是使用 MVCC 就没有读锁了吗？
现在我有两个问题：
如果我使用 JDBC 读取一些数据，然后执行一个单独的命令来插入读取的数据。数据库如何知道它只在读时必须进行排他锁？据我所知，2PL 中不允许将读锁增加为写锁。
我认为我的假设是错误的......这种情况在哪里等待或者一笔交易被杀死？未提交的读取不应允许丢失更新，但我看不出这是如何工作的。
如果有人能帮助我，我会很高兴。谢谢
say I want to do the following transactions in read committed mode (in postgres).
T1: r(A) -> w(A)
T2: r(A) -> w(A)
If the operations where called in this order:
r1(A)->r2(A)->w1(A)->c1->w2(A)->c2
I would exspect that T2 has to wait at r(A). Because T1 would set an exclusive lock for A at the first read, because it wants to write it later. But with MVCC there are are no read locks?
Now i've got 2 questions:
If I use JDBC to read some data and then execute a separte command for inserting the read data. How does the database know that it has to make an exclusiv lock when it is only reading? Increasing an read lock to a write lock is not allowed in 2PL, as far as I know.
I think my assumtions are wrong... Where does this scenario wait or is one transaction killed? Read uncommitted shouldn't allow lost updates, but I can't see how this can work.
I would be happy if someone could help me. Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您在 select 语句中指定
for update，则会出现写锁定。在这种情况下，如果 r2(A) 尝试锁定与 r1(A) 相同的行，则 r2(A) 将等待读取。http://www.postgresql.org/docs/9.0/interactive/explicit -locking.html
如果两个事务开始并最终请求彼此已锁定的行，则会发生死锁：
There are write locks if you specify
for updatein your select statements. In that case, r2(A) would wait to read if it's trying to lock the same rows as r1(A).http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html
A deadlock occurs if two transactions start and end up requesting each others already locked rows:
“但是使用 MVCC 就没有读锁了？”
MVCC 是一个不同的野兽。 MVCC 中没有“锁”，因为在这种情况下，系统会维护并发运行的事务可能需要的多个版本的单行。行的“以前的内容”不会“因更新而丢失”（即物理上覆盖和破坏），从而确保读者不会看到“新更新”，通过将读者的查询“重定向”到“以前的内容”，未锁定（因此称为“快照隔离”）。请注意，MVCC 原则上不能应用于更新交易。
“如果我使用 JDBC 读取一些数据，然后执行单独的命令来插入读取的数据。数据库如何知道它只在读取时必须进行排它锁？将读取锁增加为写入锁不是据我所知，2PL 是允许的。”
你对 2PL 的看法是错误的。 2PL 意味着获取的锁直到提交时才被释放。这并不意味着现有的锁不能被加固。顺便说一句：这就是为什么诸如“游标稳定性”之类的隔离级别不是 2PL 的原因：它们确实会在提交时间之前释放读锁。
"But with MVCC there are are no read locks?"
MVCC is a different beast. There are no "locks" in MVCC because in that scenario, the system maintains as many versions of a single row as might be needed by the transactions that are running concurrently. "Former contents" of a row are not "lost by an update" (i.e. physically overwritten and destroyed), and thus making sure that a reader does not get to see "new updates", is addressed by "redirecting" that reader's inquiries to the "former content", which is not locked (hence the term "snapshot isolation"). Note that MVCC, in principle, cannot be applied to updating transactions.
"If I use JDBC to read some data and then execute a separate command for inserting the read data. How does the database know that it has to make an exclusive lock when it is only reading? Increasing an read lock to a write lock is not allowed in 2PL, as far as I know."
You are wrong about 2PL. 2PL means that acquired locks are never released until commit time. It does not mean that an existing lock cannot be strengthened. Incidentally : that is why isolation levels such as "cursor stability" are not 2PL : they do release read locks prior to commit time.
PostgreSQL 中的默认事务模式是 READ COMMITTED，但是 READ COMMITTED 并不能提供您正在寻找的序列化级别。
您正在寻找可串行化事务级别。阅读 PostgreSQL 的文档后，查看 SET TRANSACTION 命令事务序列化级别，特别是可串行化模式。 PostgreSQL 的 MVCC 文档 也值得一读。
干杯。
The default transaction mode in PostgreSQL is READ COMMITTED, however READ COMMITTED does not provide the level of serialization that you are looking for.
You are looking for the SERIALIZABLE transaction level. Look at the SET TRANSACTION command after reading PostgreSQL's documentation on Transaction Serialization Levels, specifically the SERIALIZABLE mode. PostgreSQL's MVCC docs are also worth reading.
Cheers.