db2 与 Oracle 的锁的区别和比较

macrozeng

Db2 回退靠Log实现，造成了 LOG 竞争激烈，（所以 db2 默认是 autocommit）。而且由于没有专门的 undo tablespace 机制那么就无法实现类似 FlashBack 的机制。楼上的 ITL LOCKING 的例子也太极端了点 ：）

askgyliu

ORACLE TABLE，只要时不时地UPDATE给每个ROW多几个BYTES，过一段时间就会出现我所描述的ITL LOCKING了。特别是真的有很多CONCURRENT UPDATE的话。

我给的这个例子很极端，是为了很快就可以拿到我要的效果。

最要命的是，过了一段时间(好象是10秒左右)，那个在ITL WAITING的TRANSACTION会固执地只等一个固定的ITL SLOT！所以就会出现我所描述的那种即使已经有FREE ITL SLOT，那个TRANSACTION还会继续ITL WAITING的情况。

DB2 V9的CONCURRENCY应当是好很多了。其实DB2有个好处，就是BY DEFAULT所有的TRANSACTIONS看到的肯定是CONSISTENT的数据(因为被LOCK住了)。就是强调“一致性”吧。而ORACLE要做到这一点就得花额外的功夫了，结果也是牺牲了CONCURRENCY。

南来一味凉

最初由 macrozeng 发布
[B]大家还可以参考 ITPUB 杂志 ORACLE的隔离级别

http://epub.itpub.net/3/4.htm

里面明显区分了 read uncommitted 和 read committed [/B]

里面的确明显区分了read uncommitted 和 read committed，不过不是Oracle区分的，是SQL92(SQL99)区分的。

Oracle只提供了SQL92(SQL99)中的read committed和serializable。为什么没有read uncommitted呢？

按SQL标准的逻辑read uncommitted可以提供最大的并发性，但会出现Dirty read。

SQL99的Dirty read的定义如下：
P1 (‘‘Dirty read’’): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row
before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that
was never committed and that may thus be considered to have never existed.

参照SQL99的Table 10—SQL-transaction isolation levels and the three phenomena
4个隔离级解决3种并发冲突。大部分份数据库厂商的理解(我读完SQL99也是这样理解的，不知道大家如何)是在Read uncommitted可以读到Dirty read。如果在Read committed要解决Dirty read就在遇到T1正在修改而没有提交时, T2不能访问，直到T1提交。

而Oracle是在Read committed下如果有访问冲突给一个曾经提交过的数据。的确不会有‘‘Dirty read’’，但是这里SQL99的‘‘Dirty read’’指的是一种并发访问的现象。我前面用Dirty read的确不准确，容易引起混淆，我当时想表述的是“脏”的数据--不管它“曾经” “确定”存在过或“现在”“临时存”着在反正都是脏数据。如果有更好的词来代替我很乐意接受。

重点是表述“曾经” “确定”存在过数据和“现在”“临时存”的数据是半斤和八两的关系,谁也不要五十步笑百步。

打个可能不恰当的比方，应用逻辑提交，取消可能是在一个事务里完成，一个曾经存在的然后又被rollback的数据也是存在过的"历史数据"呀。因为数据库rollback大部分是应用控制的。可以把应用逻辑拆分成几个独立事务，把rollback用增删改来实现，写个自己的大事务控制。

也许这个比喻很无聊，但是可以说明第一所谓“存在过的历史数据”没有绝对定义。第二对定义“存在过的历史数据”，并在某个时候返回给用户这件事，很多应用并不关心，就象规定全天下都用Oracle或者都用DB2一样都可以。

至于隔离级、锁机制和并发、性能的关系。我没见过DB2真正深入的锁机制，并发控制的文章，哪位有可以share一下。影响性能并发的也不单单只是锁的因素，应用、数据库其他模块的设计都有关系。实际的测试数据最说明问题。

中国银联用的DB2做交易系统，应该算国内最大的OLTP吧，每天上千万笔的交易。算起来也很大并发呀。
当然也许并没有在某条记录上并发这么多。也许有一条记录上很大并发的需要，但应用上做了优化。

要谈CONCURRENCY，只怕也要先定义清楚CONCURRENCY。是指在线并发用户，并发数据库连接，并发执行的SQL，某条SQL的并发度，某条记录上的并发访问数（并发读，并发写）。。。

以前遇到客户有很多用户对同一条记录很频繁的update，最多也只能放到内存去update，该排队的还是要排队。

askgyliu

觉得对于ISO标准还是不用太看重。能抓老鼠的就是好猫；而能给出最好最准的数据库就是好数据库。

在ORACLE现在的IMPLEMENTATION，根本就没办法READ UNCOMMITTED DATA。

仔细想想，ORACLE象是比较偏重于SESSION本身的DATA CONSISTENCY，而DB2是侧重于不同TRANSACTIONS之间的CONSISTENCY。

一个设计好好的OLTP系统，DB2的LOCKING应当不会造成问题。而在OLTP系统中出现LOCK ESCALATION是不可思议的事。

在DB2的DSS/DWH系统中，LOCK ESCALATION是家常便饭。但是，这种系统经常是晚上做LOADING，白天做QUERY，相互间也没很多BLOCKING。

感觉数据库中的LOCKING是最无从着力的地方。

hx9999

查了一些资料，应该说oracle里的read committed＝db2里的Cursor Stability，DB2支持ANSI的四个隔离级别，不过名字和ANSI的正好有点混，db2里是RR,RS,CS,UR,分别对应ANSI里的SERIALIZABLE,REPEATABLE READ,READ COMMITTED,READ UNCOMMITTED，ORACLE实现了SERIALIZABLE和READ COMMITTED,对应于DB2就是RR和CS。

askgyliu

SHARED and UPDATE LOCKS: Oracle won't need them. You can explicitly ask for shared locks with Oracle's non-standard SQL-extension LOCK TABLE statement, but typical transactions get only exclusive locks. These locks are done with marks on the wall, not with RAM records.

ESCALATION: Oracle won't do it. All automatic exclusive locks have row-level granularity.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED: Oracle won't actually support READ UNCOMMITTED. Instead, it upgrades to a higher level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED: Oracle supports READ COMMITTED by exclusive-locking writes and by using the log to read data rows "as at start of SQL statement."

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ: Oracle won't actually support REPEATABLE READ. Instead, it upgrades to SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE: Oracle supports SERIALIZABLE by exclusive-locking writes and by using the log to read data rows "as at start of transaction."

SET TRANSACTION READ ONLY: Oracle supports READ ONLY transactions by using the log to read data rows "as at start of transaction"—which in effect means that a READ ONLY transaction will effectively be at "repeatable read" isolation level.

askgyliu

READ UNCOMMITTED is the lowest level of transaction isolation; it also gives you the highest concurrency level. No locks are issued and no locks are checked during the transaction. READ UNCOMMITTED tells the DBMS you want it to allow reading of rows that have been written—but not committed—by other transactions, and always implies a READ ONLY transaction, that is, no data changes allowed.

The next level of transaction isolation, READ COMMITTED, still allows for a fairly high concurrency level. Shared locks are mandatory but can be released before the transaction ends. READ COMMITTED tells the DBMS you want it to allow reading of rows that have been written by other transactions only after they have been committed.

REPEATABLE READ is the next level; concurrency drops sharply. Shared locks are mandatory and will not be released until the transaction ends. REPEATABLE READ tells the DBMS it must not allow a situation where one transaction gets two sets of data from two reads of the same set of rows because a second transaction changed that set of rows between the two reads.

SERIALIZABLE is the final and highest level of transaction isolation and thus gives you the lowest concurrency level. The DBMS may lock whole tables, not just rows (or paths to objects rather than just the objects) during the course of a transaction. SERIALIZABLE tells the DBMS you want it to execute concurrent transactions in a manner that produces the same effect as a serial execution of those transactions.

askgyliu

In DB2 Term:

The Repeatable Read isolation level completely isolates one transaction from the effects of other concurrent transactions. When this isolation level is used, every row that is referenced in any manner by the isolated transaction is "locked" for the duration of that transaction. As a result, if the same SELECT SQL statement is issued two or more times within the same transaction, the result data set produced will always be the same. (Lost updates, dirty reads, nonrepeatable reads, and phantoms cannot occur.) In addition, transactions using the Repeatable Read isolation level will not see changes made to other rows by other transactions until those changes have been committed

Transactions using the Repeatable Read isolation level can retrieve the same set of rows multiple times and perform any number of operations on them until terminated by performing either a commit or a rollback operation. However, no other transaction is allowed to perform any insert, update, or delete operation that would affect the set of rows being accessed by the isolating transaction—as long as that transaction remains active. To ensure that the data being accessed by a transaction running under the Repeatable Read isolation level is not adversely affected by other transactions, each row referenced by the isolating transaction is locked—not just the rows that are actually retrieved and/or modified. Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are acquired and held on all 1,000 rows scanned—not just on the 10 rows retrieved

So how does this isolation level work in a real-world situation? Suppose you own a large hotel and you have a Web site that allows individuals to reserve rooms on a first-come, first-served basis. If your hotel reservation application runs under the Repeatable Read isolation level, whenever a customer retrieves a list of all rooms available for a given range of dates, you will not be able to change the room rate for those rooms during the date range specified, nor will other customers be able to make or cancel reservations that would cause the list to change if it were generated again—as long as the transaction that produced the list is active. (However, you can change room rates for any room that was not scanned in response to the first customer's query. Likewise, other customers can make or cancel room reservations for any room that was not scanned in response to the first customer's query.)

askgyliu

In DB2 Term:

Unlike the Repeatable Read isolation level, the Read Stability isolation level does not completely isolate one transaction from the effects of other concurrent transactions. That is because when the Read Stability isolation level is used, only rows that are actually retrieved by a single transaction are locked for the duration of that transaction. Thus when this isolation level is used, if the same SELECT SQL statement is issued two or more times within the same transaction, the result data set produced may not always be the same. (Lost updates, dirty reads, and nonrepeatable reads cannot occur; phantoms, however, can and may be seen.) In addition, transactions using the Read Stability isolation level will not see changes made to other rows by other transactions until those changes have been committed.

Transactions using the Read Stability isolation level can retrieve a set of rows and perform any number of operations on them until terminated by performing either a commit or a rollback operation. However, no other transaction is allowed to perform any update or delete operation that would affect the set of rows that were retrieved by the isolating transaction—as long as that transaction exists. (However, other transactions can perform insert operations, and if the transaction running under the Read Stability isolation level executes the same query multiple times, rows inserted between each query by other concurrent transactions may appear in subsequent result data sets produced. As mentioned earlier, such rows are called "phantoms."

Unlike the Repeatable Read isolation level, where every row that is referenced in any way by the isolating transaction is locked, when the Read Stability isolation level is used, only the rows that are actually retrieved and/or modified by the isolating transaction are locked. Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are only acquired and held on the 10 rows retrieved—not on all 1,000 rows scanned. (And because fewer locks are acquired, more transactions can run concurrently.)

So how does this isolation level change the way our hotel reservation application works? 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 that does not appear on the list, and other customers will be able to cancel room reservations for rooms that had been reserved for the date range specified by the first customer's query. Therefore, if the customer generates the list of available rooms again (before the transaction that submitted the query terminates), the list produced may contain new room rates and/or rooms that were not available the first time the list was generated.

askgyliu

In DB2 Term:

The Cursor Stability isolation level is even more relaxed than the Read Stability isolation level in the way it isolates one transaction from the effects of other concurrent transactions. When the Cursor Stability isolation level is used, only the row that is currently being referenced by a cursor is locked. The lock acquired remains in effect until the cursor is repositioned—more often than not by executing the FETCH SQL statement—or until the isolating transaction terminates. (If the cursor is repositioned, the lock being held on the last row read is released and a new lock is acquired for the row the cursor is now positioned on.).

When a transaction using the Cursor Stability isolation level retrieves a row from a table via an updatable cursor, no other transaction can update or delete that row while the cursor is positioned on it. However, other transactions can add new rows to the table, as well as perform update and/or delete operations on rows positioned on either side of the locked row, provided the locked row itself was not accessed using an index. Furthermore, if the isolating transaction modifies any row it retrieves, no other transaction can update or delete that row until the isolating transaction is terminated, even when the cursor is no longer positioned on the modified row. As you might imagine, when the Cursor Stability isolation level is used, if the same SELECT SQL statement is issued two or more times within the same transaction, the results returned may not always be the same. (Lost updates and dirty reads cannot occur; nonrepeatable reads and phantoms, on the other hand, can and may be seen.) In addition, transactions using the Cursor Stability isolation level will not see changes made to other rows by other transactions until those changes have been committed.

Once again, let us see how this isolation level affects our hotel reservation application. Now when a customer retrieves a list of rooms available for a given range of dates, then views information about each room on the list produced (one room at a time), you will be able to change the room rate over any date range for any room in the hotel with the exception of the room the customer is currently looking at. Likewise, other customers will be able to make or cancel reservations over any date range for any room in the hotel; however, they will not be able to do anything with the room the first customer is currently looking at. When the first customer views information about another room in the list, the same holds true for the new room the customer is looking at; you will now be able to change the room rate for the room the first customer was just looking at and other customers will be able to reserve that particular room—provided the first customer did not reserve the room for themselves.