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>