Hi,

I want to generate primary keys from my application. I don't want to use sequences nor identities nor generate_unique().

create table test( col1 int not null primary key )


Now, from the application I do (autocommit off, isolation level rs or better):


insert into test select coalesce(max(col1), 0) + 1 from test

select max(col1) from test into :id

commit


Now I believe to hold the id that I generated, even in szenarios with arbitrary concurrent access, i.e. not limited to the application.

Couple of questions:
a) Is it guaranteed that I will never fetch anybody else's id?
b) Is this approach guaranteed to be robust?
c) Is this a good portable approach (i.e. works alike in Oracle and SQL Server), or is there a better one?


I would love to first select the max, add 1 and have the new id, then insert and then commit, unfortunately, this is not robust because select the max will not prevent others to sneak up from behind and insert something before I get to do my insert. So I would have to do a lock table first, which again is something I don't want.

There is another point that makes me feel uncomfortable. How am I to know if DB2 places the locks first and then does the select coalesce(max(col1), ... etc.? If it selects the max first and then does the locking, again somebody else might sneak up from behind and do his insert.

Johann

I think this should work fine for 2-3 concurrent users and small table size. However, this solution will barely scale above that.

I think a better solution would be to have a separate table to store the current key value. This table would have only one row (per data table it serves). An application would lock a row, get current key value, increment it, then release the row. Normally the increment would be larger than 1; this would allow each application to reserve certain number of keys for its own use. By increasing the number of reserved keys you can easily increase concurrency of the "keys" table access, and the data table access doesn't suffer at all.

Hi,

I am talking about concurrent batch processing and small control tables.

Your suggestion is nevertheless a good one. But basically that means programming after sequences. I would prefer such a sequence to be tightly attached to the table, in much the same kind an identity is attached to a table.

On the other hand, I dislike identities, because you can have only one in a table - so they are not really data types - and this kind of grosses me out. Another point is, identities are not supported within partitioned databases, which is what we have over here, and I believe this holds even for single partition tablespaces.

Johann