I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can any one tell me how mysql handles auto_increment column in below cases by using AUTO-INC locking.

1.When there are multiple mysql users performing inserts in parallel ?

2.When a user updating maximum value of the auto increment column and the same time another user is inserting a new row ?

3.how mysql handles before insert trigger when there is an update column inside the trigger changes auto increment value to be inserted ?

4.If any extended insert is in progress and let assume it run for 2 seconds then any other inserts on the same table executed at the same time will wait to acquire auto-inc lock ?

Also tell me is a there any work around to get rid of this bug related to auto increment and triggers.

  • There are many mysql experts here like rolando (effective mysql author),etc. Can any one please answer my question...
    – vidyadhar
    Nov 13, 2014 at 23:54

1 Answer 1

  1. Auto increment values are assigned sequentially. In the event there is a rollback or deadlock you will find gaps in the auto inc value.

  2. The auto inc is always assigned previous_auto_inc_value+1. If a value is manually bumped you will get another gap in ids according to this rule. If there are gaps you could manually specify a value for a "missing" id but that won't bump the next generated auto inc value.

If you delete rows with max auto inc values that will not reset the next generated value unless you do an alter table specifying the lower auto increment value that has no values larger than it.

  1. Values for all columns in a trigger are accessible in a NEW object in the event of a before trigger or in the event of an after trigger you also have an OLD object.

  2. Since ids are assigned sequentially there is a shared lock and there may be some contention between concurrent inserts but one will not completely block another. You shouldn't expect the auto inc values for an insert to all be sequentially +1 of each other, although they likely may be.

  • 1
    it's not always previous_auto_inc_value + 1. It's actually previous_auto_inc_value + auto_increment_increment. See the auto_increment_increment variable. May 13, 2015 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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