How can I prevent gaps in a MySQL table AUTO_INCREMENT field?
An auto increment column will always provide the highest available id for a new user in your case. 

However re-using missing numbers from failed transactions is not possible in a single step, since the primary key will probably have been used in other tables which will need to be updated. 

There are no performance issues associated with having missing numbers, and there are a common occurrence. This can only be an issue if you have more failed transactions than those which complete, however there I would recommend you set a larger size for your primary key.