I am trying RS isolation level on DB2 Personal Edition on my Windows XP.

I have two clients (Command Editors) open, with auto commit turned off. In the following description, I use T1 and T2 to represent two transactions running in each client. Queries below are listed in temporal order.

T1: select PROPERTY_VALUE from SYSTEM_CFG where PROPERTY_NAME = 'environment' with RS;
(T1 returns immediately with value 'D')

T2: update SYSTEM_CFG set PROPERTY_VALUE = 'P' where PROPERTY_NAME = 'environment';
(T2 is blocked on the lock hold by T1. This is what I expect)

T1: select PROPERTY_VALUE from SYSTEM_CFG where PROPERTY_NAME = 'environment' with RS;
(T1 run the same query again, I expect to see the same result because of RS. However, as the result of this query, T2 is unblocked and update is successful. T1 is now blocked...)

T2: commit;
(T2 commits and release its locks. T1 returns with value 'P')

I am confused. Read Stability is supposed to make sure two same queries in a single transaction return the same values. But this is not the case in T1. Two same queries return 'D' and 'P' respectively

Can anyone please shed some light on this? Thanks!

Repeatable Read guarantees that two selects in the same unit of work will return the same values each time for T1 (unless another SQL run by the same application T1 changes the data). The read-only locks are released at commit time on T1.

Read Stability only guarantees that no data that has already been read by a read-only SQL query (T1) can be changed by another application (T2), untill the first application (T1) has finished the read-only SQL. The read locks (share) are released on T1 when the read-only SQL statement finishes (it does not wait until a commit has happened on T1).

Seems like this problem is unique to Command Editors. When I run the same steps in Command Windows (DB2 CLP), everything works just fine.

Quote from DB2® 9 for Linux®, UNIX®, and Windows®: DBA Guide, Reference, and Exam Prep, Sixth Edition:

Read Stability
The Read Stability (RS) isolation level locks those rows that are part of a result table. If you have a table containing 10,000 rows and the query returns 10 rows, then only 10 rows are locked.

An application using read stability cannot read uncommitted data. Instead of locking a single row, it locks all rows that are part of the result table. No other application can change or modify these rows.

If you decide to use this isolation level, your application will always get the same result if the query is executed more than once in a transaction, though you may get additional phantom rows.

I said that RS would not guarantee the same result if you ran the select statement more than once. If someone inserts rows (and possibly starts updating and/or deleting the newly inserted rows) then you are not guaranteed to get the same results if you run the query multiple times. Therefore, the statement you quoted is an oxymoron, because phantom rows in your result set of the second query means that your query results are not the same as the first time you ran it.

When I said "The read locks (share) are released on T1 when the read-only SQL statement finishes (it does not wait until a commit has happened on T1)." I shoud have been more clear that I was talking about table level share locks, and not rows level locks.

Great, Marcus. We are on the same page then, except that I don't agree with your following statement in bold:

If the S lock is release right after the read SQL statement before commit, there is no way to prevent other transaction from updating the row that is just read.

As I clarified in my previous post, it is not an S lock on the row that is released, it is an intent to share on the table that is released when the SQL completes (to the best of my knowledge).