Acquiring table locks in a database - interview question

Question

One of my interviews. Questions, if several users all over the world turn to an application in which it uses a table with a primary key as an auto-increase field.

Question: how can you prevent another user from receiving the same primary key when another user is running?

My answer: I will get Lock on the table, and I will make the user wait until this user is released using the primary key.

But the question is

• How do you programmatically implement table locking and implement this?
• If 1000 users enter the application, if you find a lock on the table, will the application slow down? How do you do it?

Please suggest possible answers to the above question.

Answer 1

An auto-incrementing primary key will never give the same value to two users if you use it correctly. This is because the primary key, by definition, includes a unique constraint.

To use it correctly, first insert a row, and then enter the identifier of the last inserted row. There's a whole page about this in the MySQL documentation, for example. In MySQL, you can use LAST_INSERT_ID()or mysql_insert_id()to get the identifier of the last row inserted.

The bad method is to insert the row first and then query the table for MAX(id).

, . , , .

Answer 2

Java, JDBC , Statement.getGeneratedKeys() , INSERT.

EDIT: . auto-increment . - , MAX (id) +1.

Answer 3

Marks, SQL Server Scope_Identity() , .

, , , SQL Server / .