Updating millions of rows in MySQL - when you need to commit

Question

I have a for loop that goes through millions of objects. What would be the proposed way to do this? Here are some examples that I thought of:

# after each for item in items: cursor.execute() conn.commit() # at the end for item in items: cursor.execute() conn.commit() # after N items for n, item in enumerate(items): cursor.execute() if n % N == 0: conn.commit() conn.commit() 

Which of the following would be most effective?

Answer 1

You ask if I should commit ...

• only once at the end of a huge operation
• after updating each line
• after each element N.

First of all, if you are working with MyISAM, do not worry about it. MyISAM does not complete transactions.

If your system will not be damaged by making only some changes to your line, you should fix it after each N element. This is because committing after each item will significantly slow down your processing. Only committing once at the end of a huge operation is likely to delete your rollback space or take an indescribably long time during which your other database users will slow down.

I went through this countless times. Any N greater than about 20 will be fine.

Answer 2

Perhaps the "end" is one , since you are BEGIN and COMMIT only once, and this is one transaction with its own scope. This is simpler in terms of concurrency: basically the transaction says: this is my table now, do not touch anyone.

If you execute several times (two other solutions), you BEGIN and COMMIT transaction many times (after you have completed the transaction, the next transaction has started). This means more chances to interrupt from other current database operations. Also - these operations take time themselves.

However, you must run a test that mimics your use case. I would be interested to know whether it depends on certain conditions (the number of rows to insert, session configuration, data type, indexes used), one or another solution may prevail.