Questions regarding Lock Objects.

[Former Member]

I am creating a custom table and I want to implement a unique ID for my table and I want to use the technique of auto-increment. I created a seperate table where to store the increments to be used as IDs. So basically, when a new data is going to be saved on my other table, the program will query the next "id" from the table for increments. I have to make sure that the updating and reading of the data from the table is accessed one at a time to rule out the possibility that there will be no duplicate IDs generated.

I have read about shared locks and exclusive locks which would serve as a solution for my purpose. The question is: is it possible for other users to "read" from a table if it is currently in an "exclusive lock" by another user?

[lbreddemann · Accepted solution]

>

> I am creating a custom table and I want to implement a unique ID for my table and I want to use the technique of auto-increment. I created a seperate table where to store the increments to be used as IDs. So basically, when a new data is going to be saved on my other table, the program will query the next "id" from the table for increments. I have to make sure that the updating and reading of the data from the table is accessed one at a time to rule out the possibility that there will be no duplicate IDs generated.
> 
> I have read about shared locks and exclusive locks which would serve as a solution for my purpose. The question is: is it possible for other users to "read" from a table if it is currently in an "exclusive lock" by another user?

No it isn't.

When you've selected your current value with SELECT FOR UPDATE (to get the exclusive lock) then every other reader will have to wait.

But is this really what you want?

Putting locks on something like an increasing number that is just used for an internal ID?

This will do nothing but turn your multiuser database application into a one-by-one slowness.

Instead, check the documentation about SEQUENCES.

These database objects were made for this. Lockfree increasing (or decreasing) numbers for unique ID columns.

Easy to use. Safe to use.

In 99.9% of all cases this is what you should use.

regards,

Lars