探讨 DB2 的隔离级别，以及如何设置

zhangzongj

Oracle 中的锁可以是手动的，也可以是自动的。Oracle Lock Manager 可以在行级隐式地锁定表数据，此外也可以使用以下 SQL 语句在事务或会话级覆盖缺省的锁：
1. SET TRANSACTION ISOLATION LEVEL
2. LOCK TABLE
3. SELECT FOR UPDATE

Oracle 支持一种称为 Multi-Version Read Consistency 的机制，这是用 undo 段中的 undo 数据实现的。

DB2 实现 ANSI 标准隔离（Isolation）级别，例如未提交读（Uncommitted Read）、游标稳定性（Cursor stability）、读稳定性（Read stability）和可重复读（Repeatable Read）。除非使用未提交读隔离级别，否则用户只能看到已提交的数据。行锁是根据隔离级别隐式地获得的。可锁定的数据库对象有表空间、表和行，但是，只有表和表空间可以显式锁定。可使用 LOCK TABLE 命令来锁定一个表，而不是使用缺省的行锁定。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
注： LOCK TABLE 语句命令格式如下：
>>-LOCK TABLE--+-table-name-+--IN--+-SHARE-----+--MODE---------><
               '-nickname---'      '-EXCLUSIVE-'

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

zhangzongjun

现在，我的问题如下：
1. DB2 如何显示的设置其隔离级别呢？
2. 其默认的隔离级别是否是 CS？
3. 事务默认的隔离级别是什么？

欢迎大家探讨！

DB.Uncle

1.At the statement level:
SELECT (INTO), DELETE, UPDATE ... WITH {RR, RS, CS, UR}
For dynamic SQL within the current session:
SET CURRENT ISOLATION = {RR, RS, CS, UR} or SET ISOLATION {RR, RS, CS, UR}
At precompile or bind time:
ISOLATION {RR, RS, CS, UR} option of the command line processor PREP or BIND commands
From the DB2 Call Level Interface (DB2 CLI):
For DB2 CLI, change the isolation level as part of the DB2 CLI configuration (db2cli.ini). This can be done either by:
UPDATE CLI CFG FOR SECTION sectionname USING TXNISOLATION {1, 2, 4, 8}
where:
1 = SQL_TXN_READ_UNCOMMITTED - Read Uncommitted (Uncommitted Read)
2 = SQL_TXN_READ_COMMITTED (default) - Read Committed (Cursor Stability)
4 = SQL_TXN_REPEATABLE_READ - Repeatable Read (Read Stability)
8 = SQL_TXN_SERIALIZABLE - Serializable (Repeatable Read)
editing db2cli.ini and setting applTxnIsolation = {1, 2, 4, 8}
From CLI or ODBC at run time:
Use the SQLSetConnectAttr function with the SQL_ATTR_TXN_ISOLATION attribute to set the transaction isolation level for the current connection referenced by the ConnectionHandle.
When working with JDBC or SQLJ at run time:
Use the setTransactionIsolation method in the java.sql interface connection, where:
TRANSACTION_SERIALIZABLE = Repeatable read
TRANSACTION_REPEATABLE_READ = Read stability
TRANSACTION_READ_COMMITTED = Cursor stability
TRANSACTION_READ_UNCOMMITTED = Uncommitted read
On a Command Line Processor (CLP) Connection:
CHANGE ISOLATION TO {RR, RS, CS, UR, NC} where NC stands for No Commit
On database servers that support REXX:
CHANGE SQLISL TO {RR|CS|UR|RS|NC} {RR, RS, CS, UR, NC}

2.yes, the default isolation level is CS

3.sorry, i can not understand and answer your question, hope somebody can help on it

zhangzongjun

对于以下的实例，我看不明白，
能否请高手指点一下？
＝＝＝＝＝＝＝＝＝＝
In the following example, a user is in DB2 interactive mode following creation of the SAMPLE database:

   update command options using c off
   catalog db sample as sample2

   set client connect 2

   connect to sample
   connect to sample2

   change isolation to cs
   set connection sample
   declare c1 cursor for select * from org
   open c1                        // 1。这里是成功的吧？
   fetch c1 for 3 rows

   change isolation to rr    // 2。这里是失败的吧？
   fetch c1 for 2 rows  

An SQL0514N error occurs because c1 is not in a prepared state for this isolation level.
=====================
3。实际上，是在 sample2 上open的c1 吗？
=====================

   change isolation to cs
   set connection sample2
   fetch c1 for 2 rows

An SQL0514N error occurs because c1 is not in a prepared state for this database.

=====================
4。 为什么？
=====================

luckysea

最初由 zhangzongjun 发布
[B]现在，我的问题如下：
1. DB2 如何显示的设置其隔离级别呢？
2. 其默认的隔离级别是否是 CS？
3. 事务默认的隔离级别是什么？

欢迎大家探讨！ [/B]

默认的隔离级别是CS
事务默认的隔离级别也是CS

使用


[PHP]

C:\>db2 select * from org with cs

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      10 Head Office        160 Corporate  New York
      15 New England         50 Eastern    Boston
      20 Mid Atlantic        10 Eastern    Washington
      38 South Atlantic      30 Eastern    Atlanta
      42 Great Lakes        100 Midwest    Chicago
      51 Plains             140 Midwest    Dallas
      66 Pacific            270 Western    San Francisco
      84 Mountain           290 Western    Denver

  8 条记录已选择。

.
[/PHP]

suoxunfei

学习！

zhangzongjun

luckysea

select * from org with cs

只是静态SQL的用法，
但大多数情况下，还是使用 游标 的

在使用游标时（正如 第4楼 描述的那样），
本人存在很多疑问，
请大家帮忙说明一些，
谢谢～！

lovelyman_gold

我顶顶
学习

acme1921209

原帖由 DB.Uncle 于 2007-1-11 21:12 发表
1.At the statement level:
SELECT (INTO), DELETE, UPDATE ... WITH {RR, RS, CS, UR}
For dynamic SQL within the current session:
SET CURRENT ISOLATION = {RR, RS, CS, UR} or SET ISOLATION {RR, RS, CS, UR}
At precompile or bind time:
ISOLATION {RR, RS, CS, UR} option of the command line processor PREP or BIND commands
From the DB2 Call Level Interface (DB2 CLI):
For DB2 CLI, change the isolation level as part of the DB2 CLI configuration (db2cli.ini). This can be done either by:
UPDATE CLI CFG FOR SECTION sectionname USING TXNISOLATION {1, 2, 4, 8}
where:
1 = SQL_TXN_READ_UNCOMMITTED - Read Uncommitted (Uncommitted Read)
2 = SQL_TXN_READ_COMMITTED (default) - Read Committed (Cursor Stability)
4 = SQL_TXN_REPEATABLE_READ - Repeatable Read (Read Stability)
8 = SQL_TXN_SERIALIZABLE - Serializable (Repeatable Read)
editing db2cli.ini and setting applTxnIsolation = {1, 2, 4, 8}
From CLI or ODBC at run time:
Use the SQLSetConnectAttr function with the SQL_ATTR_TXN_ISOLATION attribute to set the transaction isolation level for the current connection referenced by the ConnectionHandle.
When working with JDBC or SQLJ at run time:
Use the setTransactionIsolation method in the java.sql interface connection, where:
TRANSACTION_SERIALIZABLE = Repeatable read
TRANSACTION_REPEATABLE_READ = Read stability
TRANSACTION_READ_COMMITTED = Cursor stability
TRANSACTION_READ_UNCOMMITTED = Uncommitted read
On a Command Line Processor (CLP) Connection:
CHANGE ISOLATION TO {RR, RS, CS, UR, NC} where NC stands for No Commit
On database servers that support REXX:
CHANGE SQLISL TO {RR|CS|UR|RS|NC} {RR, RS, CS, UR, NC}

2.yes, the default isolation level is CS

3.sorry, i can not understand and answer your question, hope somebody can help on it

=======================
其实这个已经多是很清楚了哦,你仔细看看这个会有很大收获的(不知你看了没)

下面给你个链接,上面那段其中也有 ,希望对你有帮助
http://www.ibm.com/developerwork ... cle/dm-0509schuetz/

lovelyman_gold

再顶两下