cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

Transaction Handling in ThingWorx

No ratings

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

smainente_0-1680102269768.png

 

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

image

 

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

image

 

Notes

 

Comments

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

Version history
Last update:
‎Mar 29, 2023 11:04 AM
Updated by:
Labels (1)
Contributors
Tags (1)