I have some bulk insert vb.net code (working) that I have written. It calls ExecuteNonQuery() for each insert and then at the end does a commit().
My question is on where these inserts are placed, while waiting for the commit() command? I have not made any changes to support batching as yet. So with my existing code a million rows will be inserted before calling commit(). I ask this question obviously to know if I will run into memory issues, hence forcing me to makes changes to my code now.
In the normal rollback journal mode, changes are simply written to the database. However, to allow atomic commits, the previous contents of all changed database pages are written to the rollback journal so that a rollback can restore the previous state. (When you do so many inserts that new pages need to be allocated, there is no old state for those pages.)
In WAL mode, all changes are written to the write-ahead log.
In either case, nothing is actually written until the amount of data overflows the page cache (which has a size of about 2 MB by default).
So the size of a transaction is not limited by memory, only by disk space.