DB2中的隔离级别

本文讲解DB2中隔离级别的概念，首先讲述了并发设计中需要考虑的四种现象，然后通过实例讲解了DB2中4种隔离级别下并发表现。

1. 并发设计中的四种现象

1.1 Lost Updates

应用A update了表的某一行，还没有提交的情况下，应用B update了同一行。这样A的update就丢失了。由于DB2会在要update的每一行上加锁，所以能完全避免Lost Updates现象。

1.2 Uncommited Reads

如果应用A update了表的某一行，还没有提交的情况下，应用B就可以读取后修改之后的数据，这种现象叫做Uncommited Reads
测试1

Session 1:

$ db2 "select * from test"ID          NAME----------- --------------------          1 miao          2 qing  2 record(s) selected.$ db2 +c "update test set name='song' where ID = 2"DB20000I  The SQL command completed successfully.

Session 2:

$ db2 "select * from test where ID = 2 with UR"ID          NAME----------- --------------------          2 song  1 record(s) selected. 

  

1.3 Nonrepeatable Reads

在同一个事务中，第二次读取到的数据和第一次读取到的数据不一样（内容不同或者更少），这种现象叫Nonrepeatable Reads。例如，A第一次读取了3行数据，但并未对这3行数据加锁，这时候应用B删除/修改了其中一行，则A第二次读取的时候结果肯定与第一次不同。
测试2
Session 1:

$ db2 +c "select * from test"ID          NAME----------- --------------------          1 miao          2 qing          3 song  3 record(s) selected

Sesion 2:

$ db2 +c "delete from test where id = 2"DB20000I  The SQL command completed successfully.$ db2 commitDB20000I  The SQL command completed successfully.

Session 1:

$ db2 +c "select * from test"ID          NAME----------- --------------------          1 miao          3 song  2 record(s) selected.  <--同样的读取命令，比第一次少了一条数据$ db2 commit  DB20000I  The SQL command completed successfully.

1.4 Phantom Reads.

在一个事务中，若第一次读取的数据全都被加锁，则第二次读取的数据肯定包含第一次的数据，但可能会多出来一部分数据。比如，A第一次读取了3行数据，并加了锁，导致应用B无法修改这3行，但应用B增加了一行，A第二次读取的时候，就会读到4行数据。这种现象叫做 Phantom Reads
测试3
Session 1:

$ db2 +c "select * from test"ID          NAME----------- --------------------          1 miao          2 qing          3 song  3 record(s) selected.

Session2:

$ db2 +c "insert into test values(4,'walle')"DB20000I  The SQL command completed successfully.$ db2 commitDB20000I  The SQL command completed successfully.

Session 1:

$ db2 +c "select * from test"ID          NAME----------- --------------------          1 miao          2 qing          3 song          4 walle  4 record(s) selected. <--同样的读取命令，比第一次多了一条数据$ db2 commit  DB20000I  The SQL command completed successfully.

2. 四种隔离级别

了解了上面4种现象之后，就可以理解DB2的隔离级别(Isolation Level)了，隔离级别的作用就是控制DB2的加锁策略的，按照加锁程度的不同，一共分四种：

• Uncommitted read (UR)
• Cursor stability (CS)
• Read stability (RS)
• Repeatable read (RR)


图1显示了各种隔离级别下，可能出现的现象，例如，在CS隔绝级别下，不可能出现Lost Updates和Uncommited Reads,但有可能出现 Nonrepeatable Reads和Phantom Reads。 而无论采用哪种隔离级别，都不会出现Lost Updates。

2.1 

Uncommitted read (UR)
 最低的隔离级别、提供最高的并发性，仅仅对读操作有效，它的读操作不需要加锁，因此不会有锁等现象。这种隔离级别下，Uncommited Reads， Nonrepeatable Reads和 Phantom Reads都可能出现。测试1中出现了Uncommited Reads现象，原因就是session 2使用的隔离级别为UR

2.2 

 Cursor stability (CS)
 是系统默认的隔离级别，在大多数情况下，可以满足应用的需求。
一个读取操作返回的结果叫做结果集（result set），虽然结果集可能不止一条记录，但是都是通过cursor的方式一条一条地读取的，CS隔离级别下，每读一条记录，都会在这条记录上加一个锁，读到下一条记录时，就会把上一个锁释放掉。CS级别下，不可能出现Lost Updates和Uncommited Reads,但有可能出现 Nonrepeatable Reads和Phantom Reads，测试2和测试3都是在CS隔离级别下做的测试。
再看一个测试，
测试4
Session 1:

$ db2 +c "select * from test"ID          NAME----------- --------------------          1 miao          2 qing          3 song          4 walle  4 record(s) selected.$ db2 +c "update test set name='ccc' where id = 3"DB20000I  The SQL command completed successfully.

Session 2: 由于在第三条记录上有sesion1的X锁，而CS隔离级别在尝试读取这一行的时候，也会尝试加锁，两者不兼容，导致锁超时。

$ db2 +c "select * from test"SQL0911N  The current transaction has been rolled back because of a deadlockor timeout.  Reason code "68".  SQLSTATE=40001

Session 3: 锁超时之前抓取到的锁等消息，只有一个行锁：

$ db2pd -db test -locksDatabase Member 0 -- Database TEST -- Active -- Up 0 days 00:52:58 Locks:Address            TranHdl    Lockname                   Type           Mode Sts Owner      Dur HoldCount0x00000000016B0100 15         01000000030000000100A07DD6 VarLock        ..S  G   15         1   00x00000000016A4980 3          4141414141664164FE8BC714C1 PlanLock       ..S  G   3          1   00x00000000016A4180 15         4141414141664164FE8BC714C1 PlanLock       ..S  G   15         1   00x00000000016A5500 3          02000400060000000000000052 RowLock        ..X  G   3          1   00x00000000016AFF00 15         02000400060000000000000052 RowLock        .NS  W   0          0   00x00000000016A5680 3          02000400000000000000000054 TableLock      .IX  G   3          1   00x00000000016AFF80 15         02000400000000000000000054 TableLock      .IS  G   15         1   0

什么？您的测试4的结果和我的不一样，那是因为cur_commit数据库配置参数默认为on，表示查询将返回提交该查询时数据的当前已落实值。
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0053556.html

另外使用游标稳定性隔离级别时，注册表变量 DB2_EVALUNCOMMITTED、DB2_SKIPDELETED 和 DB2_SKIPINSERTED 受“当前已落实”行为影响

2.3

 Read stability (RS)
 RS隔离级别会将结果集中的数据全部加锁，以确保不会有其他应用修改：
测试5： 
Session 1 以RS隔离级别读取3行数据:

$ db2 +c "select * from test where id <= 3 with RS"ID          NAME----------- --------------------          1 miao          2 qing          3 song  3 record(s) selected.

  
Session 2, 可以看到有3个行锁，而且不能修改这三条记录:

$ db2pd -db test -locksDatabase Member 0 -- Database TEST -- Active -- Up 0 days 00:09:30 -- Date 2017-03-20-Locks:Address            TranHdl    Lockname                   Type           Mode Sts Owner0x00000000016A3980 3          02000400040000000000000052 RowLock        .NS  G   30x00000000016A4500 3          02000400050000000000000052 RowLock        .NS  G   30x00000000016A4380 3          4141414141664164FE8BC714C1 PlanLock       ..S  G   30x00000000016A0200 3          02000400060000000000000052 RowLock        .NS  G   30x00000000016A4C00 3          02000400000000000000000054 TableLock      .IS  G   3$ db2 +c "update test set name = 'ccc' where id = 3"DB21034E  The command was processed as an SQL statement because it was not avalid Command Line Processor command.  During SQL processing it returned:SQL0911N  The current transaction has been rolled back because of a deadlockor timeout.  Reason code "68".  SQLSTATE=40001 

2.4

• Repeatable read (RR)
最严格的隔离级别，并发度最低，能保证四种现象均不存在。它会对涉及到的整个表加锁，以确保下一次的读取操作结果集与之前完全相同
测试6

Session 1:

Session 1:$ db2 +c "select * from test where id <=2 with RR"ID          NAME----------- --------------------          1 miao          2 qing  2 record(s) selected.

Session 2， 虽然要更改的数据和session1里读取到的结果并不矛盾，但由于session 1给整个表加了锁，导致session 2锁超时：

$ db2 +c "update test set name = 'ccc' where id = 3"DB21034E  The command was processed as an SQL statement because it was not avalid Command Line Processor command.  During SQL processing it returned:SQL0911N  The current transaction has been rolled back because of a deadlockor timeout.  Reason code "68".  SQLSTATE=40001