Does Thingworx support distributed transaction?

[htran-21]

Hi,

I wonder when a transaction service calls other transaction services but they don't use the same DatabaseThing to query/store data, are these database transactions managed by a single transaction scope?

My Bests,

Hung Tran

[PaiChung]

Do you mean Thingworx transactions against different DataTables/Streams?

Behind the scenes that all goes through one single JDBC connection pool to the Database (the Persistence Provider)

as far as 'transactions' go, i do believe that if one part fails and all the calls are in the same service it will reverse the full 'transaction'. Although I recommend testing that to verify.

[CarlesColl]

As far as I know, a transaction starts when you call a service (and it continues to any called inner service) and ends at the end of the services, if an exception it's thrown the whole transaction it's rolled back --> This it's what creates Ghosts when you are creating entities and an Exception it's thrown.

[htran-21]

Hi CarlesColl,

It is interesting now, i did a test. Thingworx does not manage transaction well. Let's see a simple test

Service TestDistributedTransactionWithError () 
{
    var conn1 = Things['TestDataAdapter'];
    var conn2 = Things['TestDataAdapter1'];

    conn1.TestSQLCommand(); // insert a row
    conn2.TestSQLCommand(); // insert a row

    conn1.RaiseError(); // database error level
    conn2.RaiseError(); // database error level

    // Both connections should not be committed, but they did

    throw 'service error level'
}

When the service ran, i got an error, but all rows are committed. It means, Autocommit mode is enabled on every live connections. On the other hand, the TestDistributedTransactionWithError is not a transaction service, only SQLCommand is done in an implicit transaction (Autocommit is On).

Another issue, I retried the test above with Autocommit=true in connection string, but the result is the same. It means that setting is overridden / unmanaged by code (connection.setAutocommit(false/true) is not called in a scope of invoking service).

My Bests,

Hung Tran

[CarlesColl]

Hello Hung,

My explanation was related to Javascript related services (and inner services) not for SQL Like Services which may depend on totally other transactions (as you can be connecting to other databases different than the Model or Data ones ).

[htran-21]

Hi CarlesColl,

You still don't see the situation, it failed with distributed transaction but also non-distributed transaction.

Service TestTransactionWithError ()
{
    var conn1 = Things['TestDataAdapter'];

    conn1.TestSQLCommand(); // insert a row

    conn1.RaiseError(); // database error level

    // the connection should not be committed, but it did

    throw 'service error level'
}

It failed because of AutoCommit mode and unmanaged transaction in code. The TestSQLCommand will be done in its own transaction, RaiseError() at database level or throw Error in Javascript related service (and inner services) won't be able to rollback the committed transaction done by TestSQLCommand.

My Bests,

Hung Tran

[PaiChung]

So I think what your testing has found out is that the SQL Command will be committed no matter what.

Then I think you would need to script the full transaction with the roll back inside of it when a condition isn't met.

you can make your SQL statement as complicated as you want. (or set up procedures for it)

Since it isn't to the Thingworx persistence layer, I would not depend on it for a transaction roll back.

[htran-21]

Hi PaiChung,

Do you mean that Thingworx service is NO TRANSACTION supported? Everyone must write redundant code to keep data consistency. Please read https://www.tutorialspoint.com/jdbc/jdbc-transactions.htm or JDBC transaction example to see how to manage transaction in Java code, and the life would be easier.

My Bests,

Hung Tran

[htran-21]

Hi PaiChung,

it does not a business of JDBC connection pool. A connection pool is only a technique to reduce the cost of opening a new underlying database connection and limit number of concurrent connections to a database server. 

About database transaction, you could read more at JDBC 4.2 section 10.1 Transaction Boundaries and Auto-commit

"

When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source. Although some data sources implement an explicit “begin transaction” statement, there is no JDBC API to do so. Typically, a new transaction is started when the current SQL statement requires one and there is no transaction already in place. Whether or not a given SQL statement requires a transaction is also specified by SQL:2003.

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes a transaction commit after each individual SQL statement as soon as that statement is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:

• For Data Manipulation Language (DML) statements such as Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing.
• For Select statements, the statement is complete when the associated result set is closed.
• For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved.

"

My Bests,

Hung Tran