[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
Ash
[/size]
Hello,
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.
Cheers
Gunnasekkhaar
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.
Mark
Hi Mark,
Why do you suggest to use the custom SQL instead of the dynamic SQL service?
Thanks,
Mike
Use dynamic when you need to programatically generate some or all of the SQL statement. This did not seem to require that.
Mark
Is there a difference between in the performance when using custom over dynamic sql?
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.
Hope this helps,
Percio