Sequence vs max primary key value + 1

Question

When inserting a new record in a table, there can be two approaches, one is to find the max value of primary key column and adding 1 to it or use a sequence. Which approach is better and why?

What is the impact if we find the next primary key value by incrementing the max value of the primary key column in a table having less than 20,000 records?

Thanks.

Answer 1

Q: Which approach is better and why?

A: Using a SEQUENCE object is a better approach.

The MAX(id)+1 approach to obtaining a unique id value is broken in a multi threaded environment, absent concurrency killing locking. This problem will not be exposed in single user testing; but it's very easy to demonstrate this using two different sessions. Consider this sequence of operations:

session 1: SELECT MAX(id)+1 AS next_id FROM mytable --> 42

session 2: SELECT MAX(id)+1 AS next_id FROM mytable --> 42

session 1: INSERT INTO mytable (id) VALUES (42)

session 2: INSERT INTO mytable (id) VALUES (42)

To prevent two (or more) separate sessions from returning the same next_id value, it would be necessary for a session to obtain an exclusive lock on the table before it executed the query. It would also need to hold that lock, until after a row with that next_id value was inserted to the table, before releasing the lock. While that session holds the exclusive lock on the table, no other session can query or insert into the table, other sessions will block if they attempt to. (We don't want to kill database performance by introducing this kind of locking, that's not the right approach, so we're not going to demonstrate how that would be done.)

Oracle provides the SEQUENCE object as an efficient means for obtaining a unique value, and queries against SEQUENCE objects are "safe" in a multi-threaded environment. For performance under a high load, we'd increase the "cache" value of the sequence, the number of values that are available in memory, so we can satisfy more NEXTVAL requests without requiring a write to the redo log.

Given those two options, the SEQUENCE is the better approach.

---

Q: What is the impact if we find the next primary key value by incrementing the max value of the primary key column in a table having less than 20,000 records?

A: A query that retrieves the maximum value of an indexed column (a column that is the leading column in an index), and adding one to it, should be very efficient, as long as the session is able to obtain the required share lock (that is, the session isn't blocked by an exclusive lock.)