5 Replies Latest reply on May 16, 2013 5:43 AM by jkronegg

    Transaction Isolation Level

    mark_mclain

       

      "Mark_McLain" wrote:
      I realize Transaction Isolation Level (TIL) issues have been beat to death in this forum; however, I must be missing something. Please enlighten me :)

      Here is our scenario:

      On both JBoss 2.4.4 and JBoss 2.4.5.RC3 we have configured XA+DTC data sources for accessing Microsoft SQL Server 2000 databases via a JSQLDirect XA+DTC compliant JDBC driver. In the JBoss.jcml we have tried various transaction isolation level settings for these datasources. When we start JBoss, the server.log states JBoss has configured our datasources using the transaction isolation levels we specified in the jboss.jcml file.

      Two other sources contradict what is in the JBoss server.log:

      (1) JDBC driver trace files show the transaction isolation level to be set to "read committed" and never to the value we have configured.

      (2) When allocated connections from these datasource pools, custom MBeans (within JBoss VM) show "read committed" when displaying feedback from "connection.getIsolationLevel()" calls.

      We have also tried modifying the AutoCommit property for the datasources, but we see read committed every time.

      Please, would someone help me understand why the transaction isolation levels specified in jboss.jcml are not available when connections are allocated?

      Thanks!



        • 1. Re: Transaction Isolation Level
          mark_mclain

           

          "Mark_McLain" wrote:
          One correction: The JDBC driver is JSQLConnect from Netdirect rather than JSQLDirect as I stated in my original post.

          We have also looked at the JBoss source code and think it is only setting the transaction isolation level if the connection is an instance of XAConnectionImpl. Our connection is not an instance of this class; could that be the problem? If it is the problem, is the JBoss source code incorrect, or have we incorrectly deployed our driver such that it is not an instance of XAConnectionImpl.

          Also, here is a snippet from JBoss.jcml:

          <!-- ==================================================================== -->
          <!-- JDBC -->
          <!-- ==================================================================== -->


          <mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=remel_sql2k">
          <attribute name="PoolName">remel_sql2k</attribute>
          <attribute name="DataSourceClass">com.jnetdirect.jsql.JSQLXADataSource</attribute>
          <attribute name="Properties">serverName=ourhost.apogent.com;database=dss_triggered_source;logfile=jsql.log</attribute>
          <attribute name="JDBCUser">sa</attribute>
          <attribute name="Password">cantgetin</attribute>
          <attribute name="TransactionIsolation">TRANSACTION_SERIALIZABLE</attribute>
          <attribute name="LoggingEnabled">true</attribute>
          <!-- <attribute name="Blocking">true</attribute> -->
          <!-- <attribute name="MaxSize">1</attribute> -->
          </mbean>


          • 2. Re: Transaction Isolation Level
            davidjencks

             

            "davidjencks" wrote:
            This feature may work currently only with the fake xa wrapper. Your setup is I think fine. I'd try changing the jboss code to work with your driver. I'm not sure setting the tx isolation level is standard on xa drivers...you might need some kind of reflection for a general solution.


            • 3. Re: Transaction Isolation Level
              pat_ameet

               

              "pat_ameet" wrote:
              Choosing a right isolation level for your program:

              Choosing a right isolation level for your program depends upon your application's requirement. In a single application itself the requirement generally changes, suppose if you write a program for searching a product catalog from your database then you can choose TRANSACTION_READ_ UNCOMMITED because you need not worry about the isolation problems , some other program can insert records at the same time, you don't have to bother much about that insertion. Obviously this improves performance significantly.

              If you write a critical program like bank or stocks analysis program where you want to control all of the isolation problems, you can choose TRANSACTION_SERIALIZABLE for maximum safety. Here it is the tradeoff between the safety and performance.

              Other two isolation levels need good understanding of your requirement. If your application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the good choice. If your application needs to read a row exclusively till you finish your work, then TRANSACTION_REPEATABLE_READ is the best choice.

              Note: Be aware of your database server's support for these isolation levels. Database servers may not support all of these isolation levels. Oracle server supports only two isolation levels, TRANSACTION_READ_COMMITED and TRANSACTION_SERIALIZABLE isolation level, default isolation level is TRANSACTION_READ_COMMITED.



              • 4. Re: Transaction Isolation Level
                n2

                 

                "n2" wrote:
                you can set the transaction isolation level in the datasource configuration file name-ds.xml in the deploy directory for jboss 3.x.x.
                this is an example for ms sqlserver:

                <datasources>
                <local-tx-datasource>
                <jndi-name>matrix</jndi-name>
                <connection-url>jdbc:microsoft:sqlserver://<ip>:1433;databaseName=<db-name>;SelectMethod=cursor</connection-url>
                <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
                <user-name>xxx</user-name>
                < password>xxx< /password>
                <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
                </local-tx-datasource>
                </datasources>
                


                possible values are: TRANSACTION_SERIALIZABLE, TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMMITTED, TRANSACTION_READ_UNCOMMITTED


                • 5. Re: Transaction Isolation Level
                  jkronegg

                  Note that COMMITTED takes two "T" (okay, I know the post is very old).