Re: What is the actual difference between innodb_autoinc_lock_mode 0,1,2
Is replication involved? If so is it SBR or RBR? SBR
Is IGNORE used? NO
Do you care what the AUTO_INCREMENT values are? At least one of the modes allows one LOAD to generate non-consecutive numbers. Is that OK? Yes, I don't care, I'm ok with the jumps in the sequence provided all should be inserted simultaneously whilst they LOAD DATA INFILE runs parallel.
Is autocommit on? Are you using BEGIN...COMMIT? Since its gona be a load data I don't really need sessions to do auto commits. If it were to be serious of inserts then yes I agree to your point. Load Data auto commits once its transaction is done, and if its killed in between it can roll back to its previous stage thus no impact in replication.
Have you timed each method? If so, please share your findings. -> Please find below for 7 threads of concurrent load data.
Using Option '0' -> It locks the entire table for each threads and keeps the threads in FIFO model and loads 20 secs for each loads one by one. To complete all 7 it took ~ 20*7=140Secs. The only advantage is, it doesn't jump the sequence of AIs for the next load data.
Using Option '1' -> It locks the entire table for each threads and keeps the threads in FIFO model and loads 13 secs for each loads one by one. To complete all 7 it took ~ 19*7=133Secs . The only difference from 0 is, it DOES jump the sequence of AIs for the next load data.
Using Option '2' -> It locks the entire table for each threads and loads the threads randomly picking each loads parellelly . To complete all 7 it took ~ 65Secs [Standard for all]. ofcourse it mixes and jumps the sequence of AIs for the next load data.
> Perhaps a Replication issue, especially with SBR. --> May I know exactly what is the problem in using SBR for option 0 and 2 , and what is handled in option 1 ?
> I mentioned possible issues. Perhaps none of them apply to you. --> Its just SBR Replication which I see from your explanation as a possible issue. Again, I would like to ask exactly what could go wrong with SBR??
> Don't all three options allow multiple processes. Sure they may be delayed, but it still works? --> Only '2' allows multiple processes at a time. Yes it works for Prod. But as you say for DR, I should do a workaround if required only for LOAD DATA module.
Why did you decide that 2 was best? Performance in each process? Yes for PROD
Performance for finishing all? Yes for PROD
Apparent parallelism? Yes for PROD
Other? Importantly, my project requires to be available for all kinda LOADS to a table at any time or any instant. For this, if we have a limit to "innodb_lock_wait_timeout" the options '1' and '0' will definitely show its teeth to the users who upload. Whereas option '2' treats any number of LOADS at a given point of time and starts to process its request the very second, thus no issue on users end.