How to control transactions using JDBC Adapter

, ,
1

Hi

Can any one tell me how to use start,commit,rollback transactions the built in services which are defined in the WM ARTPackage? What should be the connection type to use these services and what should be the transaction name to be given !

cheers
kiran.

2

Kiran,

Explicit transations are very useful when you don’t want the default transaction management, for instance to start and end several separate transactions within a service; or to have rollbacks controlled by application logic rather than server errors.

The pattern I tend to use for explicit transaction management is as follows:

	startTransaction
	TRY
		do stuff
		commitTransaction
	CATCH
		getLastError
		rollbackTransaction
		report error

If your transaction only impacts on a single database, LOCAL_TRANSACTION is adequate for your connection. If, however, you want the transaction to span multiple databases, use XA_TRANSACTION, so that they all are committed or rolled back together.

Some notes from our experiences:

  • The transaction name can be anything you like. We never assign a name – we just use the one generated and returned by startTransaction.

  • Be aware that if a calling service has already started a transaction, the IS may throw an error when you try to start another one in the current service – so make a design decision about where the transactions are to be controlled.

  • Because of the way that distributed transaction work, in a multi-DB transaction, one (and only one) of the DBs can actually be configured as LOCAL_TRANSACTION. If more that one is defined as LOCAL_TRANSACTION, you may find that changes fail to commit (but no error message may appear).

  • For some reason, while a DB connection is defined as XA_TRANSACTION, you cannot build “batch” services such as BatchInsertSql. You will need to temporarily downgrade the connection to LOCAL_TRANSACTION while you create the service. Don’t forget to change it back afterwards.

  • Explicit transactions do not work in Trace or Step mode, as the server commits after every step. We have found this can mess up the server threads and connections, forcing a server restart, so we have defined wrapper services called (surprisingly) “startTransaction”, “commitTransaction”, and “rollbackTransaction” that check if they’re running in Trace or Step mode prior to calling the ART transaction services. This is easier and safer than manually disabling transactions when we want to trace something.

Hope this helps,
Michael.

3

Hi Michael,

Thank you very much for your response email and also for your notes from your experiences. Well I have never used these services and I am just trying to learn how to use these.

Well I have tried it and I am not able to do that. I done using this on sample emp table . Please let me know where I am doing wrong !

Start Transaction (Transaction name = "xyz")
Main Sequence
  Try Sequence
     insert into emp table (with valid values )
     insert into emp table (with invalid values)
     Commit Transaction(Transaction name='xyz'-mapped from the output of start)
  Catch Sequence
     getLastError
     rollback transaction (name= 'xyz' the one mapped from start transaction)

Well I placed the start transaction above main sequence because it gets exited even one child is success and start transaction is anyway success and so it is getting of the main loop ! Well coming to the name of transaction I have given it randomly … I tried the same with out giving any name in start transaction.)

Here when i run this service the first record is being inserted properly and i am seeing this in the table.(even though the rollback step in catch block is being executed !)

But according to the code above i should not see any new record in the table .Please let me know where I am going wrong !

Is this wrong way of using these services !

Thanks in advance…

cheers
kiran.

4

Hi

I think i am also facing the same problem with the forum in placing the tabs.

The code above I have written is

Start Transaction (Transaction name = “xyz”)
Main Sequence
…Try Sequence
…insert into emp table (with valid values )
…insert into emp table (with invalid values)
…Commit Transaction(Transaction name=‘xyz’-mapped from the output of start)
…Catch Sequence
…getLastError
…rollback transaction (name= ‘xyz’ the one mapped from start transaction)

cheers
kiran

5

Hmm…

Can you confirm that:

  1. The JDBC adapter is LOCAL_TRANSACTION or XA_TRANSACTION.
  2. “Main” has “exit-on-success”.
  3. “Try” has “exit-on-failure”.
  4. You are running the flow with “Run”, not “Trace” nor “Step” (remember that transactions don’t work when you trace or step).

If that all checks out, try a simpler flow to diagnose the problem – like the following, which should not insert a record:

  • startTransaction
  • insert valid emp record
  • rollbackTransaction

Regards,
Michael

6

Hi Michael,

Thank you very much for your reply.Well while running the code (the one above which I have written ) I did in step wise. When I runned the service directly and it is working fine as per expectation. It is not inserting the record which has valid values also. Well coming to the logic you specified yesterday is also working fine with and the connection type is “LOCAL_TRANSACTION”

Correct me if I am wrong “NO_TRANSACTION” is auto commit !

Thank you
kiran.

7

I had some problems with these services.