Re: SqlDataReader default locking?

From: Alex Callea (alex.callea_at_sellys.com)
Date: 11/08/04 

Date: Mon, 8 Nov 2004 17:52:48 +0100

Hi Mary,

Thanks, for your reply, it helps a lot.
In your post you mention that with the READ UNCOMMITTED level we can read
that never existed (which I understand in the case of rollbacks or
transactions in error).

Is it also the case with consequent updates:
Let's imagine we perform the 4 following transactions one after the other
A)Update SET X=1.1
B)Update SET X= 1.2
C)Update SET X=1.3
D)Update SET X= 1.4
My question is if we do a READ UNCOMMITTED SELECT X while command D) is
being updated can we read something diffrent than X=1.3 which is the last
commited update?

Thanks
Alex

"Mary Chipman" <mchip@online.microsoft.com> wrote in message
news:lrtuo099ct9cg9542vg1dt9lpcasbcu8od@4ax.com...
> Locking in SQL Server depends on the isolation level of the
> transaction, not on whether it's a stored procedure/trigger. The
> default is READ COMMITTED, which ensures that only data that has been
> committed is read, and that you don't experience what are known as
> dirty reads, where you could possibly read data that never actually
> exists. The subject is explained in detail in the SQL Server Books
> Online topic, "Locking Architecture". Typically a read operation has a
> sub-second response time where locks are held for the minimum amount
> of time it takes to ensure that the data is consistent. You can
> manually set the isolation level to READ UNCOMMITTED, which does not
> hold or honor any locks, and can give you dirty reads. This is used in
> situations where you are aggregating large amounts of data for and do
> not care whether individual rows are accurate or not. See the
> "Transactions" topic in BOL for more information.
>
> --Mary
>
> On Mon, 8 Nov 2004 12:56:34 +0100, "Alex Callea"
> <alex.callea@sellys.com> wrote:
>
>>Hi there,
>>
>>I just wanted to know wheter any row locking takes place when we are
>>reading
>>data with a sqlDataReader.
>>
>>I know that within stored procedures and triggers the default is Row
>>locking. Is it the same with a datareader?
>>
>>Thanks
>>
>>
>

Relevant Pages

  • Re: Help..VB6 Access 2000 Adding Records Lock Error
    ... First of all you use "select * from transactions" SQL ... select data at all to be able to add new rows to the database table. ... then you will be in a locking trouble regarding ... > of the times it locks all the machines. ...
    (microsoft.public.data.ado)
  • Re: [PATCH 0/3] 64-bit futexes: Intro
    ... i suspect _any_ abstract locking functionality around a data structure ... tightly coupled to the exact details of which atomics are fast on a ... For example, some loads are almost entirely read-read locks, with only ... There we can just do a write to unlock. ...
    (Linux-Kernel)
  • Re: Working Transactions somehow started not to work
    ... Locking is a property of the recordset. ... SL> and are not associated necessarily with server-based cursors. ... For the simple reason: transactions and locking involve ... SL>>> Instead of using one of the connection created by Access, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Transaction Isolation Level
    ... what size servers are you working ... and only have to specify locking hints when we ... need locks. ...
    (microsoft.public.sqlserver.server)
  • Re: Working Transactions somehow started not to work
    ... are not associated necessarily with server-based cursors. ... For the simple reason: transactions and locking involve ... SL> Instead of using one of the connection created by Access, ...
    (microsoft.public.access.adp.sqlserver)