We have an existing integration with MSSQL 2000 . We are using WM JDBC Polling (Basic Notification) to pick up data from buffer table with an interval of 60 sec & update status flag (i.e. Mark ID Column field ) as ‘y’ to mark it as processed.
The JDBC conection with MSSQL 2000 DB is defined as “LOCAL_TRANSACTION” type & wM is doing implicit transaction
control on the IS level.
We are getting deadlock issue while Polling is in active mode & to troubleshoot further we need below information:
Is JDBC polling holds lock at the row level or table level ? if locks is at table level, then it prevent any concurrent transactions to insert/ modify data in the same buffer table from the End system.
If we use NO_Transaction type for JDBC Polling, what will be the functionality wise difference with implicit Local transaction ?
Transaction wise, is there any diferent behaviour for JDBC adapter with Oracle DB & MS SQL DB.
MS SQL DB supports of using nolock option. is there any way to use this option for JDBC Polling?