Cursor stability isolation level

[Venkat G]

1st Session:

$ db2 values current isolation

1
--
CS

1 record(s) selected.

$ db2 "select * from venkat"

EMPID EMPNAME
----------- ------------------------------
1 a
2 b
3 c
4 d
5 e

5 record(s) selected.


$ db2 "declare c1 cursor for select * from venkat"
DB20000I The SQL command completed successfully.
$ db2 +c open c1
DB20000I The SQL command completed successfully.
$ db2 +c fetch from c1

EMPID EMPNAME
----------- ------------------------------
1 a

1 record(s) selected.

************************************************** ********************************************
2nd Session:

$ db2 values current isolation

1
--
CS

1 record(s) selected.


$ db2 "update venkat set empname='2a' where empid=1"
DB20000I The SQL command completed successfully.


As you can see i was able to update the row where empid=1, in Session 2 ,which is currently being fetched in Session 1.

My Question:
As per the Cursor Stability isolation level, the row where empid=1 which is currently being fetched in Session 1 with CS isolation level should not be updatable by Session 2 (or other application). So why i am able to update the currently fetched row in session 1 from session 2?

[vijay2082]

Hi Venkat,

Fetch/Select in normal clase won't prohibit you/otehr application for updating the table/column value. That's the beauty of isolation levels. If you want to really see CS working then declare a cursor for updating the columns and see the result. I am pasting snaps from session1 and session2 for your easy ref. Enjoying swimming into the world of DB2

session 1
=============

C:\backup>db2 connect

Database Connection Information

Database server = DB2/NT 9.5.5
SQL authorization ID = VIJAY
Local database alias = PAULB001


C:\Users\vijay>set DB2OPTIONS=+c

C:\Users\vijay>db2 list command options

Command Line Processor Option Settings

Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) = +c

Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit OFF


C:\backup>db2 values current isolation

1
--


1 record(s) selected.


C:\backup>
C:\backup>db2 "set current isolation CS"
DB20000I The SQL command completed successfully.

C:\backup>db2 values current isolation

1
--
CS

1 record(s) selected.

C:\backup>db2 "declare c1 cursor for select name from vijay.test5 for update of name"
DB20000I The SQL command completed successfully.

C:\backup>db2 "open c1"
DB20000I The SQL command completed successfully.

C:\backup>db2 "fetch from c1"

NAME
----------
VIJAY

1 record(s) selected.


C:\backup>db2 "fetch from c1" <<< this sould lock the record

NAME
----------
AJAY

1 record(s) selected.

Now look at the session 2. The update command is in lock-wait status as session has got locks on the record ( Cursor stability )


Lets look at the application snapshot to conform our point and preferrablly a lock snapshot on database

Lock snapshot
--------------
Application handle = 74
Application ID = *LOCAL.DB2.110306134846
Sequence number = 00003
Application name = db2bp.exe
CONNECT Authorization ID = VIJAY
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 4
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x02000501050020110000000052
Lock Attributes = 0x00000000
Release Flags = 0x00000001
Lock Count = 1
Hold Count = 0
Lock Object Name = 287309829
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = VIJAY
Table Name = TEST5
Mode = U

Lock Name = 0x01000000010000000100B90056
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Variation Lock
Mode = S

Lock Name = 0x53514C43324731350655EBAA41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S

Lock Name = 0x02000501000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x00000001
Lock Count = 1
Hold Count = 0
Lock Object Name = 261
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = VIJAY
Table Name = TEST5
Mode = IX


Application snapshot
---------------------

C:\backup>db2 "list applications show detail"

CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status

----------------- -------------------- ---------- -------------------------- ----- ---------- ---------------- --------------- ----------------------
VIJAY db2taskd 76 *LOCAL.DB2.110306134851 00001 1 0 7544 Connect Completed
VIJAY db2bp.exe 89 *LOCAL.DB2.110306135815 00001 1 0 8060 Lock-wait
VIJAY db2stmm 75 *LOCAL.DB2.110306134850 00001 1 0 7112 Connect Completed
VIJAY db2bp.exe 74 *LOCAL.DB2.110306134846 00003 1 0 6408 UOW Waiting


>> perform a commit over here so that session2 comes out of lock wait and completes the transaction

C:\backup>db2 commit
DB20000I The SQL command completed successfully.

as soon as you will execute a commit in session1 see the sesion 2 window for completion status message

=========
SEssion 2
=========



C:\Users\vijay>set DB2OPTIONS=+c

C:\Users\vijay>db2 list command options

Command Line Processor Option Settings

Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) = +c

Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit OFF



C:\Users\vijay>db2 "update vijay.test5 set name='VENKAT' where name ='AJAY' << this session goes to lock wait

-------------------------

Cheers, Vijay

[Venkat G]

I clearly understand it now. Thank you, Vijay.