August 14, 2013 at 8:16 pm
Hi Guys,
Understand that by default, SELECT statement on a table will block update statement on the table.
By setting READ_COMMITTED_SNAPSHOT, update statement (writer) will not block select statement (reader) as the pre-image of the update will be stored in tempdb for select statement to access.
However, by setting READ_COMMITTED_SNAPSHOT, will reader (select) still block writer (update)?
Any simple sql statement to test this out?
thanks
August 16, 2013 at 6:31 am
In short No. The below script will prove this. There is an great book by Kalen Delaney that explains locking in great detail. Link below;
http://qa.sqlservercentral.com/articles/books/93946/
-- Set database context
USE master;
GO
-- Needs exclusive database access to set this
ALTER DATABASE SQLServer365 SET READ_COMMITTED_SNAPSHOT ON;
GO
-- Set database context
USE SQLServer365;
GO
-- Create test table
CREATE TABLE dbo.IsolationTest
(
ID INT IDENTITY(1,1),
Col1 VARCHAR(50)
);
GO
-- Insert some data
INSERT INTO dbo.IsolationTest
VALUES ('Test')
GO 100
INSERT INTO dbo.IsolationTest
VALUES ('Testing')
GO 100
-- Run a select in read committed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM dbo.IsolationTest;
GO
-- In a 2nd window
-- Find object id (Mine is 722101613)
SELECT OBJECT_ID('dbo.IsolationTest');
GO
-- Check for locks (There shouldn't be any)
sp_lock;
GO
-- In a 3rd window
-- Update records (This works fine)
UPDATE dbo.isolationtest
SET Col1 = 'Tester'
WHERE Col1 = 'Test';
GO
Cheers
Chris
August 16, 2013 at 10:36 am
chewychewy (8/14/2013)
Hi Guys,Understand that by default, SELECT statement on a table will block update statement on the table.
By setting READ_COMMITTED_SNAPSHOT, update statement (writer) will not block select statement (reader) as the pre-image of the update will be stored in tempdb for select statement to access.
However, by setting READ_COMMITTED_SNAPSHOT, will reader (select) still block writer (update)?
Any simple sql statement to test this out?
thanks
If your script mixes and matches select statements and update/insert/delete statements AND you are running in serializable isolation level then you could have a blocking select statement
READ_COMMITTED_SNAPSHOT does mean that writers won't block readers and vice versa but it does put a strain on tempdb. If your transactions don't need to be at a higher level of isolation you could lower it by utilising read_uncommited (nolock) for your reads but only if they don;t need to be ACID.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply