Re: delete operation blocked by an open select in different CSession

Rudolf Wiener (r.wiener@xxxxxxxx) writes:
I'm using VC++/VStudio 2003 and MSSQL Server 2000.

I open up two different CSession objects, connecting to the same DB. In
CSession 1 I open a CCommand object for reading through the DB. This
CCommand stays open throughout the application. The select stmt refers to
3 different tables, using join clauses.

Because I need to delete a record from one of these tables I open up a
second CCommand object in a different CSession. This second CCommand
issues a SQL 'delete from tableA where fieldA=22'.

The delete hangs and is never finished (well, I waited only for a couple
of minutes).

The strange thing is: if I reduce the select stmt of CSession 1 in a way
that it refers to 2 tables only, then everything works fine. It does not
matter which of the 3 tables I remove from the select stmt. As long as
there are only 2 tables involved, the delete won't be blocked.

Here's the select stmt of CSession 1:
select * from tableA join tableB on (fieldA = fieldB) left outer join
tableC on (fieldC = fieldB);

and this is the delete oc CSession 2:
delete from tableA where fieldA=22;

You need to make sure that all rows in the SELECT statement makes it
your application. There is some buffering, so if the number of rows
are small (or maybe rather the number of bytes), you will get away with
it without noticing. But if there are unfetched rows, they are locked
and you cannot delete them.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: delete operation blocked by an open select in different CSession
    ... operations in a second CSession object. ... I guess it depends on what look SQL Server takes out. ... commands on the same connection, despite that you are still fetching data ... As long as you don't have MARS active, you cannot have two commands on the ...
    (microsoft.public.data.oledb)
  • delete operation blocked by an open select in different CSession
    ... I open up two different CSession objects, ... In CSession 1 I open a CCommand object for reading through the DB. ... This second CCommand issues a SQL 'delete from tableA where fieldA=22'. ... if I reduce the select stmt of CSession 1 in a way that it refers to 2 tables only, ...
    (microsoft.public.data.oledb)