CODEWITHSUNDEEP
sqlmysql
Brad
Brad

Reputation: 11505

Locking on insert

I have a pretty simple case I am a bit confused about. I want to insert a record, which must be unique. Thus, if it exists, I need to either replace it or delete and re-insert it. If it does not exist, I need to create it - but need to worry about someone else doing the check and creating the same one at the same time.

Can anyone recommend the "correct" way of doing this with respect to locking in MySQL? i.e. preventing someone from concurrenly doing the same thing?

If I do this - I am not protected against concurrent inserts of records with the same condition:

BEGIN TRANSACTION
SELECT (condtions) FOR UPDATE
if rows exist 
  UPDATE
else
  INSERT
COMMIT

Seems like a simple thing - obviously very rusty at SQL...

Also:

If I am holding a lock, and another user tries to read/write something that is held by the lock - will the other user receive an error, or will their processing just get delayed until the lock is released?

Upvotes: 0

Views: 158

Answers (2)

ajreal
ajreal

Reputation: 47311

I believe you are looking for REPLACE (if exist, remove, and re-insert) - http://dev.mysql.com/doc/refman/5.1/en/replace.html

OR

insert ... on duplicate key update ... /* does not remove, just update */

Upvotes: 1

Ratna Dinakar
Ratna Dinakar

Reputation: 1573

Heres's the complete guide for the same.

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

If I'm correct lock table with read access, do the transaction and unlock table.

Upvotes: 0

Related Questions