Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbpatel46
Starting Member
1 Post |
 Posted - 2011-09-09 : 06:22:44
|
| We were using SQL Server 2000 as our backend. However, we have migrated to SQL Server 2008 R2 before 2 weeks.Due to this, we have been facing an issue of series jumping since migration. e.g. 1, 2, 3, 4, 5, 15, 7, 8, 9.....As seen above, 15 is a wrong series number which is creating an issue. Kindly suggest any solution for the same.I am using this type of code: if((select count(*) from btSeries where sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID) > 0) begin select @nSequence = nSequence from btSeries WITH (UPDLOCK) where sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID update btSeries set nSequence = nSequence + 1 where sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID select @nSequence endplease help me...Dharmesh Patelpatel.dharmesh46@gmail.com |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 06:37:22
|
"select @nSequence = nSequence from btSeries WITH (UPDLOCK) where sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID"Is there only ONE row that matches "sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID" ?If not the value you are getting in @nSequence will be random (and it is entirely possible that in SQL 2000 it always, or 99.9999%, read in clustered index order so your code was "safe".Maybeselect @nSequence = MAX(nSequence) from btSeries WITH (UPDLOCK) where sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID is what you need?There is also a race condition. You do this:if((select count(*) from btSeries where sPrefix = @sPrefix and nSeriesIDFK = @nSeriesID) > 0) but its possible that you get 0 but someone else has, at that very moment, also added one ... the whole thing should be in an isolated transaction I think. Its possible you are now seeing a side effect of that because SQL2008 is running more in parallel, or just faster, or even just differently, such that it never happened before but could do now. |
 |
|
|
|
|
|
|
|
SQL Server 2008 R2