Blocking SPID ... no apparent reason

Hi,
I hava a JAVA application that updates a SQL2000 (SP3a)database.
The application handles different types of "jobs" which effectively update the DB.

One job in particular appears to block all subsequent jobs. It comprises of a large amount of inserts/updates in a single transaction. This is necessary as it is an "all or nothing" scenario - so we cannot break the transaction into smaller ones. The transaction appears to succeed as it reaches the COMMIT TRAN statement without error.
However the records do not get written to the database.
EM indicates a large number of locks held on the tables accessed by the transaction and these do not get released.

Using the SP sp_blocker_pss80, the blocking SPID has a waittime of 0 and a waittype of 0x0000 - the lastwaittype is WRITELOG and its status is AWAITING COMMAND

I am using MS SQLSERVER JDBC Driver SP2 (considering using jTDS)

I have tried
- increasing Transaction Log size
- Moving Transaction Log to a separate Disk
- Reducing Isolation Mode to Read Uncommitted
- Set AutoCOMMIT to true
- set Close Cursor on COMMIT
- set SelectMethod to Direct - (we use Cursor by default)

None of these have succeeded in fixing the issue.

The job will succeed if it is the first/only job to access the database.
But if another job precedes it - then the blocking occurs.
I have verified that the preceding job only holds shared dataabase locks
before the blocking job is run.

Each job will use its own JDBC connections to access the database for reading
purposes, but all of the writing goes through the blocking SPID.

Any ideas?
Thanks, LiamIf you close the JDBC connection (when the transaction is complete), does that fix the problem?

-PatP|||I wonder if it is not a deadlock situation. By default deadlocks are not logged. Try running the following:

dbcc traceon (-1, 1205)

then run the big update process. This should start logging deadlock information to the SQL Errorlog.

Except for the data not being written at the end, you have described exactly what locking is designed to do. While someone is writing data to the database, no one else can read that data until they are done. You can potentially try to reduce table locks by checking that the update process is using an appropriate index. Try running the Index Tuning Wizard, and see if it makes any suggestions. This should, of course, be done on a test box first.|||Hi,
Thanks for replies.
1) Connections cannot be terminated after batch jobs complete due to nature of the application

2) DBCC TRACEON resulted in a number of the following entries in the SQL Log

Starting deadlock search 5306
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x42ECD510) Value:0x4b103b00
End deadlock search 5306 ... a deadlock was not found.

3) Index tuner - havent tackled yet|||I thought 1205 gave you information about deadlocks in progress. I was apparently wrong. Try this:

dbcc traceoff (-1, 1205)
go
dbcc traceon (-1, 1204)

Bizarre SQL statement

I have a SQL UNION (I know I know...they're bad, but trust me, I have to us
e
it) in a stored proc.
Effectively, it liiks like this:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
note:
T1 and T2 are not tables, but parameterized query expressions that contain 3
and 4 tables, respectively.
If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 seconds
If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
second
If I run:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
I wait 28 minutes and nothing happens...
I know that unions deprecate performance, but this is REALLY strange.
Also, this only happens for certian parameters. But its the same SQL that's
running...
What the heck is going on'David Jessee wrote:
> I have a SQL UNION (I know I know...they're bad, but trust me, I have to
use
> it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that contain
3
> and 4 tables, respectively.
> If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 second
s
> If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
> second
> If I run:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> I wait 28 minutes and nothing happens...
> I know that unions deprecate performance, but this is REALLY strange.
> Also, this only happens for certian parameters. But its the same SQL that
's
> running...
> What the heck is going on'
try UNION ALL just to see if that helps. Maybe the optimizer in
whatever DB you're using (you didn't say) is trying to do stuff so the
sort is 'faster'|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:

> I have a SQL UNION (I know I know...they're bad, but trust me, I have
> to use it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that
> contain 3 and 4 tables, respectively.
Does "T1" and "T2" share some of these tables? If so, are you sure that you
are not experiencing a deadlock? This is not my strongest field, but as far
as I've understood, the two statements that are merged with a union may
actually run in parallell. If some of the values passed to the
parameterized causes a table-lock on a shared table, you could experience
the behavior you describe, or am I completely wrong here?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||The individual queries are utilizing some of the same tables. However, the
tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an issue,
should it?
"Ole Kristian Bang?s" wrote:

> examnotes <DavidJessee@.discussions.microsoft.com>
> wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:
>
> Does "T1" and "T2" share some of these tables? If so, are you sure that yo
u
> are not experiencing a deadlock? This is not my strongest field, but as fa
r
> as I've understood, the two statements that are merged with a union may
> actually run in parallell. If some of the values passed to the
> parameterized causes a table-lock on a shared table, you could experience
> the behavior you describe, or am I completely wrong here?
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||Well, I'm using a SQL Server database (this snippet of code is inside of a
stored procedure).I'll try the UNION ALL to see if it works. Truth to tell,
I'm hoping it doesn't because each of thequeries can potentially return
duplicate records. Rach of them have DISTINCT clauses in them already. For
this query, placing DISTINCT in each of the queries and them UNIONING them
gives better performance than just relying on the UNOIN to remove duplicates
.
(not my data model...but I have to work with it *ack*)
"wolfing1@.gmail.com" wrote:

> David Jessee wrote:
> try UNION ALL just to see if that helps. Maybe the optimizer in
> whatever DB you're using (you didn't say) is trying to do stuff so the
> sort is 'faster'
>|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com:

> The individual queries are utilizing some of the same tables.
> However, the tables are references "WITH (NOLOCK)" so deadlocking
> shouldn't be an issue, should it?
I don't remember when, but I seem to remember that Kimberly Tripp had a
TechNet session or so regarding this, where she specified that even with
nolock table locks can occur. But, as I've said, I do not remember in what
situation.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Are you familiar with query and join hints? Take a look at the execution
plan generated by each individual SELECT in the UNION and try coercing the
optimizer to match the execution plan. Remember also that a UNION requires
a sort of the result set from each SELECT in order to eliminate duplicates.
You could try this:
SELECT DISNTINCT f1, f2, f3 FROM
( SELECT f1, f2, f3 FROM T1
UNION ALL
SELECT f1, f2, f3 FROM T2 ) T3
By the way, I hope you're aware of the dangers of using WITH(NOLOCK).
Except in rare circumstances, WITH(NOLOCK) will make your queries return
incorrect results at lightning speed.
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com...
> The individual queries are utilizing some of the same tables. However,
> the
> tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an
> issue,
> should it?
> "Ole Kristian Bangs" wrote:
>