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
LOCK IN SHARE MODE
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.