Re: no sql-select after sql-update?

["Fred Kulack" <kulack@xxxxxxxxxx>]

--

Ok....
Here's an example that does exactly what I think you've described.

To be quite honest, I didn't look at your code at all, it was rather
overly complex, and in general, you should try these things
with a simple tables/columns/data when you're focusing
on how the system works.

It will make it much easier on you to figure out how it works
in a simple fashion first, then in you real world environment.

In summary, I think in this case, we want to seperate our concept of reader
and updater, and assign each an appropriate isolation level (that level
may differ from the other).

Again, here's your example summary:
  web-app user1
  1) create connection
  2) setAutoCommit(false)
  3) execute 'select * ...' (subset of records)
  4) select record for update
  5) execute 'update...'
  no commit() executed, record on screen.

  web-app user2 (This doesn't work)
  1) create connection
  2) setAutoCommit(false)
  3) execute 'select * ...' (subset of records)
  a selection which includes the record fails. Other selections work
  normally.

  vajava user3 (This works)
  1) create connection
  2) setAutoCommit(false)
  3) execute 'select * ...' (subset of records)
  4) select same record for update
  This update failes correctly because of lock. It should work like this.


Some comments:
1) In my test, I have a table (KULACK.TAB1) with 2 columns. Col1 indicates
which row we're
on (could be a primary key), col2 is the data that is updated.

2) I assumed that when you said "(subset of records)" you meant a group of
records that might
include the target row that is being updated, and you'd want to read the
row that
was already updated in its newly updated value.

3) As my previous posts mentioned, the behavior you seem to want requires
different
isolation levels for the updater and the reader. BUT, for user3, you've
chosen characteristics
for the behavior that look BOTH like a reader AND like an updater.
i.e. you want to read out of date data, based on my assumption from #1
"(subset of records)",
but you want to update the data (step 4 for user3).
In order to exactly match the output you said you wanted, I've chosen
isolation levels as follows.
I've given user1 == TRANSACTION_REPEATABLE_READ
I've given user2 == TRANSACTION_READ_UNCOMMITTED
I've given user3 == TRANSACTION_READ_UNCOMMITTED

4) If you consider user3 an updater, then it would probably get
TRANSACTION_REPEATABLE_READ
the same as user1. BUT, changing the isolation level of #3 is going to
change where it tries to get locks
that conflict with the update lock of user1. This is an application
decision you need to come to grips with.
If user3 uses TRANSACTION_REPEATABLE_READ, then it will be held out on the
READ of the updated row.
If user3 uses TRANSACTION_READ_UNCOMMITED, then it will only be held out
when it actually tries to lock for update the updated row.


Here's the testcase output:

This test requires the following tables and data
the FIRST time you run it
FIRST TIME SQL:
   CREATE SCHEMA KULACK (older releases, CREATE COLLECTION)
   CREATE TABLE KULACK.TAB1
          (COL1 INT, COL2 INT)
   INSERT INTO KULACK.TAB1 VALUES(1, 5)
   INSERT INTO KULACK.TAB1 VALUES(2, 6)
   INSERT INTO KULACK.TAB1 VALUES(3, 7)
Note that this testcase never does a commit, so you
shouldn't have to change these rows manually unless you
Modify the testcase.

user1:  Create connection
user1:  Reading rows
user1:  Col1 = 1,  Col2 = 5
user1:  Col1 = 2,  Col2 = 6
user1:  Col1 = 3,  Col2 = 7
user1:  Finished reading all rows
user1:  Read row 2 for update
user1:  Update row 2
user1:  done. DO NOT COMMIT
user2:  Create connection
user2:  Reading rows
user2:  Col1 = 1,  Col2 = 5
user2:  Col1 = 2,  Col2 = 0
user2:  Col1 = 3,  Col2 = 7
user2:  Finished reading all rows
user3:  Create connection
user3:  Reading rows
user3:  Col1 = 1,  Col2 = 5
user3:  Col1 = 2,  Col2 = 0
user3:  Col1 = 3,  Col2 = 7
user3:  Finished reading all rows
user3:  Read row for update
user3: Got correct conflict: com.ibm.db2.jdbc.app.DB2DBException: Row or
object TAB1 in KULACK type *FILE in use.
Close conn1
Close conn2
Close conn3
Done
Java program completed

Here's the testcase:
(See attached file: JdbcLockUpdate.java)


Comments?



In every single ethnic, religious or racial group, there are a
very few truly evil people. For each of those people there
are many, many, many good people.
Assuming anything (evilness or capability for evil) about the
particular group is bigotry and idiocy. Don't do it.  -- Me

Fred A. Kulack  -  AS/400e  Java and Java DB2 access, Jdbc, JTA, etc...
IBM in Rochester, MN  (Phone: 507.253.5982   T/L 553-5982)
mailto:kulack@us.ibm.com   Personal: mailto:kulack@magnaspeed.net
AOL Instant Messenger: Home:FKulack  Work:FKulackWrk

--
[ JdbcLockUpdate.java of type application/octet-stream deleted ]
--