Re: transactions on reads?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Michael, Cheng [MSFT] (v-mingqc_at_online.microsoft.com)
Date: 04/23/04 

Date: Fri, 23 Apr 2004 11:00:11 GMT

Hi Daniel,

>From your description, I understand that you would like to know isolation
level of select statement. Have I fully understood you? If there is
anything I misunderstood, please feel free to let me know.

Based on my knowledge, select statement is default in Read Committed
isolation, which is to say you needn't make it a transaction explicitly. I
would like to show you an example about how it works
1. Open Query Analyzer and Login in as sa (I called it as Connection #1)
2. Original lastname of employeeid = 1 is Davolio
--Run the codes in #1
  use northwind
  go
  begin tran
  update employees set lastname='test' where employeeid = 1
--Just end here now
3. Open another Login as sa with Query Analyzer by select menu File ->
Connect ..., I will call this connection as #2
--Run the codes in #2
  select * from employees where employeeid = 1
--End of here
4. You will see that there is no result back, as Select statement is in
read committed level, it will wait until a transaction is ended.
5. Open third Login as sa with Query Analyzer, I would like to call it as
connection #3
--Run the codes in #3
  set transaction isolation level read uncommitted
  select * from employee where employeeid = 1
--End of here
  You will see that you could get the result with lastname = 'test'. As we
select the isolation level explicitly enable dirty read, so we could get
the 'dirty data'. Please compare this result with what in #2
6. change to #1
--Run the codes
  rollback
--End of here, we rollback the update operation of transaction
7. look at both #2 and #3, you will find that #2 show the right data as
Davolio and #3 get a dirty data.

Moreover, you could have a look at
Isolation Levels
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_con_7a_5t29.asp

Hope this helps and if you have any questions or concerns, don't hesitate
to let me know.

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Relevant Pages

  • Re: ISOLATION levels and when to use SERIALIZEABLE
    ... Setting an isolation level tells SQL Server ... satisfy the where clause of a query in the transaction. ... acquiring KEY RANGE locks to protect a range of data in an index. ... select * into orders2 from orders ...
    (microsoft.public.sqlserver.programming)
  • Re: Pessimistic Locking
    ... > A normal select statement executed in a transaction with serializable ... > isolation level was still possible, ... > was called which should lock the row. ... > in a transacion with serializable isolation level and another client has ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SELECT statement behavior with Key Range Lock
    ... Your thoughts on the Serializable Isolation level are interesting. ... I stated it the Key Lock was a RANGEKey Lock (Shared range, ... you had an open transaction when you entered the procedure. ...
    (microsoft.public.sqlserver.server)
  • Re: concurrent transactions
    ... The deadlock is normal with any isolation level (it was my mistake in the ... Because that you need a locking hint when executing the select: ... You execute this with RC isolation level and will be fine. ... So I don't need a serializable transaction? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transaction isolation levels
    ... SELECT 'X' INTO #TEMP ... locks the row in table TICKETS until the entire transaction has ... The lock taken out on TICKETS will be a shared lock ... that under the default isolation level will be released. ...
    (comp.databases.ms-sqlserver)