0

i start with a simple question:

according to Dirty Read definition in Wikipedia and Msdn :

we have 2 concurrent transactions, T1 and T2

Dirty Reads Occur in ,when T1 is Updating a row and T2 is reading row that "is not Committed yet" by T1

but at Read Committed Level shared locks are released as soon as the data is read (not at the end of the transaction or even the end of the statement

then how Read Committed prevents Dirty Reads? Bkaz as soon as the share lock released on updated row T2 can read the updated row and t1 can rollback the whole operation,,then we have a dirty read on the hand of t1

raoof hojat
  • 355
  • 4
  • 12

2 Answers2

1

It prevents the dirty read because T1 has a lock on the row, so T2 can't read the "not yet committed" row that could be rollbacked later.

The problem Read Committed tries to resolve is:

T1 creates a transaction and writes something

T2 reads that something

T1 rollback the transaction

now T2 has a data that didn't really ever existed.

Depending on how the DB is structured, there are two "good" possibilities:

T1 creates a transaction and writes something

T2 waits for T1 to end the transaction

or

T2 reads a "snapshot" of how the DB was BEFORE T1 began the transaction (it's called Read committed using row versioning)

(the default on MSSQL is the first option)

Here for example there is a comparison of the various isolation levels: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx (read under Isolation Levels Offered in SQL Server 2005)

xanatos
  • 109,618
  • 12
  • 197
  • 280
  • u say "T1 has a lock on the row, so T2 can't read the 'not yet committed' row that could be rollbacked later" but if T1 has this lock on row until the end of transaction,why should we have Repeatable reads isolation for the whole transaction,can i make myself clear? – raoof hojat Mar 15 '12 at 12:54
  • @raoofhojat Repeatable is another level of isolation. It means that T2 reads, T1 writes and commits, T2 re-reads if repeatable he should be able to reread the same values of the first time. This can be obtained in two ways: T1 takes a lock during the first read so T2 can't write in the middle and has to wait the end of the T1 transaction or you use full snapshotting (that is more than row versioning if you look at the table) – xanatos Mar 15 '12 at 15:00
  • i know Repeatable is another level of isolation,,u said 'T2 can't read the "not yet committed" row that could be rollbacked later',,if its whats the Commited Reads do then Commited Reads can lock read untill the end of transaction,,then it can handle repeatble reads – raoof hojat Mar 15 '12 at 16:28
  • @raoofhojat Committed Reads won't make a lock for reading. It will read if unlocked and not lock. So after the reading T1 can lock it. Repetable reads will lock + read and keep the lock. – xanatos Mar 15 '12 at 16:31
0

When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis. The duration of these share locks is just long enough to read and process each row; the server generally releases each lock before proceeding to the next row. Thus, if you run a simple select statement under read committed and check for locks (e.g., with sys.dm_tran_locks), you will typically see at most a single row lock at a time. The sole purpose of these locks is to ensure that the statement only reads and returns committed data. The locks work because updates always acquire an exclusive lock which blocks any readers trying to acquire a share lock.

Ripped from here

Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
  • chris even if short lived share locks duration is so long,it cant make sure that T1 cant rollback the transaction,this lock will be released and T2 can read updated row that will be rolback later – raoof hojat Mar 15 '12 at 12:59
  • @raoofhojat - false. The short lived share locks are for reads. Updates always acquire an exclusive lock which is not released until the transaction is committed. – Chris Gessler Mar 15 '12 at 13:18
  • -but we have not update locks in read commited level to ensure row that is edditing by T1 is not read by T2 after(look at my senario),,then how read commited guarantee no dirty reads – raoof hojat Mar 15 '12 at 16:21
  • @raoofhojat - read committed requests a SHARE lock and updates use an EXCLUSIVE lock which blocks any readers trying to acquire a SHARE lock. – Chris Gessler Mar 15 '12 at 19:16