Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction Isolation question Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 3:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:01 PM
Points: 298, Visits: 1,102
The isolation level of the database that I'm running the query on is READ COMMITTED.
I ran query 1 and query 2.
Query 1 is
IF OBJECT_ID('tempdb..##my_name') IS NOT NULL
BEGIN
DROP TABLE ##my_name;
END;

CREATE TABLE ##my_name
(
id INT,
first_name VARCHAR(20)
);

INSERT INTO ##my_name (id, first_name)
VALUES (1, 'dexter');

BEGIN TRAN

UPDATE ##my_name
SET first_name = 'derek'
WHERE id = 1;



Query 2 is
SELECT * FROM ##my_name WITH (NOLOCK);
SELECT * FROM ##my_name;

My question is that I was expecting "SELECT * from ##my_name" to hang without generating results because I haven't ran COMMIT TRAN or ROLLBACK TRAN yet. But I'm getting results as dirty read.

I'm little confused here. Can someone help me out?
Post #1493923
Posted Thursday, September 12, 2013 12:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 9:49 PM
Points: 1,101, Visits: 5,284
You should run the Query 2 on a different session (query window).
Post #1494007
Posted Thursday, September 12, 2013 1:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 43,008, Visits: 36,164
You never block yourself. If you have just run a data modification, in that same session you can always see the data you modified.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1494014
Posted Thursday, September 12, 2013 2:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:01 PM
Points: 298, Visits: 1,102
Feeling stupid.

Thanks guys! Problem solved..
Post #1494356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse