Thread Safe Coding in ThingWorx, Part 2: The Database Locker Approach and Comparison

[#1]

Thread Safe Coding, Part 2: The Database Locker Approach and Comparison

Written by Desheng Xu and edited by @vtielebein 

Overview

This is the second on this topic, describing an alternate approach to thread safe coding than one which requires the Java extension. The demo use case here is the same as in the previous post, and there is a section at the end comparing the two approaches.

Database Locker for Thread Safe Coding

The database locker is an advanced topic, so some experience with the database thing is assumed. The following steps demonstrate how to be thread safe with a database thing.

1. Create New Database Instance, and New Table for counter
   It is strongly recommended that a new database instance be created outside of the ThingWorx database schema. This guide will NOT include instructions to create the new database instance.
   
   Use the following SQL commands to create a new table:

   DROP table IF EXISTS counters;
   
   
   CREATE TABLE counters (
       name VARCHAR(100) unique ,
       value integer NULL,
       PRIMARY KEY(name)
   );
   
   INSERT INTO counters
   values('DemoCounter',0);

   This will create a new table called counters, initializing the first counter, called DemoCounter with the value 0.
   
   

2. Create a Function to Increase and Return the New counter Value
   Use the following sample code to create a table lock function:

   CREATE OR REPLACE FUNCTION IncreaseCounter(coutner_name VARCHAR(100), OUT newvalue INTEGER)
   AS $$
   BEGIN
      LOCK TABLE counters IN ACCESS EXCLUSIVE MODE;
      SELECT(SELECT value FROM counters WHERE name = $1) + 1 INTO newvalue;
      UPDATE counters
      SET value = newvalue
      WHERE name = $1;
   END;
   $$ language plpgsql;​

   Or use the following SQL command to create a new row level locker function:
   

   CREATE OR REPLACE FUNCTION IncreaseCounter(counter_name VARCHAR(100), OUT newvalue INTEGER)
   AS $$
   BEGIN
      SELECT value FROM counters WHERE name = $1 FOR UPDATE INTO newvalue;
      newvalue := newvalue + 1;
      UPDATE counters SET value = newvalue WHERE name = $1;
   END;
   $$ language plpgsql;

3. Create a Database Thing
   Create a thing with the template "database" within ThingWorx, and use the PostgreSQL Driver to connect to the new database instance created above.
   
   
4. Create New Services in the Database Thing
   The service IncreaseCounterDB would be a SQL Query service:

   SELECT * FROM public.IncreaseCounter([[counter_name]);​

   counter_name would be the input parameter, a STRING which is marked as required.
   
   The service GetCounterDB would be another SQL Query service:

   SELECT value FROM public.counters
   WHERE name=[[counter_name]]
   LIMIT 1;

   counter_name would be another input parameter, a STRING which is also marked as required.
   
   The service ResetCounterDB would be a SQL Command service:

   UPDATE public.counters
   SET value = 0
   WHERE name=[[counter_name]];

   counter_name is yet another input parameter, also a STRING and also required. 
   
   

5. Wrap the Database Thing Service
   The above database thing service will return an InfoTable, but not an integer. If it's inconvenient to use an InfoTable, wrap the service up into a local Javascript service and return an integer value.
   
   The service IncreaseCounter is a wrap up of IncreaseCounterDB and returns an integer value:

   // result: INFOTABLE dataShape: ""
   var query_result =  me.IncreaseCounterDB({
   	counter_name: 'DemoCounter' /* STRING */
   });
   var result = query_result.rows[0]["newvalue"];

   
   Similarly wrap up GetCounter into GetCounterDB:

   // result: INFOTABLE dataShape: "SingleIntegerDatashape"
   var query_result =  me.GetCounterDB({
   	counter_name: 'DemoCounter' /* STRING */
   });
   var result = query_result.rows[0]["value"];​

   
   And ResetCounter into ResetCounterDB:

   // result: NUMBER
   var query_result =  me.ResetCounterDB({
   	counter_name: 'DemoCounter' /* STRING */
   });
   var result = 0;​

6. Run the Test Again
   If necessary, head back to the previous post to obtain the tool. Then just change the end point and run a new test:

    {
       "host":"twx85.desheng.io",
       "port":443,
       "protocol":"https",
       "endpoint":"/Thingworx/Things/DatabaseDemo/services/IncreaseCounter",
       "headers":{
           "Content-Type":"application/json",
           "Accept": "application/json",
           "AppKey":"5cafe6eb-adba-41df-a7d6-4fc8088125c1"
       },
       "payload":{},
       "round_break":50000,
       "req_break":0,
       "round_size":50,
       "total_round":20
   }​

   Run:
   

7. Validate the Result
   Execute the service GetCounter to validate the result:
   

Overall Performance Comparison

The Java Extension performance looks the best here, but the database row lock will perform better if there are multiple counters.

InfoTable Type Property

InfoTable properties have the same thread-safe challenges discussed previously, but they also have some additional challenges due to the way data change events are triggered. This is outside of the scope of this document, but it is worth a very brief mention here. 

In general, the data change event for an InfoTable fires when the reference to the table is updated, and not the contents of the table. If the values of an InfoTable are updated directly, say by adding or removing a row, then the data change event will not be triggered because the value has technically not changed. Instead, the InfoTable has to be cloned, then modified, and then assigned back to the Thing so that the reference changes as well. Such additional considerations must be made when using other property types than those shown here.