对象图和 READ_COMMITTED 事务隔离
警告：几年前我遇到了这个问题。我忘记了我试图解决的实际问题，所以我用涉及链表的理论问题代替了它。
我已经尝试使用 READ_COMMITTED 事务隔离级别一年多了，但我对由于缺乏经验而产生的错误感到非常沮丧。如果你们能提醒我我在这里缺少什么，我将不胜感激。
我实现的几乎所有应用程序逻辑都无法用单个 SQL 查询来表达。也就是说，我几乎总是最终不得不遍历对象图。我不可避免地会在同一事务中遇到不一致的状态。例如，假设您在数据库中存储一个单链表，并且您想要查明该列表是否包含循环（下面将更详细地描述）。我想不出任何方法可以在 READ_COMMITTED 下执行此操作，因为当我在图表中行走时，可以从我的脚下更新出站引用。我只能看到 READ_COMMITTED 工作适用于可以使用单个 SQL 语句表示的查询。
- 数据库模式是
Node[id, name, next_id] 最初数据库包含：
A -> B-> C->一个- 线程 1 读取：
A -> B，B -> C - 线程 2 将数据库更新为：
D -> B-> C-> D - 线程 1 读取：
C -> D - 线程 1 没有检测到循环，尽管应该检测到循环。从线程 1 的角度来看，它是：
A -> B-> C-> D但它应该检测到以下循环之一：
- 线程 1 读取：
A -> B-> C->一个或 <代码>D - > B-> C-> D
CAVEAT: I ran into this problem a few years ago. I forget the actual problem I was trying to solve so I have substituted it with a theoretical problem involving a linked list.
I've been trying to use READ_COMMITTED transaction isolation level for over a year now and I'm getting very frustrated with the bugs resulting from my lack of experience. I would appreciate it if you guys could give me a head's up as to what I am missing here.
Pretty much all application logic I implement cannot be expressed in terms of a single SQL query. That is, I almost always end up having to walk the object graph. Inevitably I run into inconsistent state from within the same transaction. For example, imagine you store a singly-linked list in the database and you want to find out whether the list contains a loop (described in greater detail below). I can't think of any way to do this under READ_COMMITTED because the outbound references could be updated from underneath my feet as I walk the graph. I can only see READ_COMMITTED working is for queries which can be represented using a single SQL statement.
- The database schema is
Node[id, name, next_id] Initially the database contains:
A -> B -> C -> A- Thread 1 reads:
A -> B,B -> C - Thread 2 updates the database to:
D -> B -> C -> D - Thread 1 reads:
C -> D - Thread 1 detects no loop although one should have been detected. From Thread 1's point of view it read:
A -> B -> C -> Dbut it should have detected one of the following loops:
- Thread 1 reads:
A -> B -> C -> A orD -> B -> C -> D
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由 Michal Bergmann 提供的回答：http://groups.google .com/group/h2-database/browse_frm/thread/8f253177e2b6c543?tvc=1
Answer courtesy of Michal Bergmann: http://groups.google.com/group/h2-database/browse_frm/thread/8f253177e2b6c543?tvc=1