By using the latest version of the BulkInsert component, we find a deadlock issue in our load test and OutSystems Support finds that it was induced by the same SQL statement related to the bulk insert. Sincerely hope someone could provide some advice to us for resolving this issue. On the other hand, our client wants to apply the latest version from the Forge Community.
Remark:Before the component upgrade, we did not have a deadlock issue induced by this component.Many Thanks, Jayson
Hi
I think is unlikely that the issue is related to the component. I don't know your scenario, but if you have multiple bulk inserts with a lot of data at the same time for the same table this is somewhat expected. I would suggest you to rethink your approach and consider using a queue (using a timer for example) to do the bulk inserts.
Without more information this is my best guess… please share in more detail your use case so that the community can better help you :) Cheers
Rúben
Hi Rúben, Thank you for your help.We have tried to group the extension log from the Service Center.
The deadlock occurred in the excel rows #2858 & #2859 which were from 2 different threads and tried to perform the bulk insert at the same second.
Could we assume that an insert statement which will not induce the deadlock even 2 threads insert different records at the same time? Instead, the Bulk Insert may induce database lock?
Actually, we apply the bulk insert because we want to reduce the database traffic.
However, for the load test, it's easy to happen that different threads want to insert different records at the same time. Many Thanks, Jayson
From your explanation looks like some sort of database lock, this happened to me in the past and the way we solved it was to create a queue to do the bulk inserts.
Hope this helps :)
Hi Rúben,
Thank you for your recommendation and previous experience. May I have any reference of making a queue to resolve this issue? We cannot apply OutSystems Timer for this case cos' it belongs to the browser request submitted by a user, we need to return the result message of the submission to the user. Appreciated if we could apply your reference to resolve this issue.Many Thanks,Jayson
Hello Jayson. We will analyze the case, but I say that the indication of Rúben makes a lot of sense.
I will return soon with our analysis.
Hi Galter, Thank you for your help.Appreciated if you could help for our case. Many Thanks,Jayson
Hi Rúben & Galter,
For our current finding, we tried the load test with 500 threads. More than 100 threads got the deadlock issues, so the error rate is above 20%. After that, we tried to replace the BulkInsert component by the older version one, then only 28 records got the deadlock issues, the error rate was reduced to 5.6%.
As the result difference is too obvious after replacing the bulkinsert component with the older version, we want to know if you have any idea of this case or the upgrade of this component. Many Thanks,Jayson