jxharding
asked on
SQL 2005 Transcation : is .net transaction default to read commited?is this why can't read from table where insert is done until commit?
I set up a .net transcation where I inserted into Table1
then Updated Table2, then commited.
Immediately after the insert was done into Table1 (before the commit), I could not read from Table1 - it was locked for reading any record until commit/rollback.
Is this because the default .net isolation level is "READ COMMITTED"?
Will READ COMMITTED cause this behaviour?
Now because of this I did the transaction in a SQL Stored Proc.
The default behaviour is different here - I can read after the Insert , and before the commit.
What is the default isolation level then for SQL Server 2005? Same/Different than .net? Can anyone comment on this please?
E.g.
USE AdventureWorks
BEGIN TRAN
Insert into Person.Address(AddressLine 1,City,Sta teProvince ID,PostalC ode,rowgui d,Modified Date¿¿) select '1973 Napa Ct.','Bothell',79,'98011', '9AADCB0D- 36CF-483F- 84D8-585C2 D4EC6E3',g etdate()
select * from Person.Address
ROLLBACK
then Updated Table2, then commited.
Immediately after the insert was done into Table1 (before the commit), I could not read from Table1 - it was locked for reading any record until commit/rollback.
Is this because the default .net isolation level is "READ COMMITTED"?
Will READ COMMITTED cause this behaviour?
Now because of this I did the transaction in a SQL Stored Proc.
The default behaviour is different here - I can read after the Insert , and before the commit.
What is the default isolation level then for SQL Server 2005? Same/Different than .net? Can anyone comment on this please?
E.g.
USE AdventureWorks
BEGIN TRAN
Insert into Person.Address(AddressLine
select * from Person.Address
ROLLBACK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is there a way to, in the middle of a transaction, view the isolation level perhaps? I dont think one can set the default isolation level on a DB? Yet somehow this happens without setting the isolation level to read uncommitted