Re: no sql-select after sql-update?

["Fred Kulack" <kulack@xxxxxxxxxx>]

On 10/16/2001 at 07:32:46 AM, P.Goovaerts@Clipper.Be wrote:
So I had to
take a look at my own code again which I did whole morning.  I tracked down
every single step from logon to the update screen and ... found nothing
wrong.  What's the difference between running from webapp (user2) and
running from within VAJAVA (user3)?  Although the class is same in both
cases, the webapp does a lot more than just creating a connection and
loading the data.
--- end of excerpt ---

>From a purely abstract standpoint, the only difference between user2 and
user3 seemed to be that user2 was an updater, while user2 was only a reader
(i.e. user2 didn't do the select for update and readers get different
isolation
levels from updaters)

But, I didn't see in your detailed description where the reader's
connection is
coming from. You've talked about the connection stored in the HttpSession
using TRANSACTION_REPEATABLE_READ, but I didn't see a readers
connection. In order to get the behavior I think you want, you need a
second
connection running TRANSACTION_READ_UNCOMMITED that serves as the
connection
that the reader uses.

If all the users use TRANSACTION_REPEATABLE_READ, what you're seeing is
exactly what I would expect. Repeatable_read means that the first time
I read the record will be the same as the second, third, forth, etc...
i.e. You're doing a select, select for update, update.
Assuming all those target the same row, this consists of 3 reads, and one
update.
1 - The select reads the row.
2 - The select for update reads the row and holds an update lock for
    your intent to update it.
3 - The update with where clause reads the row, then updates it
    causing an update lock (if one doesn't already exist).

If you run everything repeatable_read, there's no way that #1 will ever
be allowed if the row is tagged with an update lock, because the row
might change, and by using repeatable_read, you're saying you want
enough locks to guarantee that the row doesn't change while the
transaction is going on.

I think you really need a reader and an updater connection running at
different isolation levels.

Secondarily:
------------
In my testing, with the toolbox on Win2000, my test acts the same.
Did you try my test in your VAJava environment?

Its probably not this problem, but always ensure that you're not
running an old toolbox driver.
Download the newest version of JTOpen from
http://www.as400.ibm.com/toolbox,
remove the old one from your VAJava environment, and import the new one.
Sorry, don't use VAJava so can't be more specific.

Trivia:
-------
The two JDBC drivers do enter the DB engine at different layers (Sort of a
Y shape diagram where toolbox is on the upper left, native driver is upper
right,
and DB engine is at the bottom. The intersection of the upper branches
(which represent
which APIs/Interfaces are used) is really a bit lower than you'd think, so
sometimes we can actually get data behavior differences (but I wouldn't
think
it would be so in this case).






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