I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it.
Let's say I have a table like this:
CREATE TABLE [dbo].[SomeTable]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [SomeData] [varchar](200) NOT NULL, [Moment] [datetime] NOT NULL, [SomeInt] [bigint] NOT NULL ) ON [PRIMARY]
and I run this "insert 1000 rows within a transaction" query:
BEGIN TRAN t1 DECLARE @i INT = 0 WHILE @i < 1000 BEGIN SET @i = @i + 1 INSERT INTO [SomeTable] ([SomeData] ,Moment, SomeInt) VALUES (CONVERT(VARCHAR(255), NEWID()), getdate(), @i) WAITFOR DELAY '00:00:00:010' END COMMIT TRAN t1
While this transaction is running, I'm executing a simple select:
SELECT Id, Moment, SomeData, SomeInt FROM [SomeTable]
It isn't always possible to reproduce it (apparently depends on timings) but sometimes select query will, after insert transaction finishes, return less than 1000 rows. In my ignorance I have believed that select will always return 1000 rows (given that isolation level is Read Committed), but obviously I have misunderstood how transactions and locking work.
However, if I put a primary key on Id column (which generates a clustered index), select query will, as long as I've tried, return all 1000 rows. Putting indexes some other way, with clustered index on composite key and a non-clustered index on some other columns, may again result in returning less number of rows than I've expected.
So, I have these questions:
- Why select doesn't always return all rows committed by transaction?
- If this is expected behavior what's the best way of actually making it work as I have expected? Basically, I want select to return the state of the table after (or before) the transaction, not some half-done data. Snapshot isolation is currently not an option. Putting TABLOCK seems to be doing the work, but is there a better solution? In real life I have tables that I wouldn't want to lock on this level if it is not absolutely necessary.
- Why putting an index changes this behavior?
Thanks in advance.