CODEWITHSUNDEEP
mysqlsqlpdoinnodbrace-condition
IainW
IainW

Reputation: 55

How to prevent race condition with INSERT?

How would i go about preventing race conditions when INSERTing a row into a table that contains no unique index. For example say my table is....

key | slot | label
------------------
 1  |  1   | some
 1  |  2   | some
 2  |  1   | some
 2  |  2   | some

... is the only way to prevent such race conditions to create a composite unique field such as "key:slot" e.g.

id  | key | slot | label
------------------------
1:1 |  1  |  1   | some
1:2 |  1  |  2   | some
2:1 |  2  |  1   | some
2:2 |  2  |  2   | some

...or is there a more efficient way that has escaped me? What about if i was to check for duplicate rows after the insert has been performed and roll the transaction back if there are any found?

Upvotes: 0

Views: 1822

Answers (2)

Freddie Fabregas
Freddie Fabregas

Reputation: 1203

How about using LOCK TABLES syntax to prevent race conditions when inserting?

Upvotes: 1

John Woo
John Woo

Reputation: 263723

actually you can do it without the key:slot column. You can define a unique compound key on the table. eg,

ALTER TABLE tableName ADD CONTRAINT tb_uq UNIQUE (`key`, slot)

Upvotes: 2

Related Questions