Acquiring Table Lock in Database - Interview Question

Question

One of my interview Questions, if multiple users across the world are accessing the application, in which it uses a Table which has a Primary Key as Auto Increment Field.

The Question how can you prevent the other user getting the Same Primary key when the other user is executing?

My answer was I will obtain the Lock on the table and I will make the user to wait Until that user is released with the Primary key.

But the Question

1. How do you acquire the Table lock programmatically and implement this?
2. If there are 1000 users coming every minute to the application, if you explicity hold the lock on the table, then the application will become slower? How do you manage this?

Please suggest the possible answers for the above question

Answer 1

An autoincrementing primary key will never give the same value to two users as long as you use it properly. That is because a primary key by definition includes a uniqueness constraint.

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

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

If you use this method then you do not need locks. Using table locks will slow down your application and you should avoid it if possible.

Answer 2

In Java using JDBC for data access, you can call Statement.getGeneratedKeys() on the statement that you executed the INSERT on.

EDIT: I just re-read your question. With an auto-increment field, the database decides the value at the time of insert. It manages locking tables and ensuring uniqueness - one of the reasons using an identity column is much better than than hacking your own identity key management by using MAX(id)+1.

Answer 3

Following on from Marks post, in SQL Server you should use Scope_Identity() to retreive the last ID created within that session.

In addition, you can implement Isolation Levels to configure how SQL Server will read/write to tables during a transaction. Information on Isolation Levels can be found here