Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.