Last post May 03, 2011 03:26 PM by abhisheks
Apr 28, 2011 02:49 PM|narenderrawal007|LINK
I just met a strange problem. I have a table with with primary key having autoIncrement by 1. I am only inserting the data in to this table (no deletion) from an asp.net form. I noticed that while data is inserted in to this table autoIncrement column has
the values 1,2,3,4,6,,8,9,12,13,16,17,18,22 where as it should have been a simple values of autoincrement by 1 like 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15... . The problem that I think is in the insertion code but I am using SQLtransaction to make sure everything
happens in entirety. I have no idea why those rows are missing and where they are gone ?
Is something like that even possible. If yes, then what could be the reaason ?
Apr 28, 2011 03:35 PM|abhisheks|LINK
possibility is since you are using sql transaction, when your transaction fails, the deletion happens in the background. So I would start on your transaction call to make sure, there is no error.
If you are seeing something like this 1,2,3,4,6,,8,9, means when you were expecting 7, transaction failed somewhere during insert on 7, and since your are probably rolling back, 7 is already being used, so on next insert it will use 8.
I will start by taking that transaction part out and see if you can repeat it without transaction.
Apr 29, 2011 06:05 AM|narenderrawal007|LINK
Thanks for reply Abhishek. Well you may be right But are not the Transactions made to rollback when an insert has failed on one of the queries and and they are not suppossedly test all queries first and then insert every record. Or It just tries to insert
query and if anything fails then it deletes all the queries. If this is the case then sure it can happen. But weird though !
Any pointers please where I can know about this behaviours ?
Apr 30, 2011 02:14 AM|Decker Dong - MSFT|LINK
I think if you fail on inserting. Though the data won't be inserted into datatable. However the autoincreament key will be added.
This is the problem....
May 02, 2011 07:30 AM|narenderrawal007|LINK
Is this expected behaviour of SqlTransaction ? I mean I expected that if insertion fails then it won't insert the data in any of the tables and also will keep the increment key intact !
Any thoughts ?
May 03, 2011 03:26 PM|abhisheks|LINK
This is the expected behaviour of Sql transaction mainly for perfomance reason. If increment key is rolled back, then the value of next 'increment' value would have to be locked for the duration of the sql transaction, so to avoid the lock on next 'increment'
value, increment key is not rolled back.