Running node.js pg and am having a deadlock issue with a CTE upsert statement. I'm getting a deadlock when trying to run the query statement. I want to know if there is a way to lock the table (in node.js pg) so that I won't run into this issue.
pg.connect(constring, function(err, client, done) {
if(err) { console.error('Error (updateHapiActivities: human_api_activities__c) connecting to DB ... : ' + err );
} else {
var queryConfig = "WITH upsert AS (UPDATE salesforce.human_api_activities__c SET calories__c = ($1), distance__c = ($2), steps__c = ($3), id__c = ($4), updatedat__c = ($5) WHERE userid__c = ($8) AND $
client.query(queryConfig, [new_cal, new_dis, new_step, wearable_id, update_time, date, create_time, user_id, source], function(err, result) {
if(err) { console.error('Error (updateHapiActivities: human_api_activities__c) updating query ... : ' + err);
} else {
var devArr = result.rows;
client.end();
}); // end upsert query
} // end else
}); // end connection
I tried putting a client.query("BEGIN;") ... and then after client.query("LOCK table IN SHARE ROW EXCLUSIVE" ... but it was erroring out with LOCK TABLE can only be used in transaction blocks.
Any help?