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.
When there are multiple mysql users performing inserts in parallel ?
When a user updating maximum value of the auto increment column and the same time another user is inserting a new row ?
how mysql handles before insert trigger when there is an update column inside the trigger changes auto increment value to be inserted ?
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.