Rollback questions and skipped number sequence

[esu4edp]

Can someone please explain if an SQL database would have to rollback for some reason, does it roll back all the data or just some of the data in certain tables? Can it do both?

I have a database and a certain table is missing entries. There is a field in the table called RECID that basically just adds one to each entry: For example when the users starts a new log it gives it #5 and the next entry is #6 and so on. Users have said they made entries but the data is missing and there is a skip in numbers. For example: #10 and then the next one is #22. There has been an issue with progressive lockups related to this issue.

 

[SQLDenis]

rollbacks do not reseed the table, numbers will be skipped as you can see from this example

create table Rollbacktest (id int identity)

insert Rollbacktest default values

select * from Rollbacktest --1

begin tran

insert Rollbacktest default values
insert Rollbacktest default values
insert Rollbacktest default values
insert Rollbacktest default values
insert Rollbacktest default values
insert Rollbacktest default values
rollback work


insert Rollbacktest default values

select * from Rollbacktest --1 and 8

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[esu4edp]

Sure, but will that table be the only one thats effected. For example if I'm missing records from that table, will other tables in my database (483 tables in my database) be missing records.

 

[jmeckley]

also transactions are not started automatically. this needs to be explicitly called (and committed) in the code or stored proc.

if a routine modifies data across 2 or more tables without a transaction and the routine fails all changes before the failure are perminent. any modifications after the failure are not executed.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[SQLSister]

Having a table that adds one to each entry and then expecting to have entries with no gaps is a bad practice. Use an identity column instead and accept that there are going to be gaps. Possible part of the problem is that two or more users got the same number and then there was an error inserting the record. This design is a recipe for disaster.

Questions about posting. See faq183-874