on 2013 Oct 25 11:08 AM
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
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You may also consider this related question with more discussion on how to get the newest ID:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
74 | |
30 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.