Using SELECT .. FOR UPDATE in SQL Query/Command service (postgresql)

[nmilleson]

I have a service in ThingWorx 9.0.1 that is selecting a row for update to avoid concurrent updates.  The service works (in that it updates the tables correctly) but it always throws an exception.  The code:

begin;

select * from opportunity where opportunity_number = 2 for update;

update opportunity set description = 'testing 1.2.3.4.5..' where opportunity_number = 2;

commit;

I've tried several ways to eliminate the error (changing the result type, changing from query to command, etc..)  I always get one of these errors:

--When I'm using the Query handler:

Unable to Invoke Service test on Thing.DB.Forecasting : Execute Query failed: org.postgresql.util.PSQLException: No results were returned by the query.

OR

--When I'm using the Command handler:

Unable to Invoke Service test on Thing.DB.Forecasting : Execute Update failed: org.postgresql.util.PSQLException: A result was returned when none was expected.

Like I said before, the service does work, but I'd like to get rid of that exception if possible.

[odukiya]

Hello @nmilleson

It is impossible to run select and insert in one SQL Service(Query or Command) with ThingWorx Thats the reason you are facing the issue. You can try the following approach:

• Define service A for select and service B for insert
• Define service C to call service A and service B.
• Note that service A/B will be committed to database once executing successfully, which means even if service C fails midway after calling service A/B, a rollback to database will not happen.

[nmilleson]

@odukiya 

Thanks for the reply.  One more followup question regarding your last bullet point:

• Note that service A/B will be committed to database once executing successfully, which means even if service C fails midway after calling service A/B, a rollback to database will not happen.

If I 'SELECT FOR UPDATE' in service A, can I be 100% sure that service B will use the same database connection (currently I have a max connection count of 10 in the Thing Configuration)?  If not, there's the possibility that I won't be able to successfully run service B (due to the row lock from service A), correct? 

Would I be better off perhaps creating a stored procedure?

[odukiya]

Hello @nmilleson 

It is a really nice question , I am not pretty sure about it let me check and i will get back to you .

Thanks

[mnarang]

I believe It's better if you create stored proc on DB side and call it from Thingworx in this case so that it can be a single threaded execution.

[nmilleson]

@mnarang and @odukiya ,

Thanks for the replies.  I'll stand by to see if @odukiya discovers an answer.  If not, I'll mark the stored procedure suggestion as the solution.  Thanks!

[odukiya · Accepted solution]

Hello @nmilleson 

I think using stored procedure is a better way and you can use that for single thread execution. I couldn't with 100% certainty claim that my approach use single db connection.

Thanks

Om Dukiya