Re: no sql-select after sql-update?

[P.Goovaerts@xxxxxxxxxx]

No problem Fred.  I really didn't take it for the bad!  I can imagine that
coding looks pretty messed up when there is no background knowledge and
written by not-too-experienced coders.  The best code is the one which is
readable at all times but, things can become very messed up before the
coder notice this.

Ok, back to this problem then.  I hope you don't mind that this reply is
rather big...  I took your code, which in general is same as mine and
included it in one of the servlets which are loaded.  It runs as it should
be!  Lock when needed, free to read into a list but no access for update.
At least I'm pretty sure that my SQL-coding must be correct.  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.

Therefore one need more info to take a look at it.  IMHO it must be
something with the connections itself.  I checked them again and again,
included some 'system.out.println' statements and counters and the
conclusion is they are really 'one connection/transaction' per user.  To
give an idea of the environment / background I give you the applicationflow
at the bottom.  Also some system info at time of running.  Maybe you can
see something which I'm not aware off?  I hope (A) and (B) can give a lead?

Notes:
- The application now allows that more users can display a list of records
when no record is 'selected for update'.
- As from a record is selected for update, NO other user can retrieve a
list (including this record) again.
- connection.commit() is done immediatly after 'update'.
- connection.rollback() is done every time a list is retrieved (to undo any
pre-actions on records).
- connection.close() and connection.rollback() are done when HttpSession is
closed.
- The connection is alway's retrieved from the HttpSession again.  At no
point I restore the connection to the HttpSession...

A) WORK OBJECT LOCK
This is a 'wrkobjlck' when one user has a record for update and another
wants to retrieve a list which includes this record:

          Job        User      Lock      Status         Scope     Thread
2a      QZDASOINIT   QUSER     *SHRRD    HELD           *JOB
1a      QZDASOINIT   QUSER     *SHRRD    HELD           *JOB
1b                             *SHRRD    HELD           *JOB

User1
1a = (u1) after 'select *...' statement (list records)
1b = (u1) after 'select *... for update' statement (1 record)

User2
2a = (u2) while 'select *...' statement (list)  locked wait

B) WORK OBJECT LOCK DETAIL
Record                                            Lock
Number  Job         User        Number  Status    Type
   160  QZDASOINIT  QUSER       777598  HELD      UPDATE
        QZDASOINIT  QUSER       777954  WAIT      READ


C) APPLICATIONFLOW:

1)   This is what the logon-servlet does:
- Create new HttpSession
- execute a registrationclass which
     1) makes a connection using default user/pwd
     2) get and test userinfo
     3) close the connection
- print HttpSessionInfo for logging
- create a new connection with user/password
- setCommit(false)
- setTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)
- put connection in HttpSession
- send webpage Menu.

useraction = load application-jsp

2) 'Application' JSP is loaded from menu which
- Initialize TablesBean
- Get connection from HttpSession
- load default tables into TablesBean using this connection
- Display result with FormAction (servlet appController)

useraction = List records

3) Servlet appController initialized and processed
- Print HttpSessionInfo for logging
- Forward JSP 'ApplicationList'

4) 'ApplicationList' loaded
- Initialize ApplicationBean
- Get connection from HttpSession
- Get list of records using this connection (execution of
ApplicationList.method) which
     - Table with details created
     - Table with key's of details created
- Display result with FormAction (servlet appController)

useraction = select record for update

5) servlet appController processed
- Print HttpSessionInfo for logging
- Get connection from HttpSession
- ApplicationBean info retrieved from HttpSession
- Retrieve record (select * ... for update) ==> RECORD IS LOCKED FROM
HERE!!!
- RecordBean initialized and filled with data retrieved
- Put RecordBean into HttpSession
- Put ApplicationBean into HttpSession
- Forward JSP 'ApplicationDetail'

6) 'ApplicationDetail' loaded
- TablesBean loaded
- ApplicationBean loaded
- RecordBean loaded
- Display recorddetails for update

useraction = update record

7) servlet appController processed
...  Update - Delete - Insert - List
executed depending on useraction





                    "Fred Kulack"
                    <kulack@us.ibm.com        To:     java400-l@midrange.com
                    >                         cc:
                    Sent by:                  Subject:     Re: no sql-select 
after sql-update?
                    java400-l-admin@mi
                    drange.com


                    15/10/2001 19:49
                    Please respond to
                    java400-l







On 10/15/2001 at 10:56:21 AM, java400-l-admin@midrange.com wrote:
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).
--- end of excerpt ---

I just wanted to state this to the list.

I didn't mean to imply that Patrick's code was poor, just that
the example was a normal real world case (i.e. the real world
data/classes/methods are complex) where for our own eduction/debug
it would be better being as simple as possible so we can ignore
the data/flow/methods/classes, and focus only on the records/locks.

Didn't mean to offend if I did. Sorry Patrick




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

_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.