•  

Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB and unique columns

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB and unique columns

    #1
    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
    Tags: None

  • #2
    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.

    Comment

    Working...
    X