locked
Commit inside Stored Procedure called by JDBC RRS feed

  • Question

  • Hi Everyone,

    We have an application in Java developed by a third party that makes JDBC calls to a stored procedure developed by us in Sql Server 2008 R2. Given that as mission-critical, this Java application makes a connection to our database and kept it alive while the service is available. The failure scenario presented is that all table records created by this application, can not be changed while the connection is alive and when the service ends those records are eliminated because have not been confirmed (Commit).

    It is important to clarify that the third party company inform us  the confirmation (Commit) process must be executed within the stored procedure and not from its Java application. We need to know how can we confirm transactions within the stored procedure independently of the app who has made the call.

    Relate code example of how the call is made and the implementation of the stored procedure:

    /* Java Application Makes Stored Procedure Call*/

     String connectionUrl = "jdbc:sqlserver://server:port;" +
                                        "instanceName=INSTANCE;" +
                                        "database=DB;" +
                                        "user=UserName;" +
                                        "password=Pass";
            Connection conn = DriverManager.getConnection(connectionUrl);
            conn.setAutoCommit(false);
            CallableStatement cs = conn.prepareCall("{call myStoredProcedure()}");
            cs.execute();
            cs.close();

            /*... Much Later ...*/

            conn.close();

    /* Stored Procedure Detail */


     CREATE PROCEDURE [dbo].[myStoredProcedure]
     AS

     begin
     begin transaction a
     insert into myTable(myField)
     select 'test'
     commit transaction a
     end


    We appreciate your cooperation.

    Sincerely,

    Daniel

    Thursday, August 21, 2014 3:50 PM

Answers

  • Yes.  I can confirm that .setAutoCommit(false) sets IMPLICIT TRANSACTIONS ON.  And agree that it's not a best practice to do this.

    However since this is third-party code, you may have to work with this. When your stored procedure is called with IMPLICIT TRANSACTIONS, the pattern for transaction handling inside the stored procedure changes.  In particular it's legal (though not good practice) to COMMIT inside your procedure without having a corresponding BEGIN TRANSACTION.  Your first DML statement will start a transaction, and you can commit that transaction inside the procedure. 

    eg

     CREATE PROCEDURE [dbo].[myStoredProcedure]
      AS
    
     begin
      --no BEGIN TRAN so this must be called with IMPLICIT TRANSACTIONS ON
      --begin transaction a
      insert into myTable(myField)
      select 'test'
      commit transaction 
      end
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, August 22, 2014 5:06 PM

All replies

  • I find it difficult to understand your question.

    If the Java application calls your store procedure, and your stored procedures commits the transaction, the data is now stored in the database permanently. ...unless the Javap application also starts a transaction. In this case your inner transaction is just part of this outer transaction and will not be committed until the outer transaction commits.

    I notice this code in the Java code:

       conn.setAutoCommit(false);

    Presumably this results in issueing the command SET IMPLICIT TRANSACTIONS ON, which means that if there is no active transaction, any DML statement, including SELECT will start a transaction. While this setting is compatible with ANSI standards, I don't like it, and it can cause trouble if your code is not written for it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 21, 2014 10:21 PM
  • To clarify what's actually happening in SQL Server I would recommend to trace the entire thing using Profiler including transactions and investigate from there.

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de


    Friday, August 22, 2014 7:21 AM
  • Yes.  I can confirm that .setAutoCommit(false) sets IMPLICIT TRANSACTIONS ON.  And agree that it's not a best practice to do this.

    However since this is third-party code, you may have to work with this. When your stored procedure is called with IMPLICIT TRANSACTIONS, the pattern for transaction handling inside the stored procedure changes.  In particular it's legal (though not good practice) to COMMIT inside your procedure without having a corresponding BEGIN TRANSACTION.  Your first DML statement will start a transaction, and you can commit that transaction inside the procedure. 

    eg

     CREATE PROCEDURE [dbo].[myStoredProcedure]
      AS
    
     begin
      --no BEGIN TRAN so this must be called with IMPLICIT TRANSACTIONS ON
      --begin transaction a
      insert into myTable(myField)
      select 'test'
      commit transaction 
      end
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, August 22, 2014 5:06 PM
  • Thanks for your answer. Works like a charm!
    Monday, December 22, 2014 3:40 PM