XA jtOpen and file locking - yet another connection pool configuration question

[Christo]

Hi,

We are running into a strange connection pooling problem. We use the ToolBox XA driver: jtOpen on Windows and the jt400 that came with JDK 1.3.1 on AS400. We need to run it XA-enabled because we access a database and a message queue at the same time. We can see some jobs started and running on the 400. The jobs seem to leave a bunch of locks on parts of files after they were accessed. When we try to click on a different page and do more database work, we may get an error, such as, 'Job XXXXX has locked the file...'. Actual error:

 

java.sql.SQLException: [SQL0913] Row or object USER_00001 in SECURITY type *FILE in use.
      java/lang/Throwable.<init>(Ljava/lang/String;)V+4 (Throwable.java:85)
      java/lang/Exception.<init>(Ljava/lang/String;)V+1 (Exception.java:33)
      java/sql/SQLException.<init>(Ljava/lang/String;Ljava/lang/String;I)V+1 (SQLException.java:34)
      com/ibm/as400/access/JDError.throwSQLException(Lcom/ibm/as400/access/AS400JDBCConnection;III)V+0 (JDError.java:517)
      com/ibm/as400/access/AS400JDBCStatement.commonExecute(Lcom/ibm/as400/access/JDSQLStatement;Lcom/ibm/as400/access/JDServerRow;)V+0 (AS400JDBCStatement.java:650)
      com/ibm/as400/access/AS400JDBCPreparedStatement.executeUpdate()I+0 (AS400JDBCPreparedStatement.java:1162)
      com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.executeUpdate()I+0 (WSJdbcPreparedStatement.java:538)
      com/mccrackenfs/common/dal/DAL.executeUpdate()I+0 (DAL.java:734)
      org/apache/jsp/_home_2D_admin._jspService(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V+0 (_home_2D_admin.java:116)

Here is a snapshot of the job locks:

                            Object                      Member  ASP   
pt  Object      Library     Type       Lock     Status  Locks   Device
    APP_D00001  SECURITY    *FILE-PHY  *SHRRD    HELD    YES          
                                       *SHRRD    HELD                 
    APPLI00001  SECURITY    *FILE-PHY  *SHRRD    HELD    YES          
                                       *SHRRD    HELD                 
    CANGELOV    QSYS        *USRPRF    *SHRRD    HELD                 
                                       *SHRRD    HELD                 
                                       *SHRRD    HELD                 
    DATASOURCE  SECURITY    *FILE-PHY  *SHRRD    HELD    YES          
                                       *SHRRD    HELD                 

The connections and the locks stay the way they are for many days!!!

 

We have configured the pools, and we have created references everywhere: web.xml, under the EJBs in ejb-jar.xml, e.g. everywhere the data sources are used. The EJBs that use the data source have a transaction attribute 'Requred'. We also use the connection outside any EJBs, from the WAR application. When we create the resource references, we specify 'Shareable'. We leave the Transaction Isolation attribute (under WebSphere-specific) blank.

 

The jt400 provider driver has many options and properties to configure, but we have used mostly the defaults:

 

DB2 UDB for iSeries (Toolbox XA)  

Implementation Classname: com.ibm.as400.access.AS400JDBCXADataSource

cursorHold	false

extendedDynamic	false

fullOpen	false

lazyClose	false

cursorSensitivity	asensitive

keepAlive

 

 

 There are a few properties that may be related to our problem, such as

 

We obtain the connections through JNDI, never call commit() and always call close(). When we check the open transactions on the WebSphere console, it says, 'no transactions currently running' or something like this.

 

So it seems that the connections are returned to the pool and the transactions, if any, are over. But why do the locks stay????

 

Any help will be appreciated.

Christo

 

[Frances Stewart]

What OS/400 release is on the '400? Are you up to date PTFs on the '400?  If you stop the application server, do the locks remain? (you may need to use STRQL, and issue LOCK TABLE lib/table IN EXCLUSIVE MODE  to determine if the locks shown are real or just pseudo-locks).

I believe that by leaving the transaction isolation level blank it will default to TRANSACTION_REPEATABLE_READ  which maps to *RS on the '400. This is a strong lock - ensure that is what you need, if not specify something more appropriate for the transaction iosolation level.

I don't know that it is correct to "never call commit()".  Especially when outside of an CMP EJB.

Christo wrote:

Hi,We are running into a strange connection pooling problem. We use the ToolBox XA driver: jtOpen on Windows and the jt400 that came with JDK 1.3.1 on AS400. We need to run it XA-enabled because we access a database and a message queue at the same time. We can see some jobs started and running on the 400. The jobs seem to leave a bunch of locks on parts of files after they were accessed. When we try to click on a different page and do more database work, we may get an error, such as, 'Job XXXXX has locked the file...'. Actual error: java.sql.SQLException: [SQL0913] Row or object USER_00001 in SECURITY type *FILE in use.

      java/lang/Throwable.<init>(Ljava/lang/String;)V+4 (Throwable.java:85)
      java/lang/Exception.<init>(Ljava/lang/String;)V+1 (Exception.java:33)
      java/sql/SQLException.<init>(Ljava/lang/String;Ljava/lang/String;I)V+1 (SQLException.java:34)
      com/ibm/as400/access/JDError.throwSQLException(Lcom/ibm/as400/access/AS400JDBCConnection;III)V+0 (JDError.java:517)
      com/ibm/as400/access/AS400JDBCStatement.commonExecute(Lcom/ibm/as400/access/JDSQLStatement;Lcom/ibm/as400/access/JDServerRow;)V+0 (AS400JDBCStatement.java:650)
      com/ibm/as400/access/AS400JDBCPreparedStatement.executeUpdate()I+0 (AS400JDBCPreparedStatement.java:1162)
      com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.executeUpdate()I+0 (WSJdbcPreparedStatement.java:538)
      com/mccrackenfs/common/dal/DAL.executeUpdate()I+0 (DAL.java:734)

      org/apache/jsp/_home_2D_admin._jspService(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V+0 (_home_2D_admin.java:116)Here is a snapshot of the job locks:

                            Object                      Member  ASP   
pt  Object      Library     Type       Lock     Status  Locks   Device
    APP_D00001  SECURITY    *FILE-PHY  *SHRRD    HELD    YES          
                                       *SHRRD    HELD                 
    APPLI00001  SECURITY    *FILE-PHY  *SHRRD    HELD    YES          
                                       *SHRRD    HELD                 
    CANGELOV    QSYS        *USRPRF    *SHRRD    HELD                 
                                       *SHRRD    HELD                 
                                       *SHRRD    HELD                 
    DATASOURCE  SECURITY    *FILE-PHY  *SHRRD    HELD    YES          
                                       *SHRRD    HELD

The connections and the locks stay the way they are for many days!!! We have configured the pools, and we have created references everywhere: web.xml, under the EJBs in ejb-jar.xml, e.g. everywhere the data sources are used. The EJBs that use the data source have a transaction attribute 'Requred'. We also use the connection outside any EJBs, from the WAR application. When we create the resource references, we specify 'Shareable'. We leave the Transaction Isolation attribute (under WebSphere-specific) blank. The jt400 provider driver has many options and properties to configure, but we have used mostly the defaults: DB2 UDB for iSeries (Toolbox XA)Implementation Classname: com.ibm.as400.access.AS400JDBCXADataSource 

[Christo]

V5R2. All WebSphere PTFs have been installed because we ran into other problems (MQ) and were trying to fix them. If I stop it properly, the jobs go away (apparently it closes its connection pools) so the locks go away as well.

 

I am using jt400 on the AS400 and jtOpen as of January 2003 on Windows. Both WebSphere on Windows and AS400 lock the table this way.

 

I can check the lock. I just did but from the same user ID as the one logged on the Job, so it allowed me to lock it. Judging from the very real error messages, the locks are real. I will try from a different user profile.

 

I don't mind the strong lock. I don't see anything bad about the lock itself. What I don't understand and dont' like is

 

(a) Why does the database not wait when an attempt to modify the table with a read lock on it is requested? Sybase/Microsoft SQL Server waits until the lock is released or times out, and kills one session if it detects a deadlock; Oracle does the same but only on update locks, as there is no such thing as read lock (at least up to version 8). I don't want to code myself, "try this SQL statement, and if SQL error with the word 'lock' in it shows up, try again a few times". I am not as familiar with DB2 on the 400 and its Java drivers.

 

(b) Why does the lock stay after the transaction is committed?

 

I am pretty sure I should not call commit(), as it is the app server job to know when the transaction is done. I can't possibly know if another session EJB is not calling me and is not done with its work. But I will double-check the J2EE spec and IBM's documentation. Outside any EJBs, I am less sure, but again, I will check, although I will be very surprized if I have to call commit().

 

Moreover, WebSphere says there are no transactions running, yet the lock stays. So the whole thing sounds like a bug. I am just wondering if anybody is using the XA driver or has any experience with WebSphere's behavior and table locks.

"Frances Stewart" <fran...@us.ibm.com> wrote in message news:3F759D1F...@us.ibm.com...

[Frances Stewart]

Can't answer all your questions, but for
"a). Why does the database not wait when an attempt to modify the table with a read lock on it is requested?",
the answer is it will if you configure the table being accessed to have it do that.  The default is to wait 60 seconds for a record lock and to not wait at all for an object (file) lock. It is not unusual on iSeries when using a strong lock to change the default wait time for the object lock on the tables  that are being accessed. To do this, for each table:

CHGPF FILE(LIB/FILE) WAITFILE(someSeconds)

For logical files, use the CHGLF command with the same parameters.
 

Christo wrote:

 V5R2. All WebSphere PTFs have been installed because we ran into other problems (MQ) and were trying to fix them. If I stop it properly, the jobs go away (apparently it closes its connection pools) so the locks go away as well. I am using jt400 on the AS400 and jtOpen as of January 2003 on Windows. Both WebSphere on Windows and AS400 lock the table this way. I can check the lock. I just did but from the same user ID as the one logged on the Job, so it allowed me to lock it. Judging from the very real error messages, the locks are real. I will try from a different user profile. I don't mind the strong lock. I don't see anything bad about the lock itself. What I don't understand and dont' like is (a) Why does the database not wait when an attempt to modify the table with a read lock on it is requested? Sybase/Microsoft SQL Server waits until the lock is released or times out, and kills one session if it detects a deadlock; Oracle does the same but only on update locks, as there is no such thing as read lock (at least up to version 8). I don't want to code myself, "try this SQL statement, and if SQL error with the word 'lock' in it shows up, try again a few times". I am not as familiar with DB2 on the 400 and its Java drivers. (b) Why does the lock stay after the transaction is committed? I am pretty sure I should not call commit(), as it is the app server job to know when the transaction is done. I can't possibly know if another session EJB is not calling me and is not done with its work. But I will double-check the J2EE spec and IBM's documentation. Outside any EJBs, I am less sure, but again, I will check, although I will be very surprized if I have to call commit(). Moreover, WebSphere says there are no transactions running, yet the lock stays. So the whole thing sounds like a bug. I am just wondering if anybody is using the XA driver or has any experience with WebSphere's behavior and table locks.

"Frances Stewart" <fran...@us.ibm.com> wrote in message news:3F759D1F...@us.ibm.com...What OS/400 release is on the '400? Are you up to date PTFs on the '400?  If you stop the application server, do the locks remain? (you may need to use STRQL, and issue LOCK TABLE lib/table IN EXCLUSIVE MODE  to determine if the locks shown are real or just pseudo-locks).

[art s]

The snapshot you provided are all *SHRRD locks which (unless the the operation is trying to do something drastic like delete the file) should not cause a file in-use exception.  The iSeries for SQL employ pseudo-locks, and I believe that is generally what you are seeing when you see all those locks applied on the db files.  The pseudo-lock mechanism will, on conflict by a different job, cause the holder of the pseudo-lock to release it.  The basis for this is performance - this way the holder of the pseudo lock does not repeatedly acquire/release the locks.  One experiment you should try is Frances' suggestion (issue LOCK TABLE lib/table IN EXCLUSIVE MODE) on these locks you provided in the snapshot, if they really are pseudo-locks, they should disappear as a result.

But since you are getting a real *file in-use exception, that would imply it is not a pseudo-lock being held. Maybe at the time of the *file in-use exception, the holder of the lock really was locking the file as part if its respective transaction.  One thing to try, as soon as you can the next time this occurs, is issue the lock table in exclusive mode - if your request also timeouts then this would be an indication that the owner of the lock is in the middle of a active transaction.  Of course the state of the transaction can change at any time, so you'd have to do this quickly.

Christo wrote:

 V5R2. All WebSphere PTFs have been installed because we ran into other problems (MQ) and were trying to fix them. If I stop it properly, the jobs go away (apparently it closes its connection pools) so the locks go away as well. I am using jt400 on the AS400 and jtOpen as of January 2003 on Windows. Both WebSphere on Windows and AS400 lock the table this way. I can check the lock. I just did but from the same user ID as the one logged on the Job, so it allowed me to lock it. Judging from the very real error messages, the locks are real. I will try from a different user profile. I don't mind the strong lock. I don't see anything bad about the lock itself. What I don't understand and dont' like is (a) Why does the database not wait when an attempt to modify the table with a read lock on it is requested? Sybase/Microsoft SQL Server waits until the lock is released or times out, and kills one session if it detects a deadlock; Oracle does the same but only on update locks, as there is no such thing as read lock (at least up to version 8). I don't want to code myself, "try this SQL statement, and if SQL error with the word 'lock' in it shows up, try again a few times". I am not as familiar with DB2 on the 400 and its Java drivers. (b) Why does the lock stay after the transaction is committed? I am pretty sure I should not call commit(), as it is the app server job to know when the transaction is done. I can't possibly know if another session EJB is not calling me and is not done with its work. But I will double-check the J2EE spec and IBM's documentation. Outside any EJBs, I am less sure, but again, I will check, although I will be very surprized if I have to call commit(). Moreover, WebSphere says there are no transactions running, yet the lock stays. So the whole thing sounds like a bug. I am just wondering if anybody is using the XA driver or has any experience with WebSphere's behavior and table locks.

"Frances Stewart" <fran...@us.ibm.com> wrote in message news:3F759D1F...@us.ibm.com...What OS/400 release is on the '400? Are you up to date PTFs on the '400?  If you stop the application server, do the locks remain? (you may need to use STRQL, and issue LOCK TABLE lib/table IN EXCLUSIVE MODE  to determine if the locks shown are real or just pseudo-locks).

[Andreas Nagel]

I believe that these locks are real locks. To be more specific: The locks are record locks. You can see them with DSPRCDLCK

 

We encountered the same problem with the XA datasources. Changing them to not-XA datasources solves the problem, but is not always an option.

 

I also think it is a bug.

 

 

Andreas

 

"art s" <sme...@SPAMus.ibm.com> schrieb im Newsbeitrag news:3F7C3505...@SPAMus.ibm.com...

[Christo]

Thank you guys for all the input.

Since I got the FILE IN USE error message, the problem is real and so are the locks. I suspect that there are two types of locks that I see, and some of them are not 'real' and are due to the so called optimizations and open file paths, and some of them must be real because of the errors.

Not using XA is probably not an option. We need it for sure in some parts; we don't for everything, but it will be a real pain to pick and choose, when XA should work always.

 

I will come up with a concurrency test to be able to reproduce for sure the error and know what works and what doesn't, and I will test both Windows and OS/400. I will try the latest driver vs the one we use now. Also, those locking times.

 

If I get any results, I'll post, in case anybody is curious. Thanks again.

 

"Andreas Nagel" <ma...@Andreas-Nagel.de> wrote in message news:blhr1s$2gvg$1...@news.boulder.ibm.com...