Transaction Handling in ThingWorx

[#1]

Important change in ThingWorx 9.3.2

• Since 9.3.2, Transaction behavior depends on the type of database opration:
  
  1. Model operations which acquire a transaction and hold it until the top-level service finishes - same behavior as pre-9.3.2.

     
     Once a connection is retrieved for performing model operations, it cannot be released until the service terminates.
     
     

  2. Data read operations which have been updated to eagerly release their transaction (Data write operations, such as property writes, are always batched and processed asynchronously from a different thread).
     
     

     Performing Data read operations before model operations allows a connection to be retrieved for querying, then released while other work is being completed.

     
     Sample services with that use Eager Transactions: DataTable, Stream, ValueStream get/find/query/search

The post below is valid for all ThingWorx versions until 9.3.1

TL;DR

• The transaction API is not exposed as ThingWorx scripting API (Service script).
• When servicing a request, that acquire a database connection, a transaction is automatically created by the Platform.
• A basic understanding of those automatic transactions (scope and lifecycle) is important to build a scalable ThingWorx solution.

Scope

• The transactions discussed here are in the context of persistence provider operations
• This post does not apply to Database Things and  SQL Services

Transaction Handling

Any of the following request handlers will automatically register the intent to participate in a transaction:

• EventInstance (Subscriptions)
• AsyncHandler (Asynchronous services)
• APIProcessor (WS Requests)
• BaseService (REST Requests), ...

However, the actual transaction is initiated if and when a database connection is acquired. The entire transaction is managed by the local (i.e. NOT distributed/XA) JDBC transaction support available with the connection.

All the above-mentioned service invocations would initiate a single transaction automatically and commit or rollback at the end of the service invocation. Note that the database transaction remains open until the request invocation completes. Therefore, it’s advised to write efficient/modular custom services and avoid long running monolithic services to avoid holding database connections for too long, which would result in running out of database connections.

The transaction API is not exposed as ThingWorx scripting API and therefore, not accessible from the JavaScript based custom services or scripts.

Most of persisted data changes (Stream, Value Stream, Data Table, Persisted Properties) are queued and batched to improve data ingestion throughput. Therefore, the actual writing to the database is performed by a separate executor thread (Data Processing Subsystem). This thread pool will create its own transaction and thus will not be part of the transaction initiated by the originating service request.

Model changes (modeled entities CRUD operations) in contrast are not queued or batched and hence would be performed as part of the transaction initiated by the originating service request.

It should also be noted that any and all database operations including reads (i.e. via select statements) require a transaction as per the JDBC specification. While typical applications may use ‘auto commit’ feature with reads ThingWorx does not treat them as separate request since it complicates transaction handling when multiple reads and writes are interlaced in service invocations. Thus transactions initiated by a Read operation will also remain open until the request invocation completes.

When there are more than one database instances are involved (usually with multiple Persistence Providers) there will be a separate connection acquired for each database and those transactions will be handled independently per connection. ThingWorx does not support distributed transactions with two phase commit protocol which means that the commit and rollback would be just best effort.

Lets consider a couple of use cases

A. Long running service (the pauses represent for example calls to an external system)

pause(5000); // 5 sec
result =  MyDataTable.GetDataTableEntryCount(); // Start DB Transaction
pause(10000); // 10 sec

This service is invoked via REST :

1. The HTTP request reaches the platform
2. The platform automatically creates a transaction context
3. The custom Service is invoked
   
   1. GetDataTableEntryCount() hits the persistence provider, a DB transaction is started
   2. pause(10000) does not interact with the DB, but the previous transaction remains open (idle)
4. The DB transaction is closed only after the entire request is complete

B. Request that involves multiple threads

me.prop1 = 10; // logged property with data change subscription, the subscription queries a Data Table
myAsyncSrc();  // Asynchronous service that also queries a Data Table

This service is invoked via REST :

1. The HTTP request reaches the platform
2. The platform automatically creates a transaction context
3. The custom Service is invoked:
   1. me.prop1 = 10;
      1. In-memory property update is not atomic nor transactional
      2. A subscription that access the persistence provider is triggered, but it is executed by a different thread and an new transaction context  is created (not nested)
      3. The property value is logged into a value stream, the persistence is performed by an other thread asynchronously
   2. myAsyncSrv()
      
      1. This asynchronous service is executed by a different thread and an new transaction context is created (not nested)
4. The request is complete without ever starting a persistence transaction since the service/thread itself did not acquire a database connection

Notes

• The Transactions API are  available in the Extension JSDK - TransactionFactory

[smainente]

New Section in the Help Center documenting the behavior: Performing Long Running Work When No Connection is in Use