|
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 |
|
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 |
|
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:
That's even more reason to use that variable - it behaves smart:) |
|
You may also consider this related question with more discussion on how to get the newest ID: |
|
I'll often use |
Thank you very much. You've all answered my question.
Thanks again, Sam
"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.
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.