Prev: Optional IN Clause parameter
Next: Need help writing stored procedure
From: RG on 19 Jul 2010 10:19
We seem to have hit an oddity.

We have a stored procedure that gets the next sequence number. It is
protected by a transaction so no two processes could update the table of ids
at the same time. Every so often, we get duplicates. How could that happen?
Is the value based on mantissa? If so, is there a way to expanded it?

Keep in mind, I can't change the type from float.

Any help is greatly appreciated.

Thanks in advance

From: --CELKO-- on 19 Jul 2010 10:39
>>  Every so often, we get duplicates.  How could that happen?  Is the value based on mantissa?  If so, is there a way to expanded it? Keep in mind, I can't change the type from float. <<

I would also have guessed floating point rounding errors, but you did
not post any code. Two floats are "equal" if they fall within an
epsilon. Math packages have corrections for this kind of problem but
SQL does not. This is why you never use float for an identifier -- it
is not discrete. Are you sure that you cannot go to an exact numeric
type?
From: Jeroen Mostert on 19 Jul 2010 10:55
On 2010-07-19 16:19, RG wrote:
> We have a stored procedure that gets the next sequence number. It is
> protected by a transaction so no two processes could update the table of ids
> at the same time.

You'll need to post code. Transactions can be used to achieve atomicity, but
simply using a transaction does not guarantee two statements will not
interfere in some fashion, especially not if you don't change the
transaction level from the default (READ COMMITTED).

> Every so often, we get duplicates. How could that happen?
> Is the value based on mantissa? If so, is there a way to expanded it?
>
If you are trying to determine if "the same" key exists with an equality
comparison, you may hit a rounding error. However, if all you're doing is
incrementing the key by 1 (and comparing with existing values generated in
the same way), this is unlikely, because the default FLOAT type (without a
specified precision) can store any integer up to and including 2^53 exactly.

This is also why posting code is important. Your problem *may* have to do
with floating-point arithmetic, but it's just as likely it doesn't.

--
J.
From: RG on 19 Jul 2010 12:25
Below is the code. Please, note, I realize all of this could have been
written with one if statement and update statement in compare clause. I just
don't see why this is not working.

ALTER PROCEDURE [dbo].[spGetSeq]
@IDOBJ VARCHAR(16),
@NEXTSEQ FLOAT OUTPUT,
@Err INT OUTPUT
AS
BEGIN

SET NOCOUNT ON;

BEGIN TRANSACTION
UPDATE SEQTAB SET CSEQ = CSEQ + 1 WHERE IDOBJ = @IDOBJ;
SELECT @Err = @@ERROR

IF @Err <> 0
BEGIN
ROLLBACK TRANSACTION
SET @NEXTSEQ = 0
RETURN
END

SET @NEXTSEQ = (SELECT CSEQ FROM SEQTAB WHERE IDOBJ = @IDOBJ);
SELECT @Err = @@ERROR

IF @Err <> 0
BEGIN
ROLLBACK TRANSACTION
SET @NEXTSEQ = 0
RETURN
END

COMMIT TRANSACTION
SET @Err = 0
RETURN

END

"Jeroen Mostert" wrote:

> On 2010-07-19 16:19, RG wrote:
> > We have a stored procedure that gets the next sequence number. It is
> > protected by a transaction so no two processes could update the table of ids
> > at the same time.
>
> You'll need to post code. Transactions can be used to achieve atomicity, but
> simply using a transaction does not guarantee two statements will not
> interfere in some fashion, especially not if you don't change the
> transaction level from the default (READ COMMITTED).
>
> > Every so often, we get duplicates. How could that happen?
> > Is the value based on mantissa? If so, is there a way to expanded it?
> >
> If you are trying to determine if "the same" key exists with an equality
> comparison, you may hit a rounding error. However, if all you're doing is
> incrementing the key by 1 (and comparing with existing values generated in
> the same way), this is unlikely, because the default FLOAT type (without a
> specified precision) can store any integer up to and including 2^53 exactly.
>
> This is also why posting code is important. Your problem *may* have to do
> with floating-point arithmetic, but it's just as likely it doesn't.
>
> --
> J.
> .
>
From: RG on 19 Jul 2010 13:17
There is one more wrinkle here. This stored procedure is being called from
vb6 program. In vb, the sequence number is of string type. So, conversion
is taking place.

"RG" wrote:

> Below is the code. Please, note, I realize all of this could have been
> written with one if statement and update statement in compare clause. I just
> don't see why this is not working.
>
> ALTER PROCEDURE [dbo].[spGetSeq]
> @IDOBJ VARCHAR(16),
> @NEXTSEQ FLOAT OUTPUT,
> @Err INT OUTPUT
> AS
> BEGIN
>
> SET NOCOUNT ON;
>
> BEGIN TRANSACTION
> UPDATE SEQTAB SET CSEQ = CSEQ + 1 WHERE IDOBJ = @IDOBJ;
> SELECT @Err = @@ERROR
>
> IF @Err <> 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @NEXTSEQ = 0
> RETURN
> END
>
> SET @NEXTSEQ = (SELECT CSEQ FROM SEQTAB WHERE IDOBJ = @IDOBJ);
> SELECT @Err = @@ERROR
>
> IF @Err <> 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @NEXTSEQ = 0
> RETURN
> END
>
> COMMIT TRANSACTION
> SET @Err = 0
> RETURN
>
> END
>
> "Jeroen Mostert" wrote:
>
> > On 2010-07-19 16:19, RG wrote:
> > > We have a stored procedure that gets the next sequence number. It is
> > > protected by a transaction so no two processes could update the table of ids
> > > at the same time.
> >
> > You'll need to post code. Transactions can be used to achieve atomicity, but
> > simply using a transaction does not guarantee two statements will not
> > interfere in some fashion, especially not if you don't change the
> > transaction level from the default (READ COMMITTED).
> >
> > > Every so often, we get duplicates. How could that happen?
> > > Is the value based on mantissa? If so, is there a way to expanded it?
> > >
> > If you are trying to determine if "the same" key exists with an equality
> > comparison, you may hit a rounding error. However, if all you're doing is
> > incrementing the key by 1 (and comparing with existing values generated in
> > the same way), this is unlikely, because the default FLOAT type (without a
> > specified precision) can store any integer up to and including 2^53 exactly.
> >
> > This is also why posting code is important. Your problem *may* have to do
> > with floating-point arithmetic, but it's just as likely it doesn't.
> >
> > --
> > J.
> > .
> >
 |  Next  |  Last
Pages: 1 2 3 4 5 6
Prev: Optional IN Clause parameter
Next: Need help writing stored procedure