Performance improvement in BEGIN EXCLUSIVE compared to BEGIN IMMEDIATE?
(1) By anonymous on 2020-11-28 07:15:54 [link] [source]
According to the documentation for BEGIN TRANSACTION: EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway. My situation is that I am running on Windows NOT in WAL mode (journal mode=DELETE) - so the behaviour of BEGIN IMMEDIATE and BEGIN EXCLUSIVE should be different (according to my understanding of the document snippet above). I currently execute a relatively large (typically approx 1 million rows across 5-10 tables) import process which is guaranteed (based on the design/architecture of my application) to run without any other processes or threads connecting at all to the database (so only a single connection running in a single thread has access to the database during the import) - this is already controlled and guaranteed (so this part is not the discussion point :) ) I process the import volume in batches, each wrapped in their own transaction and currently use BEGIN IMMEDIATE to start the transactions. Will there be any performance benefit (however minor) in changing to use BEGIN EXCLUSIVE on Windows .... or will it maybe be slower as maybe extra locking mechanisms are used to guarantee that only that connection has access to the database file?
(2) By Dan Kennedy (dan) on 2020-11-28 15:27:09 in reply to 1 [link] [source]
It will make no difference at all.
Transactions have to do all the same stuff regardless of whether they are started with BEGIN, BEGIN IMMEDIATE or BEGIN EXCLUSIVE. All that changes is exactly when they invoke the system calls to take the various required file locks.
(3) By anonymous on 2020-11-28 17:43:36 in reply to 2 [source]
@Dan, Many thanks for your speedy and concise response. Much appreciated :)