[SIZE=2]We see that webMethods services are locking the database at the Table level. The entire table remains locked while the service runs which causes other services which want to access the table to hang. The locking should be at the record level and not at the table level.
Is there a mechanism/ setting which can use to get over this problem ?
IS Version is 6.5 and DB is SQL Server 2000.
Thanks and regards
I suppose that when you use update/insert/delete queries the table should be locked and it will be. The only place where you can bypass this locking is in ‘select’ query. Instead of using default ‘select’ adapter service you can use ‘dynamic sql’ service and pass query string in the format ‘Select * from table1 where condition1 with (nolock)’ This works for SQL server.
Capture and review the SQL being created from the select/update/delete JDBC services. Work with your DBAs to determine the correct level of locking. If changes are needed (sounds like they are) then use the custom SQL template (not dynamic) instead of the select/update/delete templates.
Locking in a database is good thing, but you do need to design your solution in such a way that you only lock the minimum amount of rows. SQL Server behaves differently with regard to locking than does other database servers so a vendor-specific solution such as the one Gunnasekkhaar suggested may be required.
I strongly encourage you to work closely with your SQL Server DBAs to design the correct SQL statements, don’t just remove locking wholesale without understanding the impact.
Custom SQL adapter services supposedly use PreparedStatement objects behind the scenes. While Dynamic SQL adapter services use Statement objects. PreparedStatements will generally perform better than Statements because they can be compiled once instead of every time a call is made. However, there are cases when Statements will out-perform PreparedStatements. A quick Google search should give you a little more information.