when running the following statement with CDatabase.ExecuteSQL:

"update TABLE set COLUMN1=STRING1,COLUMN2=STRING2 where
COLUMN3=STRING3"

A little background...
This statement is ran by clients when connected to the database.  The
database is running on a machine where there is manager/monitor app
that connects to the database as well.  It appears that after 5 or so
clients have connected, DEADLOCKS begin to occur...and occasionally
timeouts.  The deadlock victim appears to always be the manger/monitor
process.  It also appears that there is 30 sql/pipe connections when it
begins to deadlock, which is after approx 5 minutes.

This is the only table that seems to cause the deadlocks to occur.  The
clients access other tables in the database with no problems.  This is
only statement where the clients access the specific "TABLE" that the
deadlock is occurring on.  Is there any better way to write the SQL
statement, that would not cause the deadlocks? I am stumped...

Thanks,
bigcoops

It's probably due to the fact you don't have a valid index on the column in
the WHERE clause.

Show quoteHide quote

I have a try/catch around the ExecuteSQL method as described in the
MSDN docs:

TRY
{
   m_dbCust.ExecuteSQL( strCmd );
}

CATCH(CDBException, e)
{
   // The error code is in e->m_nRetCode
}

...and no exception is thrown.   MSDN also reports a bug where the
CDatabase::ExecuteSQL() Function Does Not Throw Exception with Multiple
Result Sets:

http://support.microsoft.com/default.aspx?scid=kb;en-us;246165

However, this doesn't apply to my SQL command.  Even when I do
deliberatley send an erronous index no exception is thrown.

Andrew

Andrew J. Kelly wrote:
Show quoteHide quote

Why would you expect to get an exception if you are being blocked or
blocking someone else? Am I missing something here? I thought your issue was
with blocking?   Can you post the DDL including indexes for the table and
the actual Update statement used?

Show quoteHide quote

Here is the creation of the table that is having the issues:

create table PC
(
    _ID        int        NOT NULL    identity,
    _NAME        varchar(256)    NOT NULL    default '',
    _PARENT        int        NOT NULL    default 0,
    _RESTART    datetime    NOT NULL    default '',
    _ACTIVE        datetime    NOT NULL    default '',
    _USER        varchar(256)    NOT NULL    default '',
    _FLAGS        int        NOT NULL    default 0
)


....and here is an example of a statement causing the problem:

"update PC set _ACTIVE='20060213 08:38:39',_USER='Acooper' where
_NAME='invalid_name'"

from the following statement:

"update PC set
_ACTIVE=PrepareSQLDate(CTime::GetCurrentTime()),_USER=PrepareSQLString(GetActiveUser())
where _NAME=PrepareSQLString(GetComputerName())"

Here are the functions referenced in the statement above:

//*****************
CString PrepareSQLString (CString const & string, int nMaxLen/* = 0*/)
{
    // To avoid repeated memory allocations, initialise the new string to
same length as old one...
    CString newString (' ', string.GetLength());
    // ..then re-initialise it
    newString = "\'";

    // get string length and scan it
    int nLen = string.GetLength();
    int nNewLen = 0;

    for (int n = 0 ; n < nLen ; n++)
    {
        // are we checking length?
        if (nMaxLen && ((nNewLen + 1) > nMaxLen))
            break;

        // is it a single quote ?
        if (string[n] == '\'')
        {
            // ok - replace with two quotes
            newString += "\'\'";
        }
        else
        {
            newString += string[n];
        }
        nNewLen++;
    }
    // finish off with a quote
    newString += '\'';
    return newString;
}

//***********************

CString PrepareSQLDate (CTime ctDate)
{
    CString strResult;
    strResult.Format("\'%4.4d%2.2d%2.2d %2.2d:%2.2d:%2.2d\'",
ctDate.GetYear(), ctDate.GetMonth(), ctDate.GetDay(),
        ctDate.GetHour(), ctDate.GetMinute(), ctDate.GetSecond());
    return strResult;
}

//***********************

CString GetComputerName ()
{
    char szCompName[MAX_COMPUTERNAME_LENGTH + 1];
    memset (szCompName, 0, sizeof(szCompName));
    DWORD dwNameLen = sizeof(szCompName);
    return CString(szCompName);
}
//***********************

Thanks,
bigcoops

What version of SQL Server are you using?  Thanks for all the info but the
one thing I wanted to see most are all the indexes on the table. Can you
please post ALL of the indexes and Constraints for that table?

Thanks

Show quoteHide quote

Thanks for you help so far.
The problems are occurring on MSDE 2000.  I do have an index for the
column specified in the where field.
Here are the indexes for the PC table:   _ID, _NAME,  _PARENT, _RESTART
Also, there are no constraints on the table.

The clients open a connection with the database every 10 seconds and
then update the database with the statement mentioned earlier, then
close the connection.  However, it doesn't appear that the connections
are getting closed...which appears to be due to the blocking.  Here is
the connection string being used to open the database:  "Driver=SQL
Server;Server=SERVERNAME;Database=DBNAME"

Any help is appreciated.

Thanks,
bigcoops

I don't want the name of the indexes I wanted to see the actual definitions.
You say you have no constraints?  Does that mean you don't have a PK
defined?  I am not a C programmer and we don't have all the code but are you
actually closing the connection? I suspect you have several issues.  One is
that since you are building the update statement on the fly you are probably
not getting reuse of the query plans. As such you will have to compile the
plan each time you call it. This in of itself can cause blocking but usually
not for that long of a time. But if you call this often enough you can get
thousands or even tens of thousands of similar plans in the procedure cache.
This can make the process take much longer than it should.  You really
should be calling a stored procedure and passing the values as parameters
instead of an adhoc sql statement each time.  Or at the very least call
sp_executesql with parameters. Both will eliminate that aspect altogether.
The other potential issue is that even though you say you have an index on
_NAME sql server may not be using it. This is due again to the nature of the
way you are calling the update statement.  SQL Server really has no way to
tell what the datatype you are trying to equate to the _NAME column is since
it is passed in as a string along with the rest of the statement. If you
called a sp with a proper parameter it would never mistake the datatype.
With some exceptions the datatype must match in order to use the index
properly.  Have you looked at the query plan to see what this statement is
doing?  I bet it is scanning the table and thus the blocking.  One other
thing to note is to make sure you are not using serializable isolation level
when you make these calls.


Show quoteHide quote

It seems that the problem has been found to be due to a limitation in
MSDE 2000 with the "Workload Governor".  It only allows up to 8
concurrent connections before it begins to add artificial delays to
each transaction.  I ran the following command on the machine
expriencing the problems:

osql.exe -E -S -Q "DBCC CONCURRENCYVIOLATION"

....and recieved the following output:

Concurrency violations since 2006-02-13 15:02:18.310
1     2     3     4     5     6     7     8     9  10-100  >100
1936  1845  2853  2691  2677  2960  2585  2203   693   99999 99999

Running the same command on other machines running the same application
will return NO concurrency violations.  I dont have access to the
machine, but I am guessing that it is experiencing this issue due to
other applications accessing the same sql server...where connections
are never closed properly.

The governor actually uses an algorithm that takes into account several
factors but in the end it does throttle things some. If they have that many
violations they certainly are not using the right edition for their needs.
They may want to look at upgrading to Std edition or if 2005 is an option
they can use the Workstation Edition which is less expensive than Std but
more capable than MSDE or Express. Come to think of it SQLExpress removes
some of the limitations as well but it was not intended for heavy use or
lots of concurrent users.

Show quoteHide quote

As far as I am aware the governor is only included as part of MSDE 2000
and SQL Server 2000 Standard edition.  SQL Express 2005 does not
include the governor, but does limit the cpu to (one) 1GHz and memory
to 1GB.

Before I blame the issue on the server, do you think it is possible
that the some connections could be blocking due to incomplete
trasactions?  Similarly as described in the following article:
(http://support.microsoft.com/default.aspx?scid=KB;EN-US;295108)

Also, is there a way get the number of current connections at a
specific instance without having to connect to the server and run a
command similar to the one below:
"SELECT COUNT(DISTINCT spid) FROM master..sysprocesses WHERE spid > 50"

Thanks again,
Andrew

It is extremely possible the issue was due to open transactions.  Especially
since changing to NOLOCK seemed to cure it. That is a sure sign of blocking.
And especially if you don't have proper indexes.  For instance if user A
issues a Begin Tran then updates a row but waits to issue the commit he can
block User B from a simple select.  If that select only brings back one row
it really doesn't matter if there is no appropriate index to issue a seek.
If it resorts to a scan any non-compatible lock in the table will eventually
block the select from finishing until the tran is committed or rolled back.
The opposite can occur as well.  If User A runs a select and it has to scan
the table SQL Server will try to lock at the table level.  Even though it is
a shared lock it will block any updates to the table until that select is
done.  If the select is in Serializable isolation level it also has to wait
until the transaction is complete. These are easy enough to verify.  Simply
run sp_who2 during this slowness to see if blocking is occurring or not.  As
for the number of current connections it may be best to run perfmon and use
the sql server user connections counter.

Show quoteHide quote

NESTED SQL QUESTION and aggregate function
CLR Triggers and inserted table
SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?
Query help - resultset too large... impossible!