Float Types Used as Primary Key on SQL 2005
in [Programming SQL]
|
From: RG on 21 Jul 2010 13:54 Please, don't get me wrong what you have said makes sense. I just wanted to rule out the possibility of default isolation level being the culprit. Thanks again "Bob Barrows" wrote: > 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 > > > . >
From: Erland Sommarskog on 21 Jul 2010 18:03 RG (RG(a)discussions.microsoft.com) writes: > Why, then, I am not able to recreate this problem with any number in the > vacinity? Are you saying that the problem appears only with the value 2002236411? The concurrency thing seesm to be OK, so I am more inclined to believe that it has something to do with your float/string combo. Although what I don't see.. -- 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 23:28 Well, not only with 2002236411, but this problem is very rare and there doesn't seem to be a pattern. "Erland Sommarskog" wrote: > RG (RG(a)discussions.microsoft.com) writes: > > Why, then, I am not able to recreate this problem with any number in the > > vacinity? > > Are you saying that the problem appears only with the value 2002236411? > > The concurrency thing seesm to be OK, so I am more inclined to believe that > it has something to do with your float/string combo. Although what I don't > see.. > > > -- > 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: Erland Sommarskog on 22 Jul 2010 05:50 RG (RG(a)discussions.microsoft.com) writes: > Well, not only with 2002236411, but this problem is very rare and there > doesn't seem to be a pattern. I'm afraid that I'm stumped with the information I have this far. But obviously, the value that is returned is used somewhere. And that is outside the transaction defined by the procedure you posted. While the proecedure is OK on its own, there may be a hole it the overall flow. -- 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: m on 22 Jul 2010 08:00
You can use SQL profiler to detect this but it is difficult because you must infer the lock objects from the cryptic identifiers from the trace under the default transaction isolation level (read committed), when your update statement executes it will acquire an update lock or an exclusive lock on the row, page or table (the choice depends on the query execution plan), perform the update, and then release the lock. When the select statement executes, it might read the values from the table that were set in the update statement for this transaction, or there might have been several more updates to the table in between. If your transaction isolation level is serializable or repeatable read, then when the update statement executes, it will retain a lock (shared or exclusive) on the accessed data. This lock will prevent any concurrent updates, but it will also limit your concurrency. The use of an identity column does not have this drawback because SQL provides a method to access the just inserted value without holding any locks The fact that SQL server will use "RG" <RG(a)discussions.microsoft.com> wrote in message news:23F38B1B-4261-45D0-80EA-ADC2BA4A4924(a)microsoft.com... > I am working with SQL 2000 and 2005. > > Please note, I am working with single row which keeps track of sequence > number. Once this row becomes dirty, shouldn't be locked for read/update? > > < If this is being executed concurrently with default isolation and lock > < options, then you could easily get duplicates (and gaps). > > > Is there a way I could prove this based on stored procedure I provided? > > > > Thanks again > > "m" wrote: > >> 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. >> >> > . >> >> > >> . >> |