You should keep the begin_transaction/end_transaction block small, in the sense that it takes no more than 0.1s or maybe 0.2seconds to execute this block. That at least is my rule of thumb.

So if you need to update 100.000 records, you do it in transaction blocks of for example 50.


You can use begin_transaction/end_transaction, but indeed it lock the database of part of it and that can result in lock timeouts and/or slow performance for the rest of the system. But ff you keep the blocks small, it will work just fine.