10 pessoas ao mesmo tempo fazem um cadastro
De forma bem resumida, não será ao mesmo tempo. Mesmo que os requests cheguem juntos, será inserido um de cada vez. Mas hoje em dia os computadores estão tão rápidos que dá a impressão de que foram todos ao mesmo tempo. Não foram, alguém foi o primeiro, outro foi o segundo, e assim por diante. A diferença pode ser de milésimos de segundo ou até menos, mas não são simultâneos.
Quanto a não gerar ID's repetidos, geralmente usa-se uma sequence, que é um recurso gerenciado pelo próprio banco de dados: vc simplesmente pede o próximo valor e o banco garante que será único (novamente, porque não tem isso de "mais de um pedir o próximo valor ao mesmo tempo", cada um pede na sua vez). Ou vc pode usar UUID, pois a chance de encontrar um ID repetido é bem pequena.
Leitura complementar: