Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
I'm using SBR. Kindly tell me what could go wrong in option 2 with SBR and how is that handled in option 1. Do you mean to say option 1 doesn't do any table level lock and doesn't generate any gaps between one bulk insert to another? –  Mannoj Feb 11 '13 at 9:58
    
See: dev.mysql.com/doc/refman/5.5/en/…. Relevant passage is: "Auto-increment values are not ensured to be the same on the slaves as on the master if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the master and slaves do not use the same lock mode". Option 2 is not safe to use with SBR. Regarding the second part, Option 1 uses table level locks for bulk inserts, but not for "normal" inserts. It does not generate gaps because any bulk inserts are serialized and the next chunk just waits for the lock to get it's IDs. –  drogart Feb 11 '13 at 22:25
    
Imagine a scenario where you have 10 bulk inserts having avg insert time of each as 50secs. And 10 of them are initiated at same time. For options 0 and 1 it keeps them for longer time and inserts one by one whereas innodb_lock_wait_timeout=150 as default for the 4 to 10 bulk insert in the queue gets timedout. Such scenarios are prone in my application. And to set a timeout value is a non-deterministic idea. Keeping option 2 and am having workaround for replication, as these LOAD data are kept in csv at application end to be re-use if required. By this my PROD will be in good shape to users. –  Mannoj Feb 13 '13 at 7:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.