Front end Concurrency considerations on autoincrement columns

[former_SQLA_member1694902]

This is related to another questions I asked about making up for some legacy code. I want to make sure I've got the right approach to AUTOINCREMENT and am using it in the right way. Let says I have these tables, for which I will given some kind of award for ingenuity:

ParentTable (
  ParentId INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,
  ParentName CHAR(30))

ChildTable (
  ChildId INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,
  ParentId INTEGER,
  ChildName CHAR(30))

I have some hypothetical code that is writing parents and children into these records from dozens of workstations all day, every day. My front end application's table definition contains a space for some code that executes "After insert" to fill the record buffer with the Identity column.

The suggestion in the documentation is to use

SELECT MAX(ParentId) FROM ParentTable

But after a suggestion on this forum I've been experimenting with

SELECT @@Identity

Anyway, my question is this: If two inserts are happening at the "same" time, what happens with that code? What if things happen in the following order:

Connection A: INSERT INTO ParentTable (ParentName) VALUES('ParentA')
  //receives a ParentId of 1
Connection B: INSERT INTO ParentTable (ParentName) VALUES('ParentB')
  //receives a parentId of 2
Connection A: SELECT MAX(ParentId) FROM ParentTable
  //returns "2" so the record buffer and the record on disk are now out of sync
Connection B: SELECT MAX(ParentId) FROM ParentTable
  //return "2" so they record buffer is in sync with the Db

Now when ParentA adds child records they will be attached to ParentB. Am I correct that this could occur? i.e will they be able to do that or does the SELECT always happen before another record can be inserted? Is there a different result if I use

SELECT @@Identity ?

If I'm completely wrong about this, please tell me. The documentation doesn't say anything specific about concurrency, also if I should be asking this on the Clarion (The language I write in) forums instead of here, I will.

I'm using 11.0.1.2661

Thanks in advance

[former_SQLA_member1694902]

Thank you very much. You've all answered my question.

Thanks again, Sam

[Breck_Carter]

"The suggestion in the documentation is to use SELECT MAX(ParentId) FROM ParentTable"

Where exactly did you find that suggestion? It needs to be removed.

[VolkerBarth]

One further disadvantage of the SELECT MAX() approach should be mentioned:

It allows the re-use of primary keys in case the row with the highest PK value is deleted - the next entered row will then get the same PK value again. In many situations (and particularly in distributed/replicated databases) that is a no-go.

In contrast, DEFAULT AUTOINCREMENTs (and the get_identity() function that works similar) do not re-use values.

[Former Member · Accepted solution]

If you use select @@identity sql anywhere returns the correct value for you connection, doesn't share @@identity value between connections. I don't think SELECT max() works the same, here you can have several problems. Bye

[VolkerBarth]

Yes, doing a SELECT MAX(ParentID) could allow a connection to get a value that another connection has entered before. You should not use that approach, it's apparently error-prone.

In contrast, @@identity is a connection-specific variable, and as such, will return different values for different connections. When your connection has just entered a row in a table with a DEFAULT AUTOINCREMENT column, querying "SELECT @@Identity" afterwards will just return that value, so different connections do not disturb each other here. That's the recommended approach (if you do not use get_identity() to get a new value beforehand).

Note the following quote from the docs:

@@identity and triggers

When an insert causes referential integrity actions or fires a trigger, @@identity behaves like a stack. For example, if an insert into a table T1 (with an IDENTITY or AUTOINCREMENT column) fires a trigger that inserts a row into table T2 (also with an IDENTITY or AUTOINCREMENT column), then the value returned to the application or procedure which carried out the insert is the value inserted into T1. Within the trigger, @@identity has the T1 value before the insert into T2 and the T2 value after. The trigger can copy the values to local variables if it needs to access both.

That's even more reason to use that variable - it behaves smart:)

[VolkerBarth]

You may also consider this related question with more discussion on how to get the newest ID:

• How to get auto-increment field value as soon as a new row inserted?

[former_SQLA_member1694903]

I'll often use get_identity('table_name',RecCount). This lets me get 1 or more unique record id's for a table in a pre-allocated fashion. I can then build/insert the records with the allocated id numbers.