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
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 18.104.22.16861
Thanks in advance
asked 25 Oct '13, 10:08
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
answered 25 Oct '13, 10:16
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:
answered 25 Oct '13, 11:02
I'll often use
answered 31 Oct '13, 07:55