2

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
CJM
  • 11,908
  • 20
  • 77
  • 115
  • What happens when you execute it using SQL Server Management Studio? – Mark Rotteveel Feb 15 '13 at 17:17
  • @MarkRotteveel - Unsure; A couple of times it has worked (@sp=2), but often it doesn't ('Could not find prepared statement with handle 1.') – CJM Feb 15 '13 at 17:27
  • @MarkRotteveel - if I remove `set @p1 = 1`, the statement executes OK in SSMS. – CJM Feb 18 '13 at 09:40

3 Answers3

1

Don't use a PreparedStatement for this. Use just a plain Statement.

Statement stmt = mDBConnection.createStatement(sql);
mightyrick
  • 910
  • 4
  • 6
  • Could you explain why? Is they a solution to this problem, or more general advice? – CJM Feb 15 '13 at 21:05
  • It is just bad advice (IMHO) – Simon G. Feb 15 '13 at 23:07
  • 1
    @CJM You don't need to use `PreparedStatement` for everything. Statements are just fine for SQL that will be rarely run and especially for SQL statements that don't have bound variables. There is a reason why regular Statements exist. I'm not saying to use a `Statement` all the time, but in this situation, it is the correct thing to do. – mightyrick Feb 16 '13 at 02:27
  • @CJM If you are not seeing any change after using a statement, then check to see if you are committing. If your JDBC connection is set to `autoCommit( false )` or the connection is set that way by default, then you will need to call `commit()` on the connection object explicitly -- after calling `execute()` on the statement. – mightyrick Feb 19 '13 at 13:02
  • @RickGrashel - yes, we are running inside a transaction, and then commit (or rollback), but it's academic - we don't get a response from SQL, there is no RPC:Completed, so control does not return to java. – CJM Feb 19 '13 at 14:02
  • When you say "there is no RPC:Completed" and "control does not return to Java", do you mean that the Java code "hangs" at the point of `commit()`? If that is the case, then the solution is easy -- something else has the table locked up. Any SQL transactions that are operating against this table need to be completed other transactions committed. If there are other JDBC operations simultaneously operating against this table, those statements (and result sets) must be closed. – mightyrick Feb 19 '13 at 14:09
  • @CJM Some constructive criticism. I think the reason why people are having a difficult time answering your question is because your description of the behavior you are seeing is not clear to those of us who are trying to help you. Be very very explicit about what is happening. Is the code locking up? If so, which line does it lock up on? Etc. – mightyrick Feb 19 '13 at 14:10
  • @RickGrashel With respect, the third para states that, after `RPC:Completed` is recorded, control does not return to the java code. My first update, clarifies that, in actual fact there should be 2 x `RPC:Completed` - one for the `sp_prepare` and a second for `sp_execute` - `RPC:Starting` is recorded for `sp_execute`, but not `RPC:Completed` - therefore, the app is left hanging at `stmt.execute()` while it waits for SQL to respond. – CJM Feb 19 '13 at 14:47
  • @CJM My apologies, it just wasn't clear to me. I see what is happening now. To find the lock, run the code. When the code hangs on stmt.execute(), go into your SQL 2008 client and execute the following: `select * from sys.dm_tran_locks`. This will list all of the locked resources on your DB. It should give you an idea as to what exactly is preventing the table from being altered and committed. – mightyrick Feb 19 '13 at 15:06
1

The "ALTER TABLE" statement is DDL (Data Definition Language). DDL must wait for all DML (Data Manipulation Language) statements to complete. If you have an unclosed ResultSet, Statement, or PreparedStatement that is querying the table or a view upon that table, or a join with that table, or updating with auto-commit turned off - then that is DML that is not complete.

Before altering the table like this, ensure that every possible result set open on it has been explicitly closed, and similarly any statements. That will ensure that all DML is complete and DDL can be performed.

In general it is better to use PreparedStatements over Statements. A PreparedStatement is compiled once. A Statement every time it is executed. This means there is no difference for unparameterised statements like yours, and a potential benefit for any parameterised once.

Assuming a trusted JDBC implementation, there is no time a Statement might work when a PreparedStatement does not.

You may also find this question helpful.

Community
  • 1
  • 1
Simon G.
  • 6,587
  • 25
  • 30
  • Using a PreparedStatement for DDL gives no advantage whatsover over a regular Statement. That statement is not going to be executed more than once. And for statements that are only executed once, a PreparedStatement is actually *slower* than a regular Statement. –  Feb 15 '13 at 23:27
  • How do you know it is not going to get executed multiple times? Turning off the triggers could be part of a regular update process. If it is only executed once then any additional slowness is only fractions of a millisecond. Using PreparedStatements is good practice as it ensures correct data type mapping and re-usability. If you are a beginner, PreparedStatements are a better habit to get into. – Simon G. Feb 15 '13 at 23:40
  • 1
    @SimonG. Telling someone to always use a PreparedStatement is bad advice. Also, the vendor's implementation of the JDBC driver is not the only determining factor of how the query cache will behave with regards to a particular SQL statement. Many DBs intelligently cache (or not) SQL statements based on several specific factors. OP's query, just by it's nature, is obviously not executed over and over again. Disabling triggers is not ordinary repetitive behavior. There's no reason to use a PreparedStatement for infrequently used DDL. – mightyrick Feb 16 '13 at 02:32
  • Sorry, but of course they didn't. I believe your problem is as I said in my actual answer - an unclosed result set or statement. – Simon G. Feb 18 '13 at 10:16
1

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.