how sequence maintain concurrency in oracle [closed]

Question

what happened, if sequence has been access at the same time (even in milliseconds) by multiple users. Whether it will be dead lock or Oracle have some mechanism ?

I mean, If A and B users has requested for nextval from squence1 at same time, then they will get same value or they will hanged.

According to this blog, it says

Concurrent access of NextVal on a sequence will always return separate values to each caller. (Unless the sequence has been setup to cycle values - in which case duplicate values are possible).

Answer 1

Jonathan Lewis covers this in his book Oracle Core (pp.222-225). I'm not going to recapitulate everything, you should buy the book (find it here). The important thing is that he not only explains what happens, he shows us how we can see what's happening (by using SQL trace).

Basically, each sequence is just a row in the core table seq$. The first call to nextval issues an update to that sequence's entry. This increments the sequence's highwater mark to starting value + cache value. The database tracks the current value (starting value + number of calls to nextval) in the SGA; when that number reaches highwater mark the database updates seq$ again.

The important thing to note is that all this managed in the SGA and not the individual session. The only thing the individual session tracks is the result of the most recent call to nextval so it can satisfy subsequent calls to currval.