cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Alexandrite

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

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.

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

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

View solution in original post

6 REPLIES 6
Highlighted

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

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.
Highlighted

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

@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?

Highlighted

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

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

Highlighted

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

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.

 

 

 

 

Tags (1)
Highlighted

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

@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!

Highlighted

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

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

View solution in original post

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum!