Search code examples
javasql-server-2008-r2lockingprepared-statementjtds

Java app hangs after calling PreparedStatement (against SQL Server DB)

I'm trying to get to grips with a Java app that talks to a SQL Server 2008 R2 DB. The app imports data into the DB, and it has a 'test mode'; the DB requests are wrapped up in a transaction, which is rolled back at the end.

With a particular dataset, the tool disables a trigger, and then re-enables it after the import. In test mode, on the first pass, everything works as expected - the dataset in 'imported' without problems. However, if I try to repeat the exercise, the app hangs at the point where it tries to disable the trigger.

Looking at SQL Profiler, I can see an RPC:Completed trace item, which suggests that SQL Server has received and successfully processed the request. At which point, I would expect the Java app to pick up control and continue -except that it doesn't, I'm struggling to think where to look next.

Java code:

String sql = "ALTER TABLE MyTable DISABLE TRIGGER ALL";
PreparedStatement stmt = mDBConnection.prepareStatement (sql);
stmt.execute();

Trace TextData:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'',N'ALTER TABLE MyTable DISABLE TRIGGER ALL',1
select @p1

Q: Any idea what the problem might be? Or any suggestions as to how I investigate further?

UPDATE: Of course, the trace above only only shows the sp_prepare. There is a corresponding sp_execute statement - and the lack of RPC:Completed trace item, indicates that the problem is on SQL Servers side. A modified trace shows an RPC:Starting entry ('exec sp_execute 1'), but no matching RPC:Completed.

I can run sp_prepare & sp_execute in SSMS (providing I remove the set statement), as expected - it executes OK on the first pass after all.

Solution: Using sp_who2 (see below), I could see that there the first connection/spid was blocked the second; on commit, the db connection was closed, but on rollback it wasn't. Since I'm running in test-and-rollback mode, this was the crux of my problem - closing the connection solved the problem.

sp_who2:

CREATE TABLE #sp_who2 
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   RequestID int
) 
GO

INSERT INTO #sp_who2 EXEC sp_who2
GO

SELECT spid, status, blkby, command, ProgramName FROM #sp_who2 WHERE DBName = 'rio7_bch_test'
GO

DROP TABLE #sp_who2
GO
Solution

  • This very much sounds like you have locks that aren't released properly and block your DDL execution.

    When your statement hangs, run the stored procedure sp_who2.

    In the result of that procedure you'll which session is blocking your DDL and then you can take the approriate actions.