While executing BatchInsert adapter service, first I kept the transaction type as ‘Local’ and then I gave two set of inputs, one with correct data format while the other with wrong format.
Since it is local so the error was thrown and no rows got inserted in the db.
Then I changed the transaction type as ‘No_Transaction’ then also it worked the same way.
Then what is the difference between No and Local transactions if they are acting same??

And also in what scenarios we use startTransaction and commitTransaction builtin services as without them also the transaction is autocommitted.

Please help me with the same.

You can refer the JDBC adapter guide and understand how the transaction manager handles transaction for no, local and xa transactions.

For your information, as an example, use local tx, if you want to insert/update the records in two table with a start, commit and roll back transaction. If insert in table A success and insert to table B fails, then the transaction manager would roll back the inserts from table A. The code should be as below:

-----insert A
-----insert B

Hi there,

By using the LOCAL_TRANSACTION based connection you can maintain the transactional state where it manages the explicit commit/rollback events in case if you want the whole transaction to either be successful or rollback in the event of hard or transient failures.But with using the No_Trans connection you will not have chance to rollback rather it would autocommit (Implicit) in the event of errors…

So to answer to your question yes both the transaction types can handle the CRUD but the catch here is local connection can handle the exceptional try/catch handling scenarios OOTB in any case of dealing with complex DB logic Integration requirements.