Default value for primary key + 1

When inserting a new record into a table there can be two approaches, one is to find the maximum value of the primary key column and add 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 increasing the maximum value of the primary key column in a table with fewer than 20,000 records?

Thank.

+3


source to share


1 answer


Q: Which approach is better and why?

A: Using an object SEQUENCE

is the best approach.

The approach MAX(id)+1

for getting a unique id value breaks down in a multithreaded environment, with no concurrency blocking. This issue will not show up in single user testing; but it is 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 value next_id

, the session must acquire an exclusive lock on the table before executing the query. You will also need to hold this lock until after a row with this value has next_id

been inserted into the table, before releasing the lock. Although this session contains an exclusive lock on the table, no other session can query or insert into the table, other sessions will block if they try. (We don't want to kill database performance by introducing such a lock, not the correct approach, so we are not going to demonstrate how this would be done.)

Oracle provides an object SEQUENCE

as an efficient means of obtaining a unique value, and queries on SEQUENCE objects are "safe" in a multithreaded environment. For high load performance, we increased the sequence cache, the number of values ​​available in memory, so that we can serve more NEXTVAL requests without having to write to the redo log.

Considering these two parameters, this SEQUENCE

is the best approach.


Q: What is the impact if we find the next primary key value by increasing the maximum value of the primary key column in a table with fewer than 20,000 records?

A: A query that retrieves the maximum value of an indexed column (the column that is the leading column in the index) and adds it to it should be very efficient as long as the session can acquire the required shared lock (that is, the session is not locked by an exclusive lock.)

+12


source







All Articles