Skip to main content
H
htran-2115-Moonstone
15-Moonstone
February 7, 2018
Question

How could i create a SQL transaction in a service?

  • February 7, 2018
  • 6 replies
  • 4506 views

Hi,

As I know, a SQL transaction is managed (open / commit / rollback) by the platform, it would be opened before a service is invoked, commit if a service call is success without any exception, or rollback if there is an error occurred. My issue is, I have a service which will need to loop through 10000 things and do some changes, it means that there would have a lot of changes made, and the transaction would contain a lot of changes in database memory, and of course, that will hold / acquire too many locks on database resources for hours. I want to create 10000 small transactions instead of one huge transaction, but i cannot find an example how to open a transaction in scripting.

My Bests,

Hung Tran

6 replies

P
PaiChung22-Sapphire I
22-Sapphire I
February 8, 2018

You should probably create a single SQL command of the transact/rollback and fire that as a single execution in sql server, that does it for all the entities.

hopefully you can use a where clause to capture the group of entities to act upon that isn't a list of all the entities individually.

H
htran-2115-MoonstoneAuthor
15-Moonstone
February 8, 2018

Hi Pai Chung,

It would not work because the ambient transaction is already opened / managed by the platform, any SQL command would work in the same scope of transaction and same SQL connection / session.

My Bests,

Hung Tran

C
CarlesColl1-Visitor
1-Visitor
February 8, 2018

Hello Hung,

Here you face a problem I pointed out here: General Performance Recommendations and some perks (unofficial) Long Lasting Service, and also on the perks of this documented you have the solution: Build a queue system, dot.

P.D.: ThingWorx should have a navite queue system.

Carles.

H
htran-2115-MoonstoneAuthor
15-Moonstone
February 8, 2018

Hello Carles Coll,


Yes, that is Long Lasting Service. However, that would be not a problem if the platform allows to indicate a service that has no need an ambient transaction, and that will call another service with transaction support. By that way, the outer service will not block any resources and no any transaction is long.


Do you know there is any workaround?


My Bests,

Hung Tran

C
CarlesColl1-Visitor
1-Visitor
February 9, 2018

The workaround it's to build a queue system, where you start and end slices of your code.

Terms & ConditionsAccessibility statement