Grails bulk processing having lock on the table

Question

I have a Grails service which talks to SQL Server, which does bulk processing(updating records from database or creating new records) of more than 80,000 records. While this process is taking place, those records are being locked by this process. If some one else is trying to update a record individually, it is timing out, waiting for that record. How to query and update the records during bulk processing? that way it does not get blocked?.

I have tried using flush:true, but it did not help.

Answer 1

flush=true writes the data from your local hibernate session to the database so is important to avoid memory issues on your application servers for large bulk operations like yours, but it does not impact how the database locks rows.

What you need to change is your transaction boundaries. Your rows are all locking because they are in the same transaction and that probably isn't desired (or efficient for the database). Unless there is a reason all those rows need to be locked, you could make your service stateless. Then each row would be updated in its own transaction and locked only briefly. This could be a lot slower, however.

For a middle ground, I recommend a hybrid approach, where you set your service to be static transactional=false but then use a .withTransaction {...} closure and a loop within to do a certain number of rows at a time.

This is an old article but has useful information you should read that gives code examples on what I've mentioned. http://sacharya.com/transactions-in-grails/