How does thingworx manage the transaction of Database Things ?

[seanccc]

Hi, 

I thought thingworx manage the transaction automatically for Database things and for each service will start a new or existing transaction.   However,  it doesn't behavior as my assumption.   

For  example: 

TestDbTransA and TestDbTransB are Database things and the service AddOne & AddThree are SQL command services.   I expect the service AddOne doesn't take effect as the exception occurs and the transaction should get rollback,   however ,  AddOne service still insert record into the database.  

So how does thingworx manager the transaction of database things ? 

(function(){
var result = Things["TestDbTransA"].AddOne({
field: "1111" /* STRING */,
name: "1111" /* STRING */,
id: "1111" /* STRING */
});

if(true){
  throw new Error("Error occurs, AddOne is supposed to be rollback");
}

var result2 = Things["TestDbTransB"].AddThree({
field: "3333" /* STRING */,
name: "3333" /* STRING */,
id: "3333" /* STRING */
}); 
})();

 Regards,

Sean

[slangley]

Hi @seanccc.

The first part (AddOne) is completing successfully, and is therefore committed.  Whatever happens after that would have no impact on the successful completion of the previous transaction.  In summary, you can't wrap multiple data base services together in a single service and expect them to all be handled atomically.  Each will be handled in its own thread.

Please let us know if you have further questions.

Regards.

--Sharon

[seanccc]

@slangley ,

Does it means the granularity of the database transaction is not on service level , but always auto commit , at least for Database Thing ?  

Regards,

Sean

[smainente · Accepted solution]

For Database things, the granularity of the transaction is the SQL Service (if AutoCommit is not forced to true on/in the JDBC driver - in this case each SQL statement is treated as a transaction).