1

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

2
  • 2
    If there's a difference reading committed and uncommitted data, there is an ongoing transaction Aug 8, 2019 at 6:37
  • 1
    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. Aug 10, 2019 at 18:38

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.