Grails volumetric processing with lock on the table

Question

I have a Grails service that talks to SQL Server that does bulk processing (updating records from a database or creating new records) from over 80,000 records. While this process is taking place, these records are blocked by this process. If someone else tries to update the record individually, it disconnects, waiting for this record. How to request and update records during mass processing? so it is not blocked?

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

Answer 1

flush=true writes data from your local hibernation session to the database, so it is important to avoid memory problems on application servers for large mass operations like yours, but this does not affect the way the database blocks rows.

What you need to change is transaction boundaries . Your rows are blocked because they are in the same transaction and are probably undesirable (or database efficient). If there is a reason that all of these lines should be blocked, you can make your service void. Then each row will be updated in its transaction and only be locked for a short while. However, this can be much slower.

For the middle tier, I recommend a hybrid approach where you set your service as static transactional=false , but then use the closing .withTransaction {...} and the loop inside to do a certain number of lines at a time.

This is an old article, but there is some useful information that you should read that gives code examples from what I mentioned. http://sacharya.com/transactions-in-grails/