SQL server - data successfully inserted in a table but not returned in Select Statement

Question

SQL select query not returning records, even though the data insert operation is successful.

We have a table [dbo].[Login] in our database where data is inserted using the below stored procedure. The data is inserted by a windows service written in dot net. It has been working until last month and the records in the table were growing several 100 thousands. Recently, customer complained that the data in not reflected in the reports. Hence, we have investigated and found that, there is no insertion error from the stored procedure, but when we select the table no new records have been returned. We then tried setting the SQL transaction isolation level to Read un-Committed then we are able to see the newly inserted records from the table. We have reviewed the insertion stored procedure but didn't find anything suspicious.

CREATE PROCEDURE [dbo].[gp_Login_Insert] 
( 
 @ID int OUTPUT, /*-- the automatically generated value will be returned as an output parameter*/ 
 @ReaderHardWareID int, 
 @CardID int, 
 @EmpID int, 
 @UEmpID varchar(50), 
 @Type varchar(15), 
 @DateTime datetime, 
 @CreatedByEmployeeId int, 
 @CreatedDate datetime, 
 @ModifiedByEmployeeId int, 
 @ModifiedDate datetime, 
 @version_ts timestamp 
 ) 
 AS /* ** Add a row to the Login table */ 
 if not exists(select 1 from Login where EmpID=@EmpID and DateTime=@DateTime) 
 begin 
 declare @eventCount int
 select @eventCount=count(*) from [login] 
 where EmpID=@EmpID and [DateTime] between DATEADD(dd, 0, DATEDIFF(dd, 0, @DateTime)) and @DateTime
 if(@eventCount<500)
 Begin
 BEGIN TRANSACTION; 
 BEGIN TRY 
 INSERT INTO [Login] 
 ( [ReaderHardWareID], [CardID], [EmpID],[UEmpID],[Type], [DateTime], [CreatedByEmployeeId], [CreatedDate], [ModifiedByEmployeeId], [ModifiedDate] ) 
 VALUES 
 ( @ReaderHardWareID, @CardID, @EmpID,@UEmpID,@Type, @DateTime, @CreatedByEmployeeId, GETUTCDATE(), @ModifiedByEmployeeId, GETUTCDATE() ) 
 SET @ID = (SELECT SCOPE_IDENTITY()) 
 END TRY 
 BEGIN CATCH 
 SELECT 
 ERROR_NUMBER()
 IF @@TRANCOUNT > 0 
 ROLLBACK TRANSACTION; 
 END CATCH; 
 IF @@TRANCOUNT > 0 
 COMMIT TRANSACTION; 
 End
 END

Select Query on table returns the following results:

select transaction isolation level read uncommitted
select COUNT(*) from Login where CalFlag is null

returns 59341

select transaction isolation level read committed
select COUNT(*) from Login where CalFlag is null

returns 0

Expected Result: Since SQL server default transaction isolation is Read Committed it should return the correct row count 59341 in Read Committed mode. But it is not doing so.

SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22>2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit)>on Windows 10 Pro 10.0 (Build 18362: )

Thanks in advance, Ujjal Sarmah

• Lennart - Slava Ukraini: If there's a difference reading committed and uncommitted data, there is an ongoing transaction
• Randolph West: This is an excellent example why you should not read uncommitted data. If you want to read data outside of that transaction, you need to make sure it is committed first.

Answer 1

If read uncommitted returns rows but read committed does not that rather suggests that you have a number of open transactions. You can look in sys.dm_tran_active_transactions, especially column transaction_begin_time. If there's anything suspicious there I think you have your culprit.

The procedure itself seems innocent. That would suggest another transaction is opened further up the stack perhaps in a parent procedure, through the application or a server setting. SQL Server support multiple nested BEGIN TRANSACTION / COMMIT statements. A single commit will not persist all the work done by all nested BEGIN TRANSACTION statements. Quote:

When @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

If you have open, uncommited transactions I can't think what the solution could be. If the connection were severed those transaction would roll back and the data lost. If the instance were restarted those transactions would roll back and the data lost. The uncommitted values will not be in a backup file. Hopefully you have a source external to SQL Server from which they can be salvaged.