CODEWITHSUNDEEP
c#databaseoracle-databaseprimary-key
vrbilgi
vrbilgi

Reputation: 5803

Getting correct auto incremented ID in Oracle DB

I am inserting one row into the ORACLE database table, where the primary key ID is autoincrement. and I am retriving the autoincremented ID from that TABLE.

In case of many people using the same TABLE. I may run into the consumer producer problem where I may get ID of other user.

Some of user on SO mentioned about getting these incremented ID is based on the connection which will not result in Consumer Producer problem:

Just want to check below things with SO:

First:

Will I get exact autoincremented ID from below sceneario:

1) Two computer with in different subnet using DB.
2) Two computer wiht in same subnet using DB.
3) Two process using using DB connection.
4) Two thread uisng DB connection.

Second:

 If my insert is:
 Insert into Tables (ID,NAME,DESCRIPTION) values (DBNull,'Name','Description');

 What should be my query to get the autoincremented ID generated for above
 Insert.

C# related command will do much help :)

Upvotes: 2

Views: 1684

Answers (2)

Stephanie Page
Stephanie Page

Reputation: 3893

First, there is no such creature as an "autoincremented" ID. Oracle has Sequences and triggers. One can use a trigger to select a sequence value upon insert. Oracle does not guarantee order in sequence generation, it doesn't guarantee the values have no gaps. It does guarantee you will get a unique value. So there's really no query which will tell you what the last sequence was added to the table. MAX may be right, but a lower number plucked from the sequence's buffer and delayed before inserting could show up after a higher number.

Craig has the best answer for the right way to do it. What's left out is a discussion of your four scenarios.

You need to read up on ACID.

ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.

Whereas your code may have concurrency issues (i.e. race conditions or not thread-safe) databases do not have such issues. (Not supposed to and usually don't) Good databases are designed from the ground up to meet the demands of ACID. Every transaction is Isolated from every other transaction. If you insert into that table and in the same transaction select Max(ID) and I insert into that table and select MAX(ID) as part of my one transaction, I will see the number I inserted and you will see the number you inserted. Any given transaction will show only the data that existed at the time the transaction started.

you can override this functionality by changing the Isolation Levels but ACID is a good thing. You want this. Oracle has worked exactly like this for a long time.

In poorly designed databases, the Isolation is done via locks. If you read a row and keep the transaction open, in order to make sure your next read is the same, the row you're asking for is locked. This means readers can block writers - a bad thing. In Oracle, since version 5 or 6, Oracle has been able to recreate a view of a the data in a table as it appeared at the start of your transaction via MultiVersion Concurrency Control. This has been the default behavior in Oracle for like 2 decades. Not every DB is so advanced... make sure you understand the isolation levels you're working within before doing ANYTHING in a database.

Upvotes: 2

Craig
Craig

Reputation: 5820

You should use the returning clause:

declare
    v_id TABLES.ID%TYPE;
begin
    Insert into Tables (ID,NAME,DESCRIPTION)
    values (seq.nextval,'Name','Description')
    RETURNING id INTO v_id;
--
    dbms_output.put_line(v_id);
end;

This way you get the ID that actually gets inserted instead of having to requery for the highest ID and hoping it is the right one.

Upvotes: 4

Related Questions