Is it a best practice to perform Normal or Batch Insert/Update/Delete operations on a back end database as part of a Scheduler service from webMethods.
Each time when the Scheduler service runs it will insert about a volume of >10K records in a table.
If we perform this operation with a high volume will there be a risk of a Lock on the table getting applied because of the webMethods transaction.I am currently facing this issue in of my current assignment.
Yes there could be a reason for db lock for heavy transaction load. But you have to fine tune your service and perform a rigorous stress/performance test.
Also involve DBA for a fine tuning of performance at the DB side. Also did you explore SP instead of using batch insert/update?
What is the actual issue here? Why is the table in the lock mode? do you have another thread inserting/updating the table at the same time?
Thanks for the reply folks…!!
We are not adding any lock on the tables when performing the Query execution on teh DB tables.These are simple Insert/Update/Select statements.
The Backend Databases are always accessed by multiple applications so there is a high probability that some one is also accessing the same table which webMethods uses.
But my doubt is can a couple of users who insert/update about >10K rows be able to take control of the whole table.
Is this something a DB issue.
In my opinion webMethods taking the responsibility of Insert/Update/Select operations in a scheduled process leads to a tightly coupled solution and creates a lot of dependancy.
Please share your thoughts.
If you are concerning locking on the tables by WM SQL service, I’d suggest that you ask your DBA to create stored procedures for all your DB operations, that way DBA can fully control if any lock is necessary and debug deadlock issue easily.
HTH,