Not the answer you need?
Register and ask your own question!

InnoDB and unique columns

DonJulioDonJulio EntrantCurrent User Role Participant
I have a column declared "unique" when the table was created (chars[10]). As InnoDB uses multiversioning and row-level-locking, I am not sure how the uniqueness is secured using INSERT. Do I have to lock the complete table?

Best,
DonJulio

Comments

  • vgattovgatto Contributor Current User Role Advisor
    InnoDB's row-level locking includes locking the index records and gaps between records, not just the data rows. Your unique index will have that record locked by the transaction attempting the insert, which will block the another transaction attempting to insert the same value. Once the first transaction commits, the second will continue with the expected duplicate key error.

    This same thing is going on all the time when you use auto-incrementing primary keys. There's no need to manually lock the table.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.