# InnoDB and unique columns

**URL:** <https://forums.percona.com/t/innodb-and-unique-columns/1047>\
**Category:** Other MySQL® Questions\
**Created:** [December 29, 2008, 7:14am UTC](https://forums.percona.com/t/innodb-and-unique-columns/1047 "2008-12-29T07:14:41Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![DonJulio](https://avatars.discourse-cdn.com/v4/letter/d/d26b3c/32.png) [@DonJulio](https://forums.percona.com/u/DonJulio)\
**Post date:** [December 29, 2008, 7:14am UTC](https://forums.percona.com/t/innodb-and-unique-columns/1047/1 "2008-12-29T07:14:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [December 29, 2008, 11:11am UTC](https://forums.percona.com/t/innodb-and-unique-columns/1047/2 "2008-12-29T11:11:04Z")

</div>

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.