Phantom SQL Server Process Locks My App

Phantom SQL Server Process Locks My App

Post by Alexander J. Os » Sun, 23 Jun 2002 05:21:01



I'm using Borland C++ Builder 4 and its TQuery component to try to work with
a SQL Server 7.0 database via ODBC from Win98.  My goal is to completely
delete and then repopulate four tables (from external flat files) so that no
one can do anything with these four tables while they're loading.  My
approach is the following:

Create four TQuery objects all using the same database.
Use one of the TQuery objects to execute the following:
    "set transaction isolation level serializable"
    "begin transaction"
Use each TQuery object to "delete from <table>" in turn.
Prepare a different "insert" statement in each of the four TQuery objects.
Process the flat files, inserting as you go.
Use one of the TQuery objects to "commit work".

The deletion of the four tables goes fine, but when the first (or something
close to the first) "insert" statement is run, the process hangs.  Looking
at the SQL Server Enterprise Manager, I see that there are more than one
"spid" processes accessing my database (typically, three [I think]).  One
spid is WAITing for a table lock, held by another, to be released.

Shouldn't there be only one SQL Server process running?  How can a single
database connection wait for itself?  Shouldn't all four prepared statements
share the same spid?

Thanks for any assistance you can render!

 
 
 

Phantom SQL Server Process Locks My App

Post by Russell Field » Mon, 24 Jun 2002 00:11:13


Alexander,

I am not familiar with the TQuery component.  However, there is nothing
inherent in ODBC that prevents a program or a component from opening more
than one connection.  See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc...
bcconnection_handles.asp

You can also use DBCC INPUTBUFFER(spid) to investigate the problem spid.
You probably already know what it is doing, but this is a way of checking.
Also DBCC OPENTRAN('database_name') will show you the oldest transaction.

Since this is a rarely run function then I would question the value of using
a series of prepared statements.  You should be able to do the whole thing
as a single batch, perhaps as a stored procedure that you call from TQuery.

If you have the needed rights (sysadmin on SQL Server 7.0) when running this
job then:

Begin Transaction
Truncate Table tablename -- faster than delete if all rows are to be removed
...
BULK INSERT tablename ... WITH (TABLOCK)
...
Commit Transaction

FWIW
Russell Fields



Quote:> I'm using Borland C++ Builder 4 and its TQuery component to try to work
with
> a SQL Server 7.0 database via ODBC from Win98.  My goal is to completely
> delete and then repopulate four tables (from external flat files) so that
no
> one can do anything with these four tables while they're loading.  My
> approach is the following:

> Create four TQuery objects all using the same database.
> Use one of the TQuery objects to execute the following:
>     "set transaction isolation level serializable"
>     "begin transaction"
> Use each TQuery object to "delete from <table>" in turn.
> Prepare a different "insert" statement in each of the four TQuery objects.
> Process the flat files, inserting as you go.
> Use one of the TQuery objects to "commit work".

> The deletion of the four tables goes fine, but when the first (or
something
> close to the first) "insert" statement is run, the process hangs.  Looking
> at the SQL Server Enterprise Manager, I see that there are more than one
> "spid" processes accessing my database (typically, three [I think]).  One
> spid is WAITing for a table lock, held by another, to be released.

> Shouldn't there be only one SQL Server process running?  How can a single
> database connection wait for itself?  Shouldn't all four prepared
statements
> share the same spid?

> Thanks for any assistance you can render!


 
 
 

1. Phantom process in DBMS server.

HI, I am having a very serious problem. A process is active in one of my servers
that does not have any corresponding VMS process. It is most likely from an
interactive query that was killed via ctrl-c (my guess). It is in BIO and is
a dead mask (I have attempted to remove it several times) at this time but after
alomst 24hours it is still there. I can't seem to get rid of it.

The sql it is running (according to IPM) is a copy statement, it was copying rows
into a flat file. I have checked the table it was using and it has no locks on
it and appears to be fine. The flat file was closed a couple of days ago.

How can I get this process out of my server? Am I going to be forced to manually
kill the server and if I do what will happen? My previous experience with something
like this is that it will mark that database inconsistent. Any ideas?

Rob Feehan

2. dependencies across databases

3. ACCESS 97 fron-end/SQL-Server 6.5 back-end app: table-locks when only displaying data

4. pgsql/ oc/src/sgml/runtime.sgml rc/backend/uti ...

5. ghost/phantom processes

6. A Challenging Design and Tool Selection

7. PHANTOM PROCESS ON APPRO

8. NT Error 87 when closing VB App

9. starting phantom processes on D3

10. how to handle phantom processes

11. SB+ processes and phantoms

12. Help - Phantom process

13. NT SQLServer/Named Pipes/Phantom Processes