11 Replies Latest reply on Mar 11, 2002 6:21 PM by TJ

    Problem using JBOSS with SQL Server (Need help desperately)

    TJ Newbie

      I tried to run JBoss-2.4.4_Tomcat-4.0.1 version with resin to update the database using BMP Entity Bean but I can only update it only once. After that JBoss seems to lock the database using the same entity bean and prevent me from updating the database the second time. I tried not to use JBoss pool and directly updating the database from the entity bean and it works!. I also tried to use mysql instead of SQL Server and use JBOSS pool and it also works! so my guess it something wrong with the JBOSS database pool. I'm using opta2000 enterprise server (Support transaction)and sprinta2000 from inet software. I also tried to use third party database pool product which is JPool and I still can't update the database. JBOSS just stopped in ejbStore(). This is my configuration in JBoss.jcml :


      com.inet.tds.TdsDriver



      SQLServerDS
      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

      jdbc:inetdae7://localhost:1433/PGS_Core
      1200000
      tj
      arlo97
      10
      false
      false
      false
      true
      120000
      1800000
      false
      false
      1.0
      0


        • 1. Re: Problem using JBOSS with SQL Server (Need help desperate
          TJ Newbie

          This is what I got on JBOSS:
          ============================
          [WARN,TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=ken//9, BranchQual=
          ] timed out. status=STATUS_ACTIVE
          This is what I got on JSP page :
          =================================
          java.lang.reflect.UndeclaredThrowableException: javax.transaction.RollbackException:
          Unable to commit, tx=XidImpl [FormatId=257, GlobalId=ken//9, BranchQual=]
          status=STATUS_ROLLEDBACK
          at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:245)
          at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:220)
          at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:122)
          at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker_Stub.invokeHome(Unknown
          Source)
          at org.jboss.ejb.plugins.jrmp.interfaces.HomeProxy.invokeHome(HomeProxy.java:258)
          at org.jboss.ejb.plugins.jrmp.interfaces.HomeProxy.invoke(HomeProxy.java:182)
          at $Proxy0.create(Unknown Source)
          at _test__jsp._jspService(/test.jsp:55)
          at com.caucho.jsp.JavaPage.service(JavaPage.java:87)
          at com.caucho.jsp.JavaPage.subservice(JavaPage.java:81)
          at com.caucho.jsp.Page.service(Page.java:474)
          at com.caucho.server.http.FilterChainPage.doFilter(FilterChainPage.java:166)
          at com.caucho.server.http.Invocation.service(Invocation.java:277)
          at com.caucho.server.http.CacheInvocation.service(CacheInvocation.java:129)
          at com.caucho.server.http.RunnerRequest.handleRequest(RunnerRequest.java:334)
          at com.caucho.server.http.RunnerRequest.handleConnection(RunnerRequest.java:266)
          at com.caucho.server.TcpConnection.run(TcpConnection.java:140)
          at java.lang.Thread.run(Thread.java:484)



          Please somebody help me!!!!!!

          • 2. Re: Problem using JBOSS with SQL Server (Need help desperate
            David Jencks Master

            Are you holding connections over transaction or method boundaries? If so, stop it, jboss does not support this at the moment.

            • 3. Re: Problem using JBOSS with SQL Server (Need help desperate
              TJ Newbie

              hi;
              Thank you for your reply , I really appreciate it.
              I'm basically open the database connection in setEntityContext() and close it in unsetEntityContext().
              So I share the connection accross different method. So you're saying it is not supported in JBoss currently? Please help me and reply. I tried to create a new connection for every method but still doesn't work. I then use third party database Pool (JPool) and do the same thing but still doesn't work. Finally I tried to use JPool own statement without creating connection. For example: JPool.executeTransaction("mydemo", "delete From Test where TestID = " + TestID + ")"); and it works. So somehow the connection just lost when I create it. Please give me an example of how you actually implement the BMP entity Bean in JBOSS.

              Regards
              ^_^

              • 4. Re: Problem using JBOSS with SQL Server (Need help desperate
                David Jencks Master

                The testsuite is a good place to look for examples. Usually most of it works, and almost all of it worked at some time.

                Proper use of connections in jboss;

                public void doSomething(Object param)
                {
                try
                {
                DataSource ds = (DataSource) new InitialContext("java:/comp/env/mydatasource");
                Connection c = ds.getConnection();
                try
                {
                PreparedStatement ps = c.prepareStatement("select * from mytable where id= ?");
                try
                {
                ps.setObject(1, param);
                ResultSet rs = ps.executeQuery();
                try
                {
                //read result set
                }
                finally
                {
                rs.close();
                }
                }
                finally
                {
                ps.close();
                }
                }
                catch (Exception e)
                {//ignore}
                finally
                {
                c.close();
                }
                catch (Exception e)
                {//ignore}
                }

                Obviously, you don't really want to ignore the exceptions.

                You can get the Datasource in ejbActivate and set it to null in ejbPassivate if you want.

                • 5. Re: Problem using JBOSS with SQL Server (Need help desperate
                  TJ Newbie

                  Thank's for your fast response, you mentioned about the testsuite. Where is the testsuite? can you give me the URL? and do you recommend the JBOSS pool? we're considering whether we should use the third party database pooling like JPool or use JBOSS database pool? our database is MS SQL Server 2000. My preliminary test suggests that JBoss database pooling is not reliable. If we try to insert into database the JBoss will hang and we have to restart it again. but if we use JPool, you don't have to do that. Do you also recommend BMP over CMP ? since JBOSS 2.4.4 only support EJB 1.1 right now. ?

                  Thank you again for the response. I appreciate it. I'm waiting again for your reply. Thank's. Where are you anyway? USA? Are you actually using it in work?

                  • 6. Re: Problem using JBOSS with SQL Server (Need help desperate
                    David Jencks Master

                    Lots of people have been using jboss pooling for quite a while in production successfully. If you use 3rd party pooling it will probably require a lot of adaptation to be able to provide container managed transactions. If you are finding it unreliable you have almost certainly configured something wrong or are trying to do something not supported by the pooling system. In fact you have already indicated you are doing this by holding a connection over a method boundary. Does your app start working when you fix this?

                    You can find the testsuite in cvs.

                    My interests are probably different from yours. I recommend you use jboss 3 beta and cmp2, however this is possibly because I am working on it rather than trying to develop a production system on it.

                    • 7. Re: Problem using JBOSS with SQL Server (Need help desperate
                      TJ Newbie

                      Thank you again for your reply. We're actually comparing the JBoss database pool and JPool database pool. From our test we found out that JPool database pool is more reliable. We found alot of deadlock problem on SQL Server 2000 and found none when we're using JPool. We've done some stress test by doing insert and findPrimaryKey operation together. We also found out that only few data are inserted from 10 threads calling the same method on the same entity bean.

                      Message when 10 threads calling the same BMP entity bean using JBool database pool:
                      [INFO,Default] Error in storeRow = java.sql.SQLException: [KEN]Transaction (Proc
                      ess ID 541) was deadlocked on {lock} resources with another process and has been
                      chosen as the deadlock victim. Rerun the transaction.

                      snipplet of my JBoss.jcml:


                      com.inet.tds.TdsDriver



                      SQLServerDS
                      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

                      jdbc:inetdae7://localhost:1433/test
                      1200000
                      tj
                      arlo97
                      1000
                      true
                      false
                      false
                      true
                      120000
                      1800000
                      false
                      false
                      1.0
                      500


                      We're planning to deploy a number of web application for more than 5 countries so we need to have a reliable database pooling. Any trips that you can offer to bump up the Jboss database pool to become more reliable. So if JBoss is reliable and stable and fast enough, we're planning to use it in almost all of our project in our departement.

                      • 8. Re: Problem using JBOSS with SQL Server (Need help desperate
                        TJ Newbie

                        I forget also: we're actually planning to use BMP instead of CMP unless we found a good reason to use CMP. Unless if JBoss 3 become stable since it supports EJB 2.0 which has good CMP.

                        • 9. Re: Problem using JBOSS with SQL Server (Need help desperate
                          David Jencks Master

                          My personal opinion unsupported by any knowledge of JPool or your application is that you have not designed your transactions appropriately and jboss /your db is telling you so in no uncertain terms. I think (with no knowledge whatsover of JPool) that you are avoiding deadlock using JPool by having autocommit on, so each db operation runs in its own transaction: this will certainly prevent deadlock but is unlikely to result in any kind of database consistency.

                          Database deadlock is not a problem with jboss pooling, it is a problem with your application design-- your transactions are either too long or do not access resources in an appropriate order, or you have bad lock settings in your database.

                          • 10. Re: Problem using JBOSS with SQL Server (Need help desperate
                            TJ Newbie

                            I'm attaching my ejb and test page. TestDemo.java is the class that calls test.jsp and do insert. Using this program I got deadlock problem. If you don't mind please view it a bit and let me know if something wrong with it.
                            Thank you very much before, you've been really helpful.

                            • 11. Re: Problem using JBOSS with SQL Server (Need help desperate
                              TJ Newbie

                              upsss; I'm having problem with sending a larger files. I need to send it later from my home.