Search code examples
mysqluniqueisam

Should I lock an ISAM table to insert a value into a unique key field?

I have an ISAm table in mySql that was created similar to this:

create table mytable (
 id int not null auto_increment primary key,
 name varchar(64) not null );

create unique index nameIndex on mytable (name);

I have multiple processes inserting rows into this table. If two processes try to insert the same "name", I want to make sure that one of them either gets an error or finds the row with the matching "name".

Should I lock the table and in the lock make sure that the name doesn't exist, or should I rely on the server giving an error to one of the processes that try to insert a value that already exists in the unique indexed field?

I'm a bit hesitant to use a lock because I don't want to get into a deadlock situation.

Solution

  • Do not bother locking, your index will prevent duplicates. You should handle the error code from your application.

    MySQL should return an error code of 1062 (or SQLSTATE 23000) when your unique key constraint is violated.