SqlDataReader default locking?

  • Thread starter Thread starter Alex Callea
  • Start date Start date
A

Alex Callea

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
 
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
 
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
 
If 1.3 has been committed, you will see that. If 1.4 has not been
committed, you will (I believe) see that as well. The problem is that
if 1.4 gets rolled back subsequent to the dirty read, you will have
seen phantom data that never actually existed in the database.

--Mary
 
Back
Top