Hi GilaMonster,
Thanks for the reply 🙂
Can u plz go thro the below paragraphs and offer me your valuable guidance/suggestions.
When i tried to explore isolation levels i found the below stuff.
The following statements are executed to test READCOMMITTED isolation level
CREATE TABLE Consultants (
ConsultantID INT IDENTITY,
ConsultantName VARCHAR(20),
Technology VARCHAR(15),
HireDate DATETIME,
HourlyRate MONEY)
---------------------------------------------------------------------
-- Fill some data
---------------------------------------------------------------------
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Jacob', 'SQL Server', '2000-10-03', 120
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Smith', 'ASP.NET', '2005-01-01', 140000
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Michael', 'C#.NET', '2002-10-30', 80000
I executed the following statements in Query Window 1
BEGIN TRAN
UPDATE c SET
HourlyRate = 250
FROM Consultants c
WHERE ConsultantName = 'Smith'
When execcuted the following statements from Query Window 2
select * from Consultants
the query blocks and does not return. This query blocks with the transaction running on
the other session. Now move back to Query Window 1 and COMMIT the transaction.
COMMIT TRAN
Now switch back again to Query Window 2. You will notice that the query returned the following.
/*
ConsultantID ConsultantName Technology HireDate HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1 Jacob SQL Server 2000-10-03 00:00:00.000 120.00
2 Smith ASP.NET 2005-01-01 00:00:00.000 250.00
3 Michael C#.NET 2002-10-30 00:00:00.000 100.00
*/
I then created a clustered index on the ConsultantID column.
ALTER TABLE Consultants
ADD CONSTRAINT PK_Consultants PRIMARY KEY (ConsultantID)
Now move to Query Window 1 and update the record of Smith.
BEGIN TRAN
UPDATE c SET
HourlyRate = 250
FROM Consultants c
WHERE ConsultantName = 'Smith'
Now, Move to Query Window 2 and run the following queries.
SELECT *
FROM Consultants
WHERE ConsultantID = 3
/*
ConsultantID ConsultantName Technology HireDate HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
3 Michael C#.NET 2002-10-30 00:00:00.000 100.00
*/
Now, the thing i want to know is that "is still something
there to know more about isolation levels in connection with indexes ?"
N.B: the above code and observations were obtained from the following URL
http://www.sqlserverandxml.com/2008/08/sql-server-transaction-isolation-level.html
Thanks and Regards,
Sree