Acquiring Table Lock in Database - Interview Question
- by harigm
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
How do you acquire the Table lock programmatically and implement this?
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