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

Transaction Handling in ThingWorx

smainente
Pearl

Transaction Handling in ThingWorx

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

Snag_1a176990.png

 

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

Snag_1a175a6d.png

 

Notes

 

1 REPLY 1

Re: Transaction Handling in ThingWorx

Hi Stephane,

 

Excellent article, thank you for explaining the inner workings of the platform.

 

In usecase B, in the subscription that queries the DataTable, when is the Subscription DB transaction closed? I would expect it to be closed at the end of the Subscription code, after it ran the Database query, right?

I guess it would be the same case also for the Async service - the DB transaction ends at the async service code execution?

I'm asking this because I see the transaction starts in those two usecases, but not the close events.

 

Have a good day!

Announcements

Check out the upcoming Expert Session: Understanding ThingWorx Navigate Licensing in Community "Customer Events" section.