What is the lowest safe isolation level for a insert with a subselect on the primary key

Asked Nov 06 '14 at 15:22

Active Nov 10 '14 at 14:59

Viewed 93 times

Given the following SQL statement:

INSERT INTO my_table VALUES ((SELECT MAX(id) + 1 my_table), {omitted})

And assuming id is the only primary key for my_table.

What is the lowest possible isolation level that would guarantee no duplicate key exception could occur when executing the above statement?

DB2 Isolation levels (ANSI SQL equivalent)

Repeatable read (Serializable)
Read Stability (Repeatable read)
Cursor Stability (Read committed)
Uncommitted Read (Read uncommitted)

Feel free to use either the DB2 or ANSI SQL equivalent in your answer, but please specify which one you're using. We're using DB2, but I'll accept a more generic answer if one is possible.

edited Nov 10 '14 at 14:59

asked Nov 06 '14 at 15:22

jColeson

1

If you use sequence, you don't have to think about this problem. – Luc M Nov 06 '14 at 15:23
...also known as *auto_increment* – fancyPants Nov 06 '14 at 15:45
That's true, but we're unable to use auto_increment. I simplified the example considerably so that it would be more useful to other people. – jColeson Nov 06 '14 at 16:07
@fancyPants auto_increment (i.e. an identity, which is tied to a specific table) is not the same thing as a sequence (which is independent of a table). – Ian Bjorhovde Nov 10 '14 at 16:26

What is the lowest safe isolation level for a insert with a subselect on the primary key

0 Answers0