CODEWITHSUNDEEP
mysqlsql
Nikul Panchal
Nikul Panchal

Reputation: 1673

how to apply race condition for insert in mysql

I want to check if record is greater than 0 in wp_joinroom table, then only i want to add the record, i have tried this things but i am not sure, how to do it in standard way, can anyone please help me how to resolve this things ? any help will be really appreciated, here i have added my query for that, which i tried, thanks

query :

    SELECT coun(*) FROM wp_joinroom where room_id = 1;
    INSERT INTO wp_joinroom (room_id,is_admin,user_id) values (1,0,2)

Upvotes: 1

Views: 176

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want room_id to be unique in wp_join_room, then use a unique constraint or index:

alter table wp_join_room add constraint unq_wp_join_room_room
    unique (room_id);

This will prevent the table from having duplicates. The database validates this condition, so there is no issue with race conditions. Such reliability is very hard to guarantee with multiple queries.

Note that an attempt to insert duplicates would result in an error. If you want to avoid the error, use insert ignore, on duplicate key update, or on conflict. I recommend one of the last two, but which depends on the version of MySQL you are using.

Upvotes: 0

MBeale
MBeale

Reputation: 750

In pure SQL you could try the following.

INSERT INTO  wp_joinroom (room_id,is_admin,user_id)
  SELECT * 
    FROM (VALUES ROW(1,0,2)) AS temp 
    WHERE NOT EXISTS (SELECT room_id FROM wp_joinroom where room_id = 1);

That would only insert the values if the room_id doesn't exist in the table.

Upvotes: 1

nbk
nbk

Reputation: 49375

You can do a dynamic sql approach.

Thsi wouuld only insert the values, if no row with room_id 1 exists

SELECT IF ( NOT EXISTS(SELECT 1 FROM wp_joinroom where room_id = 1) 
,@sql := 'INSERT INTO wp_joinroom (room_id,is_admin,user_id) values (1,0,2)'
,@sql := 'Do NULL') ;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

Related Questions