I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id column has skipped a number.

So I was wondering what happens when you insert and forget to commit is this bad for the Database? Or does is just skip because when you insert it assigned an id to it but didn't save it?


1 Answer 1


A skipped id can happen in several situation. Keep in mind that an INSERT generally starts by allocating all the ids that it may need. Here's a few examples where AUTO_INCREMENT ids are 'burned':


INSERT IGNORE -- and some of them are ignored due to dup key


The one I dislike is:

SET autocommit = 0;
forget to ever do the COMMIT

For that reason, I avoid autocommit=0.

In a multi-master (including Galera) setup, with auto_increment_increment > 1, the INSERTs on one master skip values.

  • great thanks for reply, but is it bad when an id is skipped?
    – spenf10
    Commented Aug 26, 2015 at 5:03
  • @spenf10 no, it is not. Unless you need unbroken series (for accounting etc) and in that case you should not use auto_increment for that.
    – jkavalik
    Commented Aug 26, 2015 at 5:57
  • Another potential 'badness' in burning ids is when you burn them so fast that the integers threaten to overflow. Once I got in trouble with SMALLINT UNSIGNED (65K max) and "I'll never need more than a few thousand". But the code was burning ids.
    – Rick James
    Commented Aug 27, 2015 at 22:19

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.