Float Types Used as Primary Key on SQL 2005
in [Programming SQL]
|
From: Jeroen Mostert on 22 Jul 2010 14:18 On 2010-07-19 18:25, 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 You don't set the isolation level, so there's a possibility that the client code has set it to something else. To exclude this possibility, add SET TRANSACTION ISOLATION LEVEL READ COMMITTED before the BEGIN. Note that when executing from SMSS, the default is configurable in the options, but it again defaults to READ COMMITTED. It is very much possible to get different results in SMSS as compared to your client code (for this and other reasons). Otherwise, this sequence of statements is fine under COMMITTED. If you're still getting duplicates after explicitly setting the transaction level, then I'd recommend a profiler trace to see what statements are actually being executed when. It's still possible that a floating-point issue is tripping you up, but really only if something other than this procedure can modify values. Taking an exactly representable integer and adding 1 to it does not result in in inexact value, unless floating-point is seriously broken in SQL Server, which I should hope not. -- J.
From: Erland Sommarskog on 22 Jul 2010 16:47 m (m(a)b.c) writes: > 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. No. The UPDATE is in a transaction, and until the transaction is complete, there is an exclusive lock on the row, and no other process can modify the data. Or read it, with less than using dirty reads. -- 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: JG on 22 Jul 2010 18:08 Just want to add that we found a rogue app which is using a different methodology to generate the ID for new record INSERTS. It's simply doing a SELECT MAX from the base data table, rather than using the transactional ID pump. Obviously, this 'naive' approach is very unsafe. With this in mind, the round-up is that we no longer have any reason to suspect a breach of transactionality inherent in the transactional ID pump. Perhaps this proc could be streamlined in a couple of ways (as per suggestions baove) however the upshot of our analysis is that it should be sound as currently constructed. Once the base UPDATE (which is the first statement in the transaction batch) executes, then all other UPDATES to this particular row should block until this UPDATE either commits or rolls back. Thus, as long as all clients use this ID pump, pump executions should be serialized and every client should get a unique ID number. Is this not the case? Thanks to all who participated in this discussion. We appreciate your help! - JG - "RG" <RG(a)discussions.microsoft.com> wrote in message news:5F0CCC73-90D5-4F7B-9287-CF285518F37A(a)microsoft.com... > 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: m on 22 Jul 2010 19:22 That is only the case if the transaction isolation is repeatable read or serializable. It is explained opaquely by Microsoft here: http://msdn.microsoft.com/en-us/library/ms173763.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx But if you don't believe me then you can easily verify the behaviour by using SQL Profiler and tracing the lock acquire, lock release events during execution at various isolation levels and with various table hints and with different keys. These are called phantoms and this is a classic concurrency programming mistake. This is yet another reason to reduce procedural code in SQL as much as possible and for the OP the best solution will still be to use a table with an identity column and scope_identity to allocate his id values "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DBDE7ECAF60Yazorman(a)127.0.0.1... > m (m(a)b.c) writes: >> 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. > > No. The UPDATE is in a transaction, and until the transaction is > complete, there is an exclusive lock on the row, and no other process > can modify the data. Or read it, with less than using dirty reads. > > > -- > 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 23 Jul 2010 09:24
m (m(a)b.c) writes: > That is only the case if the transaction isolation is repeatable read or > serializable. It is explained opaquely by Microsoft here: No. Do this as a setup: CREATE TABLE alfons (a int NOT NULL PRIMARY KEY NONCLUSTERED, b sysname) INSERT alfons(a,b) SELECT object_id, name FROM sys.objects Then do this in one query window: BEGIN TRANSACTION UPDATE alfons SET b = upper(b) WHERE object_id = 3 Then try to modify or read alfons.b from a different query window. What you are saying is correct if the first statement of the transaction was a SELECT statement, but it isn't. -- 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 |