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

Question

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.

Please let me know what you think. Thanks!

Answer 1

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.

Answer 2

By the way you described the fear of encountering a DEADLOCK, the causation may not be clearly understood (unless there is more to your querying than described in the question).

A good summary someone else wrote:

1. Query 1 begins by locking resource A
2. Query 2 begins by locking resource B
3. Query 1, in order to continue, needs a lock on resource B, but Query 2 is locking that resource, so Query 1 starts waiting for it to release
4. In the meantime, Query 2 tries to finish, but it needs a lock on resource A in order to finish, but it can't get that because Query 1 has the lock on that.