1 Reply Latest reply on Jul 21, 2009 5:47 AM by Vicky Kak

    Configure no-tx-datasource: not to acquire shared lock

    Vignesh MPN Newbie

      I am using JBoss 4.2.3.

      Our application queries multiple ODS datasources. As they are read-only operations, all the ODS datasources are configured as no-tx-datasource as we don't need the read operations to be part of any transaction. I understand that though the read operations are not part of a transaction, the SELECT query would still acquire a shared lock on the data records read until the result is returned. So we need a configuration so that the read operations (SELECT queries) on the datasources does not acquire shared lock on the data records read.

      We don't want to add "WITH (NOLOCK)" in all our queries. Instead we would like to configure the transaction isolation level to TRANSACTION_READ_UNCOMMITTED for the datasources. But the JBoss documentation mentions that the property <transaction-isolation/> is not applicable for no-tx-datasource.

      Is there a way to configure a no-tx-datasource, not to acquire a shared lock on execution of a SELECT query? I could be conceptually wrong about my understanding of no-tx-datasource.

      One workaround we thought is to configure them as local-tx-datasource and mention the transaction-isolation property. But most part of our business logic run as part of container-managed transaction, and so we don't want the read operations to be part of the transaction unnecessarily.

      I would highly appreciate any help.

      Thanks
      Vignesh

        • 1. Re: Configure no-tx-datasource: not to acquire shared lock
          Vicky Kak Master

           

          "vigneshmpn" wrote:
          I understand that though the read operations are not part of a transaction, the SELECT query would still acquire a shared lock on the data records read until the result is returned


          I don't believe it to be the default behaviour, are you guessing this or you have tested it?

          "vigneshmpn" wrote:
          But most part of our business logic run as part of container-managed transaction, and so we don't want the read operations to be part of the transaction unnecessarily.


          Using no-tx-datasource inside the Container managed transaction does not make any sense, also setting the transaction isolation level on the no-tx-datasource too would not make sense.