Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X
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.
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.
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;
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.
// result: INFOTABLE dataShape: ""
var query_result = me.IncreaseCounterDB({
counter_name: 'DemoCounter' /* STRING */
});
var result = query_result.rows[0]["newvalue"];
// result: INFOTABLE dataShape: "SingleIntegerDatashape"
var query_result = me.GetCounterDB({
counter_name: 'DemoCounter' /* STRING */
});
var result = query_result.rows[0]["value"];
// result: NUMBER
var query_result = me.ResetCounterDB({
counter_name: 'DemoCounter' /* STRING */
});
var result = 0;
{
"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:
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.