The feature to implement is to allow a user to select items and apply data update in bulk. It is very similar to JIRA's ability to bulk update a list of selected issues. In my case:

  • I iterate over the items,
  • and for each item, I simply call the existing method used for individually updating an item.

Take note, I cannot do this in sql level (ie. sql batch update) as business logic is in java.

Evidently, for performance, it will be slower given that a user decides to update multiple items, hence you can expect speed = # of items * rate of transaction. So in effect, updating an item individually should be same speed as bulk updating a list where the list contains a single item. That is fine, as it's a trade-off.

My other concern however is this: the method used to update individually, will remain unchanged, and will simply be called by bulk update multiple times according to the number of selected items; this method acquires locks to involved tables until it finishes its transaction. This in effect, would cause locking for long time if there were multiple items to be updated; hence a user would not be able to use the view and search page that retrieves from the locked tables. It's a trade-off that may not be as acceptable as the first point I discussed above.

I am conflicted, given that the nature of the existing method for individually updating an item is really to acquire locks, and I am not suppose to touch that code. I am supposed to only reuse that method, calling it multiple times to implement the bulk update. Is this 2nd trade-off something we cannot bypass, is it a valid trade-off?

Please note also, I am not an expert in transactions and locks.

1 Answer 1


You certainly can use SQL and bulk updates is one of those areas in which it can be a superior solution (in terms of performance) than an ORM layer or firing of multiple small updates (with an overriding Transaction).

It's the cost of lots of networks calls vs a larger update statement.

You need to measure the performance difference, but I'm guessing that your I/O across the network is the larger bottleneck.

In terms of locks/txns etc. I'd carefully draw out where the boundaries of these are. You might find you're able to reduce the Txn overhead. Using AOP style Txns helps here, i.e. Injecting the start and end of a txn at exactly the points you need them and no more.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.