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
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.