db2 与 Oracle 的锁的区别和比较

askgyliu · 发表于 2007-7-7 15:46

In DB2 Term:

While the Repeatable Read isolation level is the most restrictive of the isolation levels available, the Uncommitted Read isolation level is the least intrusive isolation level provided. In fact, when the Uncommitted Read isolation level is used, rows that are retrieved by a single transaction are only locked if another transaction attempts to drop or alter the table from which the rows were retrieved. Because rows often remain unlocked when this isolation level is used, dirty reads, nonrepeatable reads, and phantoms can occur. Therefore, the Uncommitted Read isolation level is commonly used for transactions that access read-only tables/views or transactions that execute queries on which uncommitted data from other transactions will have no adverse affect.

In most cases, transactions using the Uncommitted Read isolation level can read changes made to rows by other transactions before those changes have been committed. However, such transactions can neither see nor access tables, views, or indexes created by other concurrent transactions until those transactions themselves have been terminated. The same applies to existing tables, views, or indexes that have been dropped—transactions using the Uncommitted Read isolation level will only learn that these objects no longer exist when the transaction that dropped them is terminated. There is one exception to this behavior: When a transaction running under the Uncommitted Read isolation level uses an updatable cursor, the transaction will behave as if it is running under the Cursor Stability isolation level, and the constraints of the Cursor Stability isolation level will apply.

So how does the Uncommitted Read isolation level affect our hotel reservation application? Now when a customer retrieves a list of rooms available for a given range of dates, you will be able to change the room rate for any room in the hotel, and other customers will be able to make or cancel reservations over any date range for any room. Furthermore, the list produced for the first customer may contain rooms that other customers have chosen to cancel reservations for, but whose cancellations have not yet been committed to the database.

askgyliu · 发表于 2007-7-7 16:04

DB2 具体的LOCK IMPLEMENTATION比这简单的四个分级复杂多了。有十三个不同的MODES。

所以感觉LOCKING是那种无能为力的事情。只能是尽可能地多加了解，编程时尽可能只要求必需的LOCKING，再加上尽可能地优化程序。

另外，记得尽可能早地COMMIT-一旦UOW完成就COMMIT。

askgyliu · 发表于 2007-7-8 00:36

最初由 macrozeng 发布
[B]原来一直在做 Oracle ，“读不阻止写，写不阻止读”的想法扎根很深，但是在用 DB2 的过程中总感觉 DB2 的锁的实现、使用以及管理都麻烦些，不知道各位有没有类似的体会，到底怎么才能正确使用 DB2 的锁呢？各位是否有什么心得，欢迎大家谈这方面感受和技术：） [/B]

看了一下论坛的旧贴。LZ可以参考一下下面的讨论。

http://www.itpub.net/207514.html

感觉只做ORACLE的人刚来做DB2是会有很大的CULTURE SHOCK。

还是用回我那次看电影的经验。明明看好了一个座位，想定下来时就没了。我知道那BACKEND DATABASE应当是ORACLE。

LZ是从ORACLE的经验来的，可以重翻一下THOMAS KYTE关于ORACLE SERIALIZED TRANSACTION的一些讨论。

BTW，我算是在DB2和ORACLE都混的那种，没有PREFERENCE的非典型数据库爱好者。