Groovy SQL TRANSACTION BOUNDARY

Recently I have been trying to explore the Transaction feature of Groovy SQL.

Groovy SQL really relieves us from all the boiler plate code that we required while setting up JDBC connection and executing CRUD operations.
Querying, Inserting, Updating data, traversing the ResultSets is much smoother with Groovy SQL beacuse of all the various tools it provide and another feature that I cannot stop myself from mentioning out here is the DataSets. They are really handy.

In this tutorial, I am not going to discuss on the above mentioned features, as there are lots of resources in web on those topics.
I got into a scenario, where I need to set the auto commit off for a set of JDBC transaction, so that in case of any failure, I can rollback and in case of everything is fine I can commit, i.e. I want a set of JDBC opeartions to be carried out within a single unit of work, i.e. a transaction.
I found that the JDBC operation carried out within Sql.withTransaction{} closure are well within transaction boundaries, and any exception raised within this bondary will rollback all the operations carried out within closure boundary only.
But what will happen if we want to span that transaction across method boundaries?????

Lets Traverse through the code, Here it is a workaround.
Here I have used HSQLDB for the implementation.
The code is:

class SqlTest {

 static main(args)
 {
  DataSource ds = new JDBCDataSource()
  ds.database = "jdbc:hsqldb:mem:foo"
  ds.user = 'sa'
  ds.password = ''
  Connection con = ds.connection
  Sql sql = new Sql(con)
  sql.execute("create table subha (id integer, name varchar(20) not null)")
  sql.execute("insert into subha values (100,'Subha')")

  /*sql.withTransaction {connection ->
   println "${connection.getClass()}"
   }*/

  try{
   testMethod(sql)
   sql.eachRow("select * from subha")
   {
    println "${it.id}"
    println "${it.name}"
   }

  }
  catch(e)
  {
   sql.eachRow("select * from subha")
   {
    println "${it.id}"
    println "${it.name}"
   }
  }

  finally
  {
   sql.close()
  }
 }

 static def testMethod(Sql sql)
 {
  SubhaModel su = new SubhaModel(subhaID:10000,subhaName:'Puchuuuuu')

  try{

   sql.withTransaction {connection ->
    //connection.setAutoCommit(false)
    //throw new Exception("Okkkkk")
    sql.execute("insert into subha values (103,'Subha133')")
    def people = sql.dataSet("SUBHA")
    people.add(id:104,name:'Subha135')
    people.add(id:105,name:'Subha136')
    //throw new Exception("Okkkkk")
    people.add(id:106,name:'Subha137')
    people.add(id:107,name:'Subha138')

    sql.execute("insert into subha values (?.subhaID,?.subhaName)",su)


    testAgain(sql)

    //throw new Exception("Yoooooo")
    
    people.add(id:111)
    //sql.rollback()

   }


   //sql.commit()
  }
  catch(e)
  {
   //sql.rollback()
  }
 }

 static def testAgain(Sql sql)
 {
  try{

   //sql.withTransaction {
    def people = sql.dataSet("SUBHA")
    people.add(id:999,name:'Subha9999')
    people.add(id:9999,name:'YOOOOO')
    
    //throw new Exception("Yoooooo")
   //}
  }

  catch(e)
  {
   throw new Exception("YooooooAgainnnn")
  }
 }
}

Now, from the above code we can see that within testMethod() we are inserting some data into SUBHA table and there is also a call to testAgain() which again does some insert and all this is happening within the transactional boundary initiated by sql.withTransaction{} closure.

Now, if an exception is raised within this transaction boundary (of testMethod()) then the whole transaction is rolled back , i.e. none of the data gets inserted even the one within testAgain().

Now if we want the called method to be within the same Transactional Boundary then any exception raised within the called method needs to propgated to the calling transaction boundary, if some how the the Excetion is not propagated to the calling transaction boundary, then the calling Transaction will be considered to be successful and they are committed and in the called method just before the point the Exception is raised, all the statements are committed.

When the Exception is propagated with the help of throws clause in the ctach block of testAgain(), then the transaction is rolled back.

Now if we wrap the operations of the called method within transactional boundary (i.e. uncomment the sql.withTransaction { } closure from within testAgain and comment the throw clause within the catch) and an Exception is thrown even not propagated also then the calling transaction and the called transaction both are rolled back. But one catch is: if the transaction in testAgain() is over then all the all the statements starting from the calling transaction within testMethod() to this point are considered to be successful and are commited, and after this when the control moves to the calling transaction within testMethod() and then some exception arises WITHIN within testMethod() then the whole transaction is not rolled back.

This is the way by which we can propagate transaction across methods in groovy.
Keep sharing your Views and Opinions and untill next Time keep Coding and keep Sharing....