xLite xLite - 1 year ago 63
SQL Question

How do I lock on an InnoDB row that doesn't exist yet?

How can I guarantee that I can search if a username exists in my database, then insert that username into the database as a new row without any intercept between the


Almost as if I am locking on a row that doesn't exist. I want to lock on the non-existent row with the username "Foo", so that I can now check if it exists in the database AND insert it into the database if it doesn't already exist without any interruption.

I know that using
exist but as far as I know, that only works on rows that already exist. I am not sure what to do in this situation.

Answer Source

If there is an index on username (which should be the case, if not, add one, and pererrably a UNIQUE one), then issuing a SELECT * FROM user_table WHERE username = 'foo' FOR UPDATE; will prevent any concurrent transation from creating this user (as well as the "previous" and the "next" possible value in case of a non-unique index).

If no suitable index is found (to meet the WHERE condition), then an efficient record-locking is impossible and the whole table becomes locked*.

This lock will be held until the end of the transaction that issued the SELECT ... FOR UPDATE.

Some very interesting information on this topic can be found in these manual pages.

* I say efficient, because in fact a record lock is actually a lock on index records. When no suitable index is found, only the default clustered index can be used, and it will be locked in full.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download