ADO的IsolationLevel

来源:cww

请先叁照RDO 的IsolationLevel一文中，对Isolation Level的介绍，在这里，要提出的
是OpenLink Informix ODBC Driver的限制，使得我们只能用Client端的Scroll Cursor，
如果用Server端的Cursor，则要以adFrowardOnly的Cursor来开启。而Informix只有在
Server端的Cursor才能设定IsolationLevel，而且要在Transaction之下才能够有作用，
使得ADO Connection物件的IsolationLevel属性的设定不能起作用，而有其特殊的用法。
但在这里得再特别提出说明，建议只能设定成Dirty Read/Read Committed，不要设定成
Cursor Stability，经我的测试，使用Cursor Stability时，不像RDO有预期的效果
Dim WithEvents cn   As ADODB.Connection
Private WithEvents rs As ADODB.Recordset
Private qry As ADODB.Command
Private adoerr As ADODB.Errors

Private Sub Form_Load()
Dim connstr As String
Dim ans As Integer, errstr As String, sql As String

Set cn = New ADODB.Connection

connstr = "UID=cww;PWD=jjh5612;Database=cwwpf@eis;" _
        + "Driver={OpenLink Generic 32 Bit Driver};" _
        + "Host=192.168.0.61;" _
        + ";FetchBufferSize=30" _
        + ";NoLoginBox=Yes" _
        + ";Options=" _
        + ";Protocol=TCP/IP" _
        + ";ReadOnly=No" _
        + ";ServerOptions=" _
        + ";ServerType=Informix 7.2"
cn.ConnectionString = connstr
cn.Open
cn.BeginTrans
cn.Execute "SET ISOLATION  TO DIRTY READ" '这要在Transaction启动时用，且在
                                                'Recordset建立前设定
sql = "Select * from qppfa where case_no = 'E8701761' and seq between 1 and 80"

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.CursorLocation = adUseServer   '设定成Server端的Cursor
rs.Source = sql
rs.CacheSize = 1
rs.Open , cn, adOpenForwardOnly, adLockReadOnly, adCmdText
rs.MoveNext
cn.RollbackTrans
然而在SQL Server 6.5呢，则有不错的效果。特性如下：
1.在Connection物件开启前便设定IsolationLevel，如果是Repeatable Read，则在Recordset
  开启之前便得Begin Transaction。Read Committed/ Dirty Read可不必在Transaction中
  开启Recordset。
2.就算是Client端的Cursor，设定为Repeatable Read的IsolationLevel，也会使Recordset
  内的资料在其上有一个Share Lock，而不是只有 Server端的 Cursor会如此。然而，
  Read Committed/ Dirty Read是针对Server端的Cursor所设定的，也就是说，如果我们
  设定Read Committed而且是Client端的Cursor，而某一笔Data被其他Process修正了且未
  Committed，那麽我们Move到该笔时，并不会因而停下来，因为我们此时读取的是在Client
  端的资料。 
3.如果设定为 adOpenForwardOnly, adLockReadOnly的Recordset，则除非IsolationLevel
  设定为 Repeatable Read，否则会是Dirty Read的行为方式

4.Read UnCommitted或Dirty Read必需是在adOpenForwardOnly, adLockReadOnly时才能
  开启

Dim WithEvents cn   As ADODB.Connection
Private WithEvents rs As ADODB.Recordset
Private qry As ADODB.Command
Private adoerr As ADODB.Errors

Private Sub Form_Load()
Dim connstr As String
Dim ans As Integer, errstr As String, sql As String

Set cn = New ADODB.Connection
connstr = "Data Source=OPEN_VIEW;User=cww;Password=jjh5612;Initial Catalog=cwwtest"
cn.Provider = "SQLOLEDB"

cn.ConnectionString = connstr
cn.IsolationLevel = adXactRepeatableRead  '在此设定IsolationLevel
                                          '如果省略会以Read Committed的方式读取
cn.Open
cn.BeginTrans   'Repeatable Read必需under Transaction
                '如果不是Repeatable Read则这行可省略
sql = "Select * from qppfa where case_no = 'E8701761' and seq between 1 and 80 "
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.CursorLocation = adUseServer
rs.Source = sql
rs.Open , cn, adOpenKeyset, adLockOptimistic, adCmdText
rs.MoveNext
Debug.Print rs!case_no, rs!seq
rs!kind = "k"
rs.Update
cn.RollbackTrans