Best way to get PK value of newly created record
-
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is (a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement. (b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
-
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is (a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement. (b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
-
Use an IDENTITY[^] column and read the latest identity using the SCOPE_IDENTITY[^] function.
Thank you responding. I will read through the documentation completely. But just at a first glance wouldn't this run into the issue of 2 clients inserting at the same time but then they both fetch the same serial number. To put it another way, client A should get 12345, client B should get 12346, but due to the timing of the inserts they both get 12346.
-
Thank you responding. I will read through the documentation completely. But just at a first glance wouldn't this run into the issue of 2 clients inserting at the same time but then they both fetch the same serial number. To put it another way, client A should get 12345, client B should get 12346, but due to the timing of the inserts they both get 12346.
a new connection implies a new scope, SCOPE_IDENTITY will not return identities created through other connections. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Thank you responding. I will read through the documentation completely. But just at a first glance wouldn't this run into the issue of 2 clients inserting at the same time but then they both fetch the same serial number. To put it another way, client A should get 12345, client B should get 12346, but due to the timing of the inserts they both get 12346.
As Luc has already answered on my behalf, SCOPE_IDENTITY() is scoped to your connection and ensures that you do not get a value that was inserted through another connection even if it were newer. There are other ways to read identity values though, but I suggested this method specifically for this reason.
-
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is (a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement. (b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
I strongly agree with getting that off the client. How to do so may depend on exactly what information is used to generate the serial number. I've seen some that use the date and a sequence, e.g. 120313123 (YYMMDDseq), with the sequence rolling over each day. Something like that could be done in the database, by a stored procedure. I understand that the upcoming version of SQL Server will have sequences built in (Oracle has had them for decades), but you could also create your own sequence (which is what I do when I need a sequence). On the other hand, you probably shouldn't put any "information" in the serial number in the first place. As to using identity columns... I don't; I find them to be very problematic, and a simple sequence works much better in most cases.
-
I strongly agree with getting that off the client. How to do so may depend on exactly what information is used to generate the serial number. I've seen some that use the date and a sequence, e.g. 120313123 (YYMMDDseq), with the sequence rolling over each day. Something like that could be done in the database, by a stored procedure. I understand that the upcoming version of SQL Server will have sequences built in (Oracle has had them for decades), but you could also create your own sequence (which is what I do when I need a sequence). On the other hand, you probably shouldn't put any "information" in the serial number in the first place. As to using identity columns... I don't; I find them to be very problematic, and a simple sequence works much better in most cases.
-
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is (a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement. (b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
As others have implied you need to change your strategy. Personally I would leave the serial number method exactly as it is but would not have it as the primary key. A primary key should hold no intelligence in it's data, so create another field (based on IDENTITY or GUID spit)) and use the serial no as a piece of clients code.
Never underestimate the power of human stupidity RAH
-
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is (a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement. (b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
First of all, separate the serial number and the primary key. If you use a Guid as the key, you can "calculate" it (
Guid.NewGuid()) already on the client. In case of an autoincrement value, you can query it from the database with aSELECT @@ID. Your serial number column should still have a unique index. When you insert your data, omit the serial number first. You can take two different approaches here: (1) Create a trigger. In the trigger function, calculate your serial number. Take care that two calculations may happen at the same time in different threads. The trigger function then updates your data. Retrieve the inserted serial number with aselectquery. (2) Calculate the serial number on the client. Do anupdatequery. When the update fails due to a duplicate value, calculate again with adjusted parameters, and try again, till the update does not fail. Here, I'd use a transaction, and commit when the update was succesful. Looks bad, but with a low chance of duplicates, it will not cause bad performance. -
PIEBALDconsult wrote:
As to using identity columns... I don't; I find them to be very problematic
I've been using them for over a decade and haven't really found any problems.
I have used them infrequently -- only in apps that someone else wrote -- and have always had trouble. Using a sequence or GUIDs has never caused me trouble.
-
PIEBALDconsult wrote:
As to using identity columns... I don't; I find them to be very problematic
I've been using them for over a decade and haven't really found any problems.
Shameel wrote:
I've been using them for over a decade and haven't really found any problems.
Neither have I. If I remember I will check my SQL 6.5 reference to see what I was using with that (since I have been using SQL Server longer than a decade.) * UPDATE * Yep 6.5 had them so I haven't had any trouble using them for 15 years.