Float Types Used as Primary Key on SQL 2005
in [Programming SQL]
|
From: RG on 21 Jul 2010 02:11 Well, tried to test this out by slightly modifying stored procedure and inserting waitfor statement. Shortly after starting executing the first instance of this stored procedure, I started executing the second instance of this stored procedure. The second instance of stored procedure appears to have waited for the completeion of the firtst instance. Please note, in both instances of the stored procedure we are updating the same row. Hence, the row we are reading is dirty as select comes after the update. So, I am not sure what you mean when you say "can read the same existing data value as part of a READ COMMITTED update sequence". If you could give a scenario that I could test, I would greatly appreciate it. Thanks again "Paul Shapiro" wrote: > 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. > > . >
From: RG on 21 Jul 2010 02:11 The value of the sequence is 2002236411 Thanks again "Erland Sommarskog" wrote: > 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: Bob Barrows on 21 Jul 2010 09:12 Given that it's float, that might not actually be the value stored. When stored to disk and then retrieved, there could be a non-zero mantissa if it is impossible to create a binary representation of the number. That is what CELKO was referring to when he taked about an epsilon comparison. RG wrote: > The value of the sequence is 2002236411 > > Thanks again > > "Erland Sommarskog" wrote: > >> 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: RG on 21 Jul 2010 10:09 Why, then, I am not able to recreate this problem with any number in the vacinity? "Bob Barrows" wrote: > Given that it's float, that might not actually be the value stored. When > stored to disk and then retrieved, there could be a non-zero mantissa if it > is impossible to create a binary representation of the number. That is what > CELKO was referring to when he taked about an epsilon comparison. > > RG wrote: > > The value of the sequence is 2002236411 > > > > Thanks again > > > > "Erland Sommarskog" wrote: > > > >> 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: Bob Barrows on 21 Jul 2010 10:42
I'm not sure. I was offering information, not trying to explain the actual cause of your symptoms. I have not seen a way to consistently reproduce the symptom, so unfortunately, it is unlikely you will receive a solution here ... all we can do is guess. Hopefully I'm wrong ... RG wrote: > Why, then, I am not able to recreate this problem with any number in > the vacinity? > > "Bob Barrows" wrote: > >> Given that it's float, that might not actually be the value stored. >> When stored to disk and then retrieved, there could be a non-zero >> mantissa if it is impossible to create a binary representation of >> the number. That is what CELKO was referring to when he taked about >> an epsilon comparison. >> >> RG wrote: >>> The value of the sequence is 2002236411 >>> >>> Thanks again >>> >>> "Erland Sommarskog" wrote: >>> >>>> 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 >>>> >>>> . >> >> >> . -- HTH, Bob Barrows |