July 19, 2005 at 3:55 am
Hi people, I am puzzled by this:
I run a job with this in it and then record the count.
>
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT COUNT(*) FROM LEDGER_ENTRIES
Recently I scheduled a DBCC SHRINKDB(ODS_Finance) which (by bad luck or management) ran at the same time. I found the counts going haywire - counts changing when no process was updating the database.
Does anyone know if SHRINKDB will cause SELECT to read phantom records if it is running with lowest isolation level.
July 19, 2005 at 9:44 am
That's an interesting one, but I think I have a theory about what's going on.
The shrink operation would have to place locks and latches on the pages that it is re-arranging, and under normal circumstances it would either have to wait to gain exclusive locks or other transactions would wait until the shrink operation releases the lock on a particular resource. However, when you specify NOLOCK or READ UNCOMMITTED your SELECT statement does not acquire any locks and doesn't pay attention to any other locks. All of this doesn't fully explain what you are seeing though, so what I suspect is that the shrink operation, because it is IO intensive is acquiring a large number of latches. Now comes the part where I'm guessing... I don't believe that latches can be ignored like locks since they ensure the physical consistency of the page while it's being transfered from disk to memory. So if your SELECT ignores the lock but then is stopped by a latch that was acquired to support the transaction that acquired the lock this could explain the inconsistent results you are seeing.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 19, 2005 at 5:35 pm
Thanks dcpeterson,
The mechanism you describe sounds plausible.
Its something I really need to watch out for as I use READ UNCOMMITTED quite a bit for reporting.
I always thought the only way to get phantoms was from a user transaction, and so I only use this isolation level when there are no user update transactions or the report accuracy required can ignore currently executing user transactions.
Now Im learning that SHRINKDB (and maybe other DBCCs) may be a major source of instability.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply