Single Large Transaction VS Multiple Small Transaction

Question

I am designing a system where users can select/ insert/update/ delete multiple rows of multiple tables multiple times (n times in a loop based on some logic, the value of n ranges from 1000 to 50,00,00,000). In my design, only 10 threads will be active simultaneously doing this job. Assuming a single iteration itself takes a few seconds at least, what are the advantages of doing each iteration in a single transaction vs some m iterations (value of m ranges from 100 to 1000) in a transaction?

Answer 1

For my understanding, each interaction in a single transaction is easy to control and low risk of contention in the lock. And the related transaction log will be small. The defect is its efficiency is not very good.

compared with some m interactions (value of m ranges from 100 to 1000) in a transaction, it has better performance normally. Transaction log will be larger. If you can ensure there is no lock contention, it should be a better choice.

The interactions value can be tuned.