concurrent users access

manish21 · #1 (**permalink**) 06-02-04, 01:29

While doing load testing on our application using load testing tool open sta, We found that when 50-100 concurrent users access it , 20-30 records get inserted. The tool does not give any reason why the other transactions are rolled back. When I tried db2 list applications show detail, there were some locks waiting. I got a snapshot of the locks , changed the locking on tables used for reading to table level lock, But still there was no significant improvement. For the queries accessing only read only tables , I added the clause for read only. I increased statement heap size ,max appls , locksize and max locks according to the guidelines in the db2 documentation and discussing with some dbas. I changed the websphere connection pool timeout parameters for our datasource. Now there are no locks but out of 100 concurrent users hitting the application at a single point in time, only 40-50 are inserted. When I list applications, some are shown as connect completed and many are shown as uow waiting for a long period of time. I tried changing isolation level to Uncommited read using the command
Change isolation to ur.But no improvement.

Any Sugestions? Pl help as this is very critical

nitingm · #2 (**permalink**) 06-02-04, 03:44

Hi,

The following can be done

1. Ensure that the runstats/reorg is properly done and up to date.
2. Indexes are properly defined on the table.
3. Values of LOCKTIMEOUT, MAXLOCK and LOCKLIST are properly defined and adequate enough.
4. A proper bind of the database is fne after you change the isolation level.

Please also search for other posts on the same topic a lot has been discussed earlier on the same.

Please also make it a habbit to post your operating system, database level and version with everypost.

J Petruk · #3 (**permalink**) 06-02-04, 06:47

Quote:

Originally Posted by manish21

While doing load testing on our application using load testing tool open sta, We found that when 50-100 concurrent users access it , 20-30 records get inserted. The tool does not give any reason why the other transactions are rolled back. When I tried db2 list applications show detail, there were some locks waiting. I got a snapshot of the locks , changed the locking on tables used for reading to table level lock, But still there was no significant improvement. For the queries accessing only read only tables , I added the clause for read only. I increased statement heap size ,max appls , locksize and max locks according to the guidelines in the db2 documentation and discussing with some dbas. I changed the websphere connection pool timeout parameters for our datasource. Now there are no locks but out of 100 concurrent users hitting the application at a single point in time, only 40-50 are inserted. When I list applications, some are shown as connect completed and many are shown as uow waiting for a long period of time. I tried changing isolation level to Uncommited read using the command
Change isolation to ur.But no improvement.

Any Sugestions? Pl help as this is very critical

Are you sure the problem is on the database, and not in the app?

Are there any errors being returned to the app that may give you some clues?

In websphere, changing the isolation level of the connection is tricky, it's supposed to be done in the resource-ref definition for the EJB... Websphere has a way of overriding anything else... it's been a bit of a nightmare for us, do a con.getTransactionIsolation() to verify the change really was effective.