June 8, 2011 at 4:23 am
I have a query like this
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WITH Customers AS (
SELECT * FROM Customer ),
SalesTotal AS ( SELECT CustomerId, SUM(OrderTotal) AS AllOrderTotal FROM
SalesOrder)
SELECT CustomerId, AllOrderTotal
FROM SalesTotal
WHERE AllOrderTotal > 10000.00;
Can anyone explain to me if other queries will be blocked by this query? And if this query also will be blocked by other queries? Explaination with details would be much appreciated!!:-)
June 8, 2011 at 5:08 am
Can anyone explain to me if other queries will be blocked by this query? And if this query also will be blocked by other queries? Explaination with details would be much appreciated!!:-)
There will be no blocking as long all the transactions are reading the same range of data but if transaction doing any update on the range mentioned data then it will not allow the transaction to process as in this isolation the records will be locked for reading purpose and will not allow any phantom read.
To understnad it more, you need to read the lock level and escallation. Then you can read further on type of isolation, which are 4 till 2000 and 5 in 2005 onwards.
----------
Ashish
June 8, 2011 at 5:12 am
Thanks! This is about the same answer as I believed. 🙂
June 8, 2011 at 6:08 am
You need to define blocking in this case. If you're doing reads, shared locks are taken, which will block any other process from performing updates (which includes deletes). That's independent of that isolation level.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply