MySQL Forums
Forum List  »  InnoDB

Tricky locking question
Posted by: Anker Berg-Sonne
Date: June 10, 2005 03:26PM

Here's something I know how to do with table locks, but I can't figure out how to do it with transactions:

I have a table with residents in a nursing home. I need to do a fuzzy match on the residents - most, but all identifying data match - and if the resident can't be fuzzy matched I add the resident to the table. Because of the fuzzy matching I can't create a primary key or unique index on the identifyers.

This algorithm has to work in a concurrent environment, so it must not be possible for two threads to look up the same resident, and then create two resident records.

With locks I simply do

LOCK TABLES RESIDENT
SELECT .... /*look up with first criteria*/
SELECT ... /*look up with second criteria*/
...
INSERT ... /*insert if all the lookups failed*/
UNLOCK TABLES

With transactions I can ALMOST get it to work if I set the isolation level to serializable and I do the selects with FOR UPDATE, but the selects don't lock unless I DONT have an index on the criteria (which causes a table scan), so its still possible for two threads to create the same resident twice.

Noone seems to know how to do this. But it seems to be something that isn't that rare, so I'm hoping I am missing something.

Thanks/Anker

Options: ReplyQuote


Subject
Views
Written By
Posted
Tricky locking question
2654
June 10, 2005 03:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.