webMethods

webMethods

Join this online group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Expand all | Collapse all

Services causing DB Locks

  • 1.  Services causing DB Locks

    Posted Wed May 21, 2008 03:51 PM

    [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]


    #Integration-Server-and-ESB
    #Flow-and-Java-services
    #webMethods


  • 2.  RE: Services causing DB Locks

    Posted Thu May 22, 2008 07:35 AM

    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


    #Flow-and-Java-services
    #Integration-Server-and-ESB
    #webMethods


  • 3.  RE: Services causing DB Locks

    Posted Thu May 22, 2008 12:21 PM

    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


    #webMethods
    #Integration-Server-and-ESB
    #Flow-and-Java-services


  • 4.  RE: Services causing DB Locks

    Posted Thu May 22, 2008 02:04 PM

    Hi Mark,
    Why do you suggest to use the custom SQL instead of the dynamic SQL service?
    Thanks,
    Mike


    #webMethods
    #Flow-and-Java-services
    #Integration-Server-and-ESB


  • 5.  RE: Services causing DB Locks

    Posted Thu May 22, 2008 02:32 PM

    Use dynamic when you need to programatically generate some or all of the SQL statement. This did not seem to require that.

    Mark


    #webMethods
    #Flow-and-Java-services
    #Integration-Server-and-ESB


  • 6.  RE: Services causing DB Locks

    Posted Fri June 06, 2008 07:13 PM

    Is there a difference between in the performance when using custom over dynamic sql?


    #Integration-Server-and-ESB
    #webMethods
    #Flow-and-Java-services


  • 7.  RE: Services causing DB Locks

    Posted Fri June 06, 2008 07:14 PM

    #webMethods
    #Integration-Server-and-ESB
    #Flow-and-Java-services


  • 8.  RE: Services causing DB Locks

    Posted Mon June 09, 2008 10:37 PM

    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


    #Integration-Server-and-ESB
    #webMethods
    #Flow-and-Java-services


  • 9.  RE: Services causing DB Locks

    Posted Tue June 10, 2008 04:05 AM

    Hello Percio,

    useful information. Thanks.

    Cheers
    Gunnasekkhaar
    http://sekkhaar.blogspot.com


    #Integration-Server-and-ESB
    #webMethods
    #Flow-and-Java-services