Web service is not releasing SQL05 locks

[DG]

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?

 

[John Saunders [MVP]]

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.

 

[DG]

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;}
}
}

 

[DG]

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

 

[DG]

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.

 

[DG]

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