First let me state the asynchronous approaches:-
The user enters a character into the username form field. I create a connection to the database, use a prepared statement to confirm whether the username already exists, close the connection, and pass results back to initial page. AJAX is at work. All of this happens for every character the user enters or removes from the input field.
I pre-fetch the list of usernames from the database and store them locally. AJAX does username uniqueness checking from this locally stored pool on each character input, rather than from the database.
Now, the synchronous approaches:-
The user enters a username in input field. Clicks submit. I create a connection to the database, use a prepared statement to confirm whether the username already exists, close the connection, and pass results back to initial page. All of this happens every time the user clicks submit.
Basically the same as second approach in asynchronous section, but checking happens on clicking submit, rather than on inputting each character.
Now the second approaches work faster in both cases I believe, but they can lead to concurrency anomalies. Am I correct?
Also, while using a prepared statement to check username uniqueness from database in 1st approach in both categories, should I rely on the exception which would occur if I try to insert the same username (because of unique constraint) or should I use a select query to confirm the uniqueness? Basically is letting MySQL run into an exception considered bad practice?
If there is another better way of doing this, I'm all ears. Thanks!