How could i create a SQL transaction in a service?

[htran-21]

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

[PaiChung]

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.

[htran-21]

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

[CarlesColl]

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.

[htran-21]

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

[CarlesColl]

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

[slangley]

Hi @htran-21.

If one of the previous responses were helpful in answering your question, please mark it as the Accepted Solution.  Or if you found an alternate solution, it would be greatly appreciated if you would post it here and mark that as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon