Float Types Used as Primary Key on SQL 2005
in [Programming SQL]
|
From: RG on 19 Jul 2010 17:25 Could you give an example how one transaction could interfere with another in read committed isolation level? "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: Erland Sommarskog on 19 Jul 2010 18:03 RG (RG(a)discussions.microsoft.com) writes: > 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. Is this application the result of some worst-practices competition? Floats for ids, and then string in the application? :-) Anyway, the procedure looks good. But what are the values in the SEQTAB table? Are they in fact whole numbers? Or could there be decimals involved? Or are the numbers more than 15 digits long? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: --CELKO-- on 19 Jul 2010 20:19 Might be time to update the resume and start looking for another company ..
From: m on 20 Jul 2010 19:15 First, what version of SQL server are you using? More details make it easier for us to help you! If this is being executed concurrently with default isolation and lock options, then you could easily get duplicates (and gaps). By design, transactions guarantee atomicity of a set of operations but not exclusion on the resources they access. If this is your situation, you can try specifying the lock hint with(ROWLOCK, UPDLOCK) or with(SERIALIZABLE,TABLOCKX) on the update (depending on the scope of your sequence numbers within the table: look in BOL for more details), but a more robust and scalable solution would be to insert into a table with an int identity column and access SCOPE_IDENTITY. This solution also avoids the problem of adding the seed row to the SEQTAB table and it is easy to add a task to purge old rows. If necessary, you can cast the result to float and or update the existing SEQTAB table to maintain compatibility with your existing code. As others have mentioned, this design needs some serious help, but as Rome wasn't built in a day, I hope these suggestions will help "RG" <RG(a)discussions.microsoft.com> wrote in message news:52851C0E-1076-4D3F-807C-96A3E9E778B1(a)microsoft.com... > 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. >> > . >> >
From: Paul Shapiro on 20 Jul 2010 19:22
Two independent transactions can read the same existing data value as part of a READ COMMITTED update sequence, and therefore compute the same "unique" new value. Changing the transaction isolation to SERIALIZABLE should prevent the problem since it requires the transactions to function as if they were being processed in series. "RG" <RG(a)discussions.microsoft.com> wrote in message news:967E27DA-6C66-4F22-9F58-689DE66C57E0(a)microsoft.com... > Could you give an example how one transaction could interfere with another > in > read committed isolation level? > > "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. |