January 28, 2003 at 7:54 pm
Hello,
My problem is that I have a table that needs a sequential primary key without using an Identity column. I created the stored procedure below, but the problem is that even though I set the serializable on for the stored procedure the users still get duplicate keys. I also created the table far below to store requests. That table has a identity column to allow duplicate requests for primary keys to be stored. Interestingly engough, although two users may receive the same primary key, both user do not have row in this table with the same key.
Any help or suggestions are appreciated. I have thought about SET IMPLICIT_TRANSACTIONS ON but...
CREATE PROCedure dbo.pGetNextRegistrationNumber
@RegNumber numeric(10,0) out
AS
BEGIN
-- the Beginning
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin Transaction
select @RegNumber = RegistrationKey
from dbo.zMaster
with (HOLDLOCK)
update dbo.zMaster
set RegistrationKey = @RegNumber + 1
insert dbo.zRegistrationKeyRequest (RegistrationKey,
RequestorID,
Requested)
values (@RegNumber, suser_sname(), GetDate())
--commit
commit transaction
/**************************************/
CREATE TABLE [dbo].[zRegistrationKeyRequest] (
[RegistrationKey] [numeric](10, 0) NOT NULL ,
[RequestorID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Requested] [datetime] NOT NULL ,
[UniqueField] [numeric](8, 0) IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY]
January 29, 2003 at 12:26 am
I do not believe the HOLDLOCK is useful in this case. What usually happens is that developers use the HOLDLOCK command, thinking that they can temporarily prevent other users from reading the same data. What they do not realize is that HOLDLOCK only generates shared locks, not exclusive locks. Because the locks are shared, other users can still read the same data values.
Wrapping it in the transaction is the correct way to go in my opinion, but incrementing it with a variable allows it to be returned with either a failure or a success. I believe that your insert is failing, yet the variable is still being returned, hence the duplicates at the clients. Increment it in the insert statement, and read the value back from the table to return it, and you will fix your issue.
January 29, 2003 at 4:12 am
Instead of this
select @RegNumber = RegistrationKey
from dbo.zMaster
with (HOLDLOCK)
update dbo.zMaster
set RegistrationKey = @RegNumber + 1
do
update dbo.zMaster
set @RegNumber = RegistrationKey, RegistrationKey = RegistrationKey + 1
which you are allowed to do. What this means is the record is locked from being read or updated until the current process completes. You effectively set the variable and increase the value in a single step.
January 29, 2003 at 5:16 am
quote:
update dbo.zMasterset @RegNumber = RegistrationKey, RegistrationKey = RegistrationKey + 1
Antares,
Way cool! You should turn this little tidbit into an article. There are all sorts of forum threads regarding this issue and this is one of the most innovative solutions to this common problem I have seen. Well done.
Jay
January 29, 2003 at 9:04 am
Hi All,
thanks for the assistance. Any idea why the insert would be failing when the primary key is the identity field? And why would a stored procedure in a transaction allow other users to run the same stored procedure and both do a select on the table. This code works in other databases, but it seems that in SQL Server 2000 transaction do not mean much. I added the holdlock later even though the docs said it should not be needed and it actually reduced the number of occurences. [shrug] I do not know if I mentioned it but the duplicates only occur between 3 and 4 p.m. eastern.
Thanks,
Brandon
January 29, 2003 at 9:12 am
Just wanted to point a couple things out that you may want to look at before anything else regarding this procedure:
1) There is no error handling anywhere in the routine. How are you rolling back the transaction if something fails due to any number of errors (times out fue to deadlock, pk violation, etc.)?
2) The update of the registration key occurs BEFORE the insert of the old number into the RegistrationKeyRequest table. If the insert fails, you have incremented a registration number and left a gap in sequence. Is this important?
3) How is this proc being called? Depending on the application code, you may want to implement isolation level in the application logic versus the proc logic...
4) As a side note, is it necessary to have the --the Beginning and --commit comments?
January 29, 2003 at 2:55 pm
Cosidering the statement on gap if an insert fails I too wondered how to deal with that. The only thing I can think of is do a Table Lock during the insert with the Insert looking like this.
insert dbo.zRegistrationKeyRequest (RegistrationKey,
RequestorID,
Requested)
SELECT MAX(RegistrationKey) + 1, suser_sname(), GetDate() FROM dbo.zRegistrationKeyRequest
Then release the table lock. This should prevent gaps but will cause lag on a high transaction system.
January 29, 2003 at 4:15 pm
Im curious why you needed a sequential key but couldnt use ident?
Andy
January 29, 2003 at 7:52 pm
That's the way I deal with it, Antares. I have another set of statements which re-uses deleted keys as well, by selecting the first out of sequence number (left outer join to a derived temp table containing all possible values where there is no match), but hate using it, as it incurs a lot of overhead. I MUCH prefer the identity type for any of it, but even then, delete one, and the same thing occurs. It's seldom that I can't convince the manager that it's not worth the re-use. Generally, a skipped or missing key is not an issue, in my experience.
January 30, 2003 at 10:09 am
There are some reconciliation systems that "require" that every number be identified. Check reconciliation caused us a number of problems this way. Identity columns will use the value even if the transaction is rolled back. These are ugly no matter what. I prefer to start the transaction from the calling process (and understand the overhead of having the sequential number locked for the duration of that transaction).
I believe - but haven't tested - that changing the "holdlock" to "rowloc" would be closer to what you want. - Still, I like the solution Antares proposed more than any other method.
Guarddata-
January 30, 2003 at 11:27 am
Hi All,
Thanks for the replies.
The reason I cannot use an identity column is the customer says no to it as they need have no gaps between registration ids.
The problem with the audit table is that it was set up so that even if two users received the same id, I would be able to see that in the log and that is why it has an identity column for the primary key (not shown below). The interesting thing from my perspective is why if two users are running the stored procedure and get identical values for RegNumber I do not see both entries. RegNumber is the primary key of another table into which a user will insert a record.
I think the solution by Antares686 will solve the problem. Although I might add, that I have my concerns with using Server 2000 if I cannot set transactions in stored procedures and expect that all the work done there be atomic. The original sql worked for the orginal database Sybase ASE. I am new to working on Server 2000 so I am afraid that there are other gotchas like this there that I am not aware.
Thanks.
January 30, 2003 at 11:32 am
quote:
...I am afraid that there are other gotchas like this there that I am not aware.
Maybe it's just me, but I wouldn't call it a "gotcha". Proper error handling is required to catch mishaps and possible logic errors in ANY transaction in ANY language, be it SQL, C, VB... Code that runs without proper error handlers is code waiting to be a nasty adventure in debugging...
January 30, 2003 at 1:21 pm
You might also want to look at this topic from last August:
January 30, 2003 at 1:48 pm
This if for jpipes, STOP replying to this thead as obviously you are a MORON!!! Your comments so far have been inane and not to the topic at hand. Maybe you like to look through lines of excess code, but I do not and assume that other peoples time is valuable.
January 30, 2003 at 3:38 pm
BMStone,
Please refrain from flaming. If you have an issue that needs to be addressed, please contact me, Steve Jones, or Brian Knight. We work hard to keep the forums noise free so that all may benefit. Thanks.
Andy
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply