Deadlock Issue - MS SQL 2000

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:

  1. 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.

  2. If we use NO_Transaction type for JDBC Polling, what will be the functionality wise difference with implicit Local transaction ?

  3. Transaction wise, is there any diferent behaviour for JDBC adapter with Oracle DB & MS SQL DB.

  4. MS SQL DB supports of using nolock option. is there any way to use this option for JDBC Polling?