Network Waits and Read Committed Snapshot
in [Server Configuration]
|
From: RG on 6 Jan 2010 11:33 I set my db isolation level to read committed snapshot. Will this allow me to read rows locked by network traffic? Thanks in advance
From: Tibor Karaszi on 6 Jan 2010 12:15 Network traffic doesn't lock rows. The database engine does. Read committed will cause SQL Server to give you earlier versions of data for rows with exclusive locks. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "RG" <RG(a)discussions.microsoft.com> wrote in message news:78423B8A-40F0-4875-B2B9-E8ABE0209071(a)microsoft.com... > I set my db isolation level to read committed snapshot. Will this allow > me > to read rows locked by network traffic? > > Thanks in advance
From: RG on 6 Jan 2010 13:28 Are you saying that while you have ASYNC_NETWORK_IO lock, the tables involved are available for reading? Thanks "Tibor Karaszi" wrote: > Network traffic doesn't lock rows. The database engine does. Read committed > will cause SQL Server to give you earlier versions of data for rows with > exclusive locks. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "RG" <RG(a)discussions.microsoft.com> wrote in message > news:78423B8A-40F0-4875-B2B9-E8ABE0209071(a)microsoft.com... > > I set my db isolation level to read committed snapshot. Will this allow > > me > > to read rows locked by network traffic? > > > > Thanks in advance > > . >
From: Andrew J. Kelly on 6 Jan 2010 14:33 ASYNC_NETWORK_IO is not a lock it is a wait type and has nothing directly to do with locking. But the snapshot isolation levels will allow you to read older versions of any data that has changed while you are trying to read it without blocking. But there is a difference in how the two snapshot isolation levels work so make sure you get the one you need. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "RG" <RG(a)discussions.microsoft.com> wrote in message news:30C19FA2-1C50-4046-A37D-DBF32A47E1E5(a)microsoft.com... > Are you saying that while you have ASYNC_NETWORK_IO lock, the tables > involved > are available for reading? > > Thanks > > > "Tibor Karaszi" wrote: > >> Network traffic doesn't lock rows. The database engine does. Read >> committed >> will cause SQL Server to give you earlier versions of data for rows with >> exclusive locks. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "RG" <RG(a)discussions.microsoft.com> wrote in message >> news:78423B8A-40F0-4875-B2B9-E8ABE0209071(a)microsoft.com... >> > I set my db isolation level to read committed snapshot. Will this >> > allow >> > me >> > to read rows locked by network traffic? >> > >> > Thanks in advance >> >> . >>
From: RG on 6 Jan 2010 15:46
I apologize, yes, wait type. When you say, the difference between 2 isolation levels, you mean issue of not getting somewhat obsolete data? If yes, I am not concerned about that. Correct me if I am wrong... The output of a query gets loaded to a network buffer, I assume on the server. If query output doesn't fit into the network buffer, sql server has to wait until remote client fetches this data to refill the network buffer. The wait between the refills is of type ASYNC_NETWORK_IO. If my isolation level is read commited and not read committed snapshot, the select statement against the same tables is partially, if not completely, blocked until client fetch completes. With read committed snapshot in the same scenario, select statement will not be blocked. "Andrew J. Kelly" wrote: > ASYNC_NETWORK_IO is not a lock it is a wait type and has nothing directly to > do with locking. But the snapshot isolation levels will allow you to read > older versions of any data that has changed while you are trying to read it > without blocking. But there is a difference in how the two snapshot > isolation levels work so make sure you get the one you need. > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "RG" <RG(a)discussions.microsoft.com> wrote in message > news:30C19FA2-1C50-4046-A37D-DBF32A47E1E5(a)microsoft.com... > > Are you saying that while you have ASYNC_NETWORK_IO lock, the tables > > involved > > are available for reading? > > > > Thanks > > > > > > "Tibor Karaszi" wrote: > > > >> Network traffic doesn't lock rows. The database engine does. Read > >> committed > >> will cause SQL Server to give you earlier versions of data for rows with > >> exclusive locks. > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://sqlblog.com/blogs/tibor_karaszi > >> > >> > >> > >> "RG" <RG(a)discussions.microsoft.com> wrote in message > >> news:78423B8A-40F0-4875-B2B9-E8ABE0209071(a)microsoft.com... > >> > I set my db isolation level to read committed snapshot. Will this > >> > allow > >> > me > >> > to read rows locked by network traffic? > >> > > >> > Thanks in advance > >> > >> . > >> > . > |