Web service is not releasing SQL05 locks

Discussion in 'ASP .Net Web Services' started by DG, Nov 20, 2007.

  1. DG

    DG Guest

    I've built a web service that is performing edit tasks on SQL05 tables. When
    the service performs a table update 9 process locks are generated in SQL05
    which do not get released. Each time the update query runs a new set of 9
    process locks are generated. If five web service updates are performed I see
    45 locked processes in SQL05. So far the only way I can kill these is either
    by using SQL Server Management Studio or restarting the SQL service.

    The web service is utilizing a class I built which does the database work.
    I initially tested the class using a C# console application and I did not
    have this problem of unreleased SQL process locks.

    Setting the class to 'null' after use in the web service does not release
    the locks.

    Anyone got an idea of how I can force the database to release the locks?
    DG, Nov 20, 2007
    #1
    1. Advertising

  2. "DG" <> wrote in message
    news:...
    > I've built a web service that is performing edit tasks on SQL05 tables.
    > When
    > the service performs a table update 9 process locks are generated in SQL05
    > which do not get released. Each time the update query runs a new set of 9
    > process locks are generated. If five web service updates are performed I
    > see
    > 45 locked processes in SQL05. So far the only way I can kill these is
    > either
    > by using SQL Server Management Studio or restarting the SQL service.
    >
    > The web service is utilizing a class I built which does the database work.
    > I initially tested the class using a C# console application and I did not
    > have this problem of unreleased SQL process locks.
    >
    > Setting the class to 'null' after use in the web service does not release
    > the locks.
    >
    > Anyone got an idea of how I can force the database to release the locks?


    Can you show us some code, please?

    Also, this isn't VB6. Setting things to Nothing (null) has nothing to do
    with anything much. You'll want to do things like this:

    using (SqlConnection conn = new SqlConnection(connString))
    {
    using (SqlCommand cmd = new SqlCommand(conn, sqlText))
    {
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
    reader.DoSomethingWithTheData();
    }
    }
    }

    Most database-related objects implement IDisposible, and placing them within
    a using statement ensures that IDisposible.Dispose is called.
    --
    --------------------------------------------------------------------------------
    John Saunders | MVP - Windows Server System - Connected System Developer
    John Saunders [MVP], Nov 21, 2007
    #2
    1. Advertising

  3. DG

    DG Guest

    Thanks for the response.

    Below is a section of the code from the web service. I'm testing access to
    this from a web application which includes a button that calls the method
    displayed below. The SOAP header parameters are sent by the test web app.
    The reference to "GISToolBoxes.FeatureToolbox" is a compiled C# class that
    contains the code that does all the work with the SQL05 DB. As I said
    earlier, using this same compiled class with a console test application does
    not cause the problem with the locks.

    I had a brief look at the description for IDisposible but I could use some
    help and how to utilize it.

    Dennis Geasan

    /************/
    using System;
    using System.Web;
    using System.Web.Services;
    using System.Web.Services.Protocols;
    using System.Xml.Serialization;


    [WebService(Namespace = "http://gistech.biz//ArcGISWebServices/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    //[XmlInclude(typeof(Spatialize_WS))]
    public class Spatialize_WS : System.Web.Services.WebService
    {

    //The following references the SOAP Header class that contains the
    connection information that needs to be sent to each method.
    //The 'ConnectHeader' class is listed below.
    public ConnectHeader ConnectParams;

    //Spatialize_WS Class Constructor
    public Spatialize_WS()
    {
    //Uncomment the following line if using designed components
    //InitializeComponent();
    }

    [WebMethod(Description="Update a feature class.")]
    [SoapHeader("ConnectParams", Direction = SoapHeaderDirection.InOut)]
    public bool UpdateFC(int[] RecordArray)
    {
    /*This method will update one or more records (features) of the
    target feature class.
    *
    * The ConnectParams SOAP header must have all values defined.
    */

    GISToolBoxes.FeatureToolbox pFCTools;
    pFCTools = SetConnectParams();
    pFCTools.UpdateFeatures(RecordArray);

    if (pFCTools.ErrorFlag)
    {
    pFCTools = null;
    return true;
    }
    else
    {
    pFCTools = null;
    return false;
    }
    }

    private GISToolBoxes.FeatureToolbox SetConnectParams()
    {
    GISToolBoxes.FeatureToolbox ParamValues = new
    GISToolBoxes.FeatureToolbox("ArcGIS Server");

    ParamValues.SQLDB_ServerName = ConnectParams.SQLDB_ServerName;
    ParamValues.SQLDB_DBName = ConnectParams.SQLDB_DBName;
    ParamValues.SQLDB_CoordTableName = ConnectParams.SQLDB_CoordTableName;
    ParamValues.SQLDB_UserName = ConnectParams.SQLDB_UserName;
    ParamValues.SQLDB_Password = ConnectParams.SQLDB_Password;
    ParamValues.SQLDB_CoordTable_FieldName_X =
    ConnectParams.SQLDB_CoordTable_FieldName_X;
    ParamValues.SQLDB_CoordTable_FieldName_Y =
    ConnectParams.SQLDB_CoordTable_FieldName_Y;
    ParamValues.SDEDB_ServerName = ConnectParams.SDEDB_ServerName;
    ParamValues.SDEDB_DBName = ConnectParams.SDEDB_DBName;
    ParamValues.SDEDB_UserName = ConnectParams.SDEDB_UserName;
    ParamValues.SDEDB_Password = ConnectParams.SDEDB_Password;
    ParamValues.SDEDB_FeatureClassName =
    ConnectParams.SDEDB_FeatureClassName;
    ParamValues.RecordIDFieldName = ConnectParams.RecordIDFieldName;
    ParamValues.SDEDB_VersionName = ConnectParams.SDEDB_VersionName;
    return ParamValues;
    }
    }
    }

    public class ConnectHeader : SoapHeader
    {
    //This class defines the SOAP header to be passed from a client to the
    method being utilized in this web service.

    //Private variables
    //The source for the coordinates will be a table in the GDD database.
    This database will be referred to as SQLDB
    //SQL DB variable names
    private string m_SQLDB_ServerName;
    private string m_SQLDB_DBName;
    private string m_SQLDB_UserName;
    private string m_SQLDB_Password;
    private string m_SQLDB_CoordTableName;
    private string m_SQLDB_CoordTable_FieldName_X;
    private string m_SQLDB_CoordTable_FieldName_Y;
    private string m_SQLDB_CoordTable_FieldName_Z;
    private string m_SQLDB_CoordTable_FieldName_CoordSysID;
    private string m_SQLDB_CoordTable_FieldName_UpdateDate;


    //SDE Database variables
    private string m_SDEDB_ServerName;
    private string m_SDEDB_DBName;
    private string m_SDEDB_UserName;
    private string m_SDEDB_Password;
    private string m_SDEDB_FeatureClassName = "GGDD_FC";
    private string m_SDEDB_VersionName = "DBO.DEFAULT";

    //Other private variables
    //IGeoProcessorResult pGPResult;
    // private IAoInitialize m_pAoInitialize; //The ArcGIS License object.
    private bool m_Error = false;
    private string m_ErrorMessage;
    //private int[] m_UpdateArray;
    private string m_RecordIDFieldName;
    private string m_LicenseName;
    private bool m_LicenseCheckedOutOK;
    private string m_LicenseMessage;
    private string m_ClassMethodMessage;

    /// <summary>(REQUIRED)The name of the ArcSDE server.</summary>
    public string SDEDB_ServerName
    {
    get {return m_SDEDB_ServerName;}
    set { m_SDEDB_ServerName = value; }
    }

    /// <summary>(REQUIRED)The name of the ArcSDE database.</summary>
    public string SDEDB_DBName
    {
    get {return m_SDEDB_DBName;}
    set { m_SDEDB_DBName = value; }
    }

    /// <summary>(REQUIRED)The user name for accessing the ArcSDE database.
    /// This will have to be a user with edit privledges.</summary>
    public string SDEDB_UserName
    {
    get {return m_SDEDB_UserName;}
    set {m_SDEDB_UserName = value;}
    }

    /// <summary>(REQUIRED)The password for accessing the ArcSDE
    database.</summary>
    public string SDEDB_Password
    {
    get {return m_SDEDB_Password;}
    set {m_SDEDB_Password = value;}
    }

    /// <summary>(Optional)The name of the ArcSDE feature class. Default
    name is 'GGDD_FC'.</summary>
    public string SDEDB_FeatureClassName
    {
    get {return m_SDEDB_FeatureClassName;}
    set {m_SDEDB_FeatureClassName = value;}
    }

    /// <summary>(Optional)The name of the ArcSDE feature class. Default
    name is 'GGDD_FC'.</summary>
    public string SDEDB_VersionName
    {
    get {return m_SDEDB_VersionName;}
    set {m_SDEDB_VersionName = value;}
    }

    /// <summary>(REQUIRED)The name of the Microsoft SQL Database
    server.</summary>
    public string SQLDB_ServerName
    {
    get {return m_SQLDB_ServerName;}
    set {m_SQLDB_ServerName = value;}
    }

    /// <summary>(REQUIRED)The name of the Microsoft SQL Server
    Database.</summary>
    public string SQLDB_DBName
    {
    get {return m_SQLDB_DBName;}
    set {m_SQLDB_DBName = value;}
    }

    /// <summary>(REQUIRED)The user name to access the SQL Database.</summary>
    public string SQLDB_UserName
    {
    get {return m_SQLDB_UserName;}
    set {m_SQLDB_UserName = value;}
    }

    /// <summary>(REQUIRED)The password to access the SQL Database.</summary>
    public string SQLDB_Password
    {
    get {return m_SQLDB_Password;}
    set {m_SQLDB_Password = value;}
    }

    /// <summary>(REQUIRED)The name of the SQL Server database table which
    contains the raw coordinate point values.</summary>
    public string SQLDB_CoordTableName
    {
    get {return m_SQLDB_CoordTableName;}
    set {m_SQLDB_CoordTableName = value;}
    }

    /// <summary>(REQUIRED)The name of the field in the SQL Server database
    table that holds the X coordinates.</summary>
    public string SQLDB_CoordTable_FieldName_X
    {
    get {return m_SQLDB_CoordTable_FieldName_X;}
    set {m_SQLDB_CoordTable_FieldName_X = value;}
    }

    /// <summary>(REQUIRED)The name of the field in the SQL Server database
    table that holds the Y coordinates.</summary>
    public string SQLDB_CoordTable_FieldName_Y
    {
    get {return m_SQLDB_CoordTable_FieldName_Y;}
    set {m_SQLDB_CoordTable_FieldName_Y = value;}
    }

    /// <summary>(Optional)The name of the field in the SQL Server database
    table that holds the Z coordinates.</summary>
    public string SQLDB_CoordTable_FieldName_Z
    {
    get {return m_SQLDB_CoordTable_FieldName_Z;}
    set {m_SQLDB_CoordTable_FieldName_Z = value;}
    }

    /// <summary>(Not implemented) The name of the field that holds the ESRI
    ID that defines the coordinates system of the XY values.</summary>
    public string SQLDB_CoordTable_FieldName_CoordSysID
    {
    get {return m_SQLDB_CoordTable_FieldName_CoordSysID;}
    set {m_SQLDB_CoordTable_FieldName_CoordSysID = value;}
    }
    /// <summary>(Not implemented)The name of the field that holds the
    update date for the record.</summary>
    public string SQLDB_CoordTable_FieldName_UpdateDate
    {
    get {return m_SQLDB_CoordTable_FieldName_UpdateDate;}
    set {m_SQLDB_CoordTable_FieldName_UpdateDate = value;}
    }

    //Error Flag
    /// <summary>Indicates an error. If TRUE then an error has
    occurred.</summary>
    /// <remarks>Read-only property.</remarks>
    public Boolean ErrorFlag
    {
    get {return m_Error;}
    set {m_Error = value;}
    }

    //Error Message
    /// <summary>The error message.</summary>
    /// <remarks>Read-only property.</remarks>
    public string ErrorMessage
    {
    get {return m_ErrorMessage;}
    set {m_ErrorMessage = value;}
    }

    /// <summary>(REQUIRED)The name of the field that contains the unique
    record ID.</summary>
    public string RecordIDFieldName
    {
    get {return m_RecordIDFieldName;}
    set {m_RecordIDFieldName = value;}
    }

    /// <summary>The ArcGIS License that was checked out.</summary>
    public string LicenseName
    {
    get {return m_LicenseName;}
    set {m_LicenseName = value;}
    }

    /// <summary>The ArcGIS License message.</summary>
    public string LicenseMessage
    {
    get {return m_LicenseMessage;}
    set {m_LicenseMessage = value;}
    }

    /// <summary>The status of the ArcGIS License. If TRUE then a license
    was available and has been successfully checked out.</summary>
    public bool LicenseCheckedOutOK
    {
    get {return m_LicenseCheckedOutOK;}
    set {m_LicenseCheckedOutOK = value;}
    }

    /// <summary>The message from a class method.</summary>
    public string ClassMethodMessage
    {
    get {return m_ClassMethodMessage;}
    set {m_ClassMethodMessage = value;}
    }
    }


    "John Saunders [MVP]" wrote:

    > "DG" <> wrote in message
    > news:...
    > > I've built a web service that is performing edit tasks on SQL05 tables.
    > > When
    > > the service performs a table update 9 process locks are generated in SQL05
    > > which do not get released. Each time the update query runs a new set of 9
    > > process locks are generated. If five web service updates are performed I
    > > see
    > > 45 locked processes in SQL05. So far the only way I can kill these is
    > > either
    > > by using SQL Server Management Studio or restarting the SQL service.
    > >
    > > The web service is utilizing a class I built which does the database work.
    > > I initially tested the class using a C# console application and I did not
    > > have this problem of unreleased SQL process locks.
    > >
    > > Setting the class to 'null' after use in the web service does not release
    > > the locks.
    > >
    > > Anyone got an idea of how I can force the database to release the locks?

    >
    > Can you show us some code, please?
    >
    > Also, this isn't VB6. Setting things to Nothing (null) has nothing to do
    > with anything much. You'll want to do things like this:
    >
    > using (SqlConnection conn = new SqlConnection(connString))
    > {
    > using (SqlCommand cmd = new SqlCommand(conn, sqlText))
    > {
    > using (SqlDataReader reader = cmd.ExecuteReader())
    > {
    > reader.DoSomethingWithTheData();
    > }
    > }
    > }
    >
    > Most database-related objects implement IDisposible, and placing them within
    > a using statement ensures that IDisposible.Dispose is called.
    > --
    > --------------------------------------------------------------------------------
    > John Saunders | MVP - Windows Server System - Connected System Developer
    >
    >
    >
    DG, Nov 21, 2007
    #3
  4. DG

    DG Guest

    Hi John,

    One additional item. When I restart IIS the SQL Server locks are released.
    Is this because the web service runs under the ASP.Net account? It looks
    like what is happening is that each call to a web service method is
    essentially another process starting but not being released.

    Dennis Geasan

    "John Saunders [MVP]" wrote:

    > "DG" <> wrote in message
    > news:...
    > > I've built a web service that is performing edit tasks on SQL05 tables.
    > > When
    > > the service performs a table update 9 process locks are generated in SQL05
    > > which do not get released. Each time the update query runs a new set of 9
    > > process locks are generated. If five web service updates are performed I
    > > see
    > > 45 locked processes in SQL05. So far the only way I can kill these is
    > > either
    > > by using SQL Server Management Studio or restarting the SQL service.
    > >
    > > The web service is utilizing a class I built which does the database work.
    > > I initially tested the class using a C# console application and I did not
    > > have this problem of unreleased SQL process locks.
    > >
    > > Setting the class to 'null' after use in the web service does not release
    > > the locks.
    > >
    > > Anyone got an idea of how I can force the database to release the locks?

    >
    > Can you show us some code, please?
    >
    > Also, this isn't VB6. Setting things to Nothing (null) has nothing to do
    > with anything much. You'll want to do things like this:
    >
    > using (SqlConnection conn = new SqlConnection(connString))
    > {
    > using (SqlCommand cmd = new SqlCommand(conn, sqlText))
    > {
    > using (SqlDataReader reader = cmd.ExecuteReader())
    > {
    > reader.DoSomethingWithTheData();
    > }
    > }
    > }
    >
    > Most database-related objects implement IDisposible, and placing them within
    > a using statement ensures that IDisposible.Dispose is called.
    > --
    > --------------------------------------------------------------------------------
    > John Saunders | MVP - Windows Server System - Connected System Developer
    >
    >
    >
    DG, Nov 21, 2007
    #4
  5. DG

    DG Guest

    On further checking I see that the aspnet_wp.exe process, running under user
    ASPNET, is not ending after the call to the web service method is completed.

    "John Saunders [MVP]" wrote:

    > "DG" <> wrote in message
    > news:...
    > > I've built a web service that is performing edit tasks on SQL05 tables.
    > > When
    > > the service performs a table update 9 process locks are generated in SQL05
    > > which do not get released. Each time the update query runs a new set of 9
    > > process locks are generated. If five web service updates are performed I
    > > see
    > > 45 locked processes in SQL05. So far the only way I can kill these is
    > > either
    > > by using SQL Server Management Studio or restarting the SQL service.
    > >
    > > The web service is utilizing a class I built which does the database work.
    > > I initially tested the class using a C# console application and I did not
    > > have this problem of unreleased SQL process locks.
    > >
    > > Setting the class to 'null' after use in the web service does not release
    > > the locks.
    > >
    > > Anyone got an idea of how I can force the database to release the locks?

    >
    > Can you show us some code, please?
    >
    > Also, this isn't VB6. Setting things to Nothing (null) has nothing to do
    > with anything much. You'll want to do things like this:
    >
    > using (SqlConnection conn = new SqlConnection(connString))
    > {
    > using (SqlCommand cmd = new SqlCommand(conn, sqlText))
    > {
    > using (SqlDataReader reader = cmd.ExecuteReader())
    > {
    > reader.DoSomethingWithTheData();
    > }
    > }
    > }
    >
    > Most database-related objects implement IDisposible, and placing them within
    > a using statement ensures that IDisposible.Dispose is called.
    > --
    > --------------------------------------------------------------------------------
    > John Saunders | MVP - Windows Server System - Connected System Developer
    >
    >
    >
    DG, Nov 21, 2007
    #5
  6. DG

    DG Guest

    I've now tested the web service from a Web front end and a MS Access VBA
    front end. They both cause the same issue. The ASP.Net worker process
    'aspnet_wp.exe' is holding the locks and won't release. If I end that
    process it restarts automatically but the locks are released from SQL05.

    Dennis Geasan

    "John Saunders [MVP]" wrote:

    > "DG" <> wrote in message
    > news:...
    > > I've built a web service that is performing edit tasks on SQL05 tables.
    > > When
    > > the service performs a table update 9 process locks are generated in SQL05
    > > which do not get released. Each time the update query runs a new set of 9
    > > process locks are generated. If five web service updates are performed I
    > > see
    > > 45 locked processes in SQL05. So far the only way I can kill these is
    > > either
    > > by using SQL Server Management Studio or restarting the SQL service.
    > >
    > > The web service is utilizing a class I built which does the database work.
    > > I initially tested the class using a C# console application and I did not
    > > have this problem of unreleased SQL process locks.
    > >
    > > Setting the class to 'null' after use in the web service does not release
    > > the locks.
    > >
    > > Anyone got an idea of how I can force the database to release the locks?

    >
    > Can you show us some code, please?
    >
    > Also, this isn't VB6. Setting things to Nothing (null) has nothing to do
    > with anything much. You'll want to do things like this:
    >
    > using (SqlConnection conn = new SqlConnection(connString))
    > {
    > using (SqlCommand cmd = new SqlCommand(conn, sqlText))
    > {
    > using (SqlDataReader reader = cmd.ExecuteReader())
    > {
    > reader.DoSomethingWithTheData();
    > }
    > }
    > }
    >
    > Most database-related objects implement IDisposible, and placing them within
    > a using statement ensures that IDisposible.Dispose is called.
    > --
    > --------------------------------------------------------------------------------
    > John Saunders | MVP - Windows Server System - Connected System Developer
    >
    >
    >
    DG, Nov 24, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Veeneman

    Releasing file locks

    David Veeneman, Feb 14, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    347
    David Veeneman
    Feb 14, 2007
  2. David Veeneman

    Releasing file locks

    David Veeneman, Feb 14, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    582
    David Veeneman
    Feb 14, 2007
  3. David Veeneman

    Releasing file locks on email attachments?

    David Veeneman, Feb 15, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    490
  4. Greg Collins [Microsoft MVP]

    Editable SQL05 XML in ASP.NET

    Greg Collins [Microsoft MVP], Apr 25, 2007, in forum: ASP .Net
    Replies:
    5
    Views:
    352
    Bruno Piovan
    Apr 27, 2007
  5. john clarke

    IE does not get reply from found web site nad the computer locks u

    john clarke, Mar 11, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    116
    john clarke
    Mar 11, 2005
Loading...

Share This Page