On Nov 13, 2007 11:39 AM, Baron Schwartz <baron@stripped> wrote:
> InnoDB can also lock the gap, which will prevent new rows that would
> have been returned by the SELECT. The manual has more info on this in
> the section on consistent reads in InnoDB. FOR UPDATE will do what you
Interesting, I didn't think that would work, but the manual does say it will:
"You can use next-key locking to implement a uniqueness check in your
application: If you read your data in share mode and do not see a
duplicate for a row you are going to insert, then you can safely
insert your row and know that the next-key lock set on the successor
of your row during the read prevents anyone meanwhile inserting a
duplicate for your row. Thus, the next-key locking allows you to
"lock" the non-existence of something in your table."
There's another suggestion in the comments on that page: INSERT IGNORE
and then check the number of rows affected. But, not portable to
P.S. I enjoy your blog, Baron.