NO LOCK Hint for SQL Server Connection in Jboss/TEIID

170 Views Asked by At

How can one pass no lock hint option in Jboss connection settings. Is passing the TRANSACTION_READ_UNCOMMITTED flag correct? Even though I have this flag in connection setting, I still see a LCK_M_SCH_S wait type for Jboss connection to SQL Server when there is a update/insert statement in progress. Below is what I have setup in standalone.xml file for Jboss:

<datasource jndi-name="java:/SourceModel" pool-name="SourceModel" enabled="true">
    <connection-url>jdbc:sqlserver://server:1433;integratedSecurity=true;authenticationScheme=NTLM;domain=domain.net;databaseName=dbname;sendStringParametersAsUnicode=false</connection-url>
    <driver>sqlserver</driver>
    <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
    <pool>
        <min-pool-size>0</min-pool-size>
        <max-pool-size>60</max-pool-size>
        <prefill>true</prefill>
        <use-strict-min>true</use-strict-min>
        <flush-strategy>IdleConnections</flush-strategy>
    </pool>
    <security>
        <security-domain>SourceModelSecurityDomain</security-domain>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <validate-on-match>true</validate-on-match>
        <use-fast-fail>false</use-fast-fail>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.novendor.NullExceptionSorter"/>
    </validation>
    <timeout>
        <blocking-timeout-millis>30000</blocking-timeout-millis>
        <idle-timeout-minutes>30</idle-timeout-minutes>
    </timeout>
    <statement>
        <track-statements>false</track-statements>
        <prepared-statement-cache-size>400</prepared-statement-cache-size>
        <share-prepared-statements>true</share-prepared-statements>
    </statement>
</datasource>
0

There are 0 best solutions below