MySQL 4.1.8
I have an InnoDB table with a unique constraint:
CREATE TABLE TEST ( ID bigint NOT NULL auto_increment, NAME varchar(100) NOT NULL, VALUE varchar(100), PRIMARY KEY (ID), UNIQUE KEY IX_NAME (NAME)) ENGINE=InnoDB
Given a particular unique name, I need to either find the existing record and return the value or create a new record. I am having problems working out how to avoid a race condition though.
If two sessions both issue the query:
SELECT * FROM TEST WHERE NAME='uniquename'
then both could get no row returned and hence try and create a new record (one of which would fail with a unique key violation).
Is it possible to get MySQL to lock the table/index so that when two sessions run the above query and no rows are found, the second select will block until the first transaction has completed? Is there an alternative way of structuring the problem that would make this concurrently safe?
Thanks,
Phil
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
