What is the actual difference between innodb_autoinc_lock_mode 0,1,2 I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.

I see in "0" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. In this case it doesn't jump the Auto Increment numbers.

Also I see in "1" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. And then it sets the max(AI_column)+some_creepy_jump.

But I see in "2" option, it doesn't lock the entire table. Instead it keeps inserts for each process simultaneously and inserting records for which ever request falls in randomly and ends all threads with average time around (1.21 sec for  7 parellel threads using load data having 1000000 record each). In this case it has multiple transactions in mixed order. And then it sets the max(AI_column)+some_creepy_jump atlast.

I'm using mysql 5.1.61 .


My question is what is the use of option 1? 
And why does it stay as default later versions of Mysql 5.1.22 ?
Have anyone comeaccross any disasters with option 2? 


As my project demands multiple processes using load data for a table. And I have tested the above options and finalized to go with option 2. Kindly correct me if my experiments are wrong.
What replication method are you using?  Option 2 is fine with ROW based, but for STATEMENT replication your auto inc values on the slave are not guaranteed to be the same. 

Option 2 also means that your auto inc values probably won't be consecutive, but maybe that does not matter to you.

Option 1 is the default because it is the safest method with STATEMENT based replication (which I think is the default).  It is more scalable than option 0 since it only does a table level auto inc lock for bulk inserts but it still provides consecutive auto inc values with no gaps.  Some folks rely upon that behavior.

Sounds like for your use case you're fine with the setting you have selected, as long as your replication settings are OK.

Hope that helps.