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 :
The HTTP request reaches the platform
The platform automatically creates a transaction context
The custom Service is invoked
GetDataTableEntryCount() hits the persistence provider, a DB transaction is started
pause(10000) does not interact with the DB, but the previous transaction remains open (idle)
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 :
The HTTP request reaches the platform
The platform automatically creates a transaction context
The custom Service is invoked:
me.prop1 = 10;
In-memory property update is not atomic nor transactional
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)
The property value is logged into a value stream, the persistence is performed by an other thread asynchronously
myAsyncSrv()
This asynchronous service is executed by a different thread and an new transaction context is created (not nested)
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
View full tip
