ExecuteReader Blocks Inserts on a Table

[Alex Kuznetsov]

Re: ExecuteReader Blocks Inserts on a Table

On Jun 18, 8:17 pm, "Charles Law" <bl...@nowhere. comwrote:

I have a sproc that returns data from a table using a simple SELECT. There
are quite a few rows returned, e.g. ~150,000.
>
In my first application, I use a reader on the sproc and iterate through the
rows, writing them out to a file. This takes about 5 minutes until I close
the reader.
>
Whilst this is going on, I have another application that is trying to insert
rows into the table. Normally, the inserts happen straight away, but when
the reader is open each insert takes a very long time to complete.
>
I realise that this is not an unreasonable thing to happen, given that I am
trying to write to the table whilst reading from it, but the rows being
written will never be included in the WHERE clause in my select statement,
and even if they were (which they won't), I wouldn't want them included in
the selected rows.
>
Is there a way to read rows so that inserts can still occur without
blocking?
>
I am using VB.NET in VS2005, and SQL Server 2005.
>
TIA
>
Charles

Consider using snapshot isolation, so that readers do not block
writers.

[Charles Law]

Re: ExecuteReader Blocks Inserts on a Table

Hi Göran

Thanks for the tip. That fits in with what I am seeing. I will give it a
try.

Charles


"Göran Andersson" <guffa@guffa.co mwrote in message
news:eUmAjZg0IH A.3884@TK2MSFTN GP05.phx.gbl...

Charles Law wrote:

>I have a sproc that returns data from a table using a simple SELECT.
>There are quite a few rows returned, e.g. ~150,000.
>>
>In my first application, I use a reader on the sproc and iterate through
>the rows, writing them out to a file. This takes about 5 minutes until I
>close the reader.
>>
>Whilst this is going on, I have another application that is trying to
>insert rows into the table. Normally, the inserts happen straight away,
>but when the reader is open each insert takes a very long time to
>complete.
>>
>I realise that this is not an unreasonable thing to happen, given that I
>am trying to write to the table whilst reading from it, but the rows
>being written will never be included in the WHERE clause in my select
>statement, and even if they were (which they won't), I wouldn't want them
>included in the selected rows.
>>
>Is there a way to read rows so that inserts can still occur without
>blocking?
>>
>I am using VB.NET in VS2005, and SQL Server 2005.
>>
>TIA
>>
>Charles
>>

>
As already has been mentioned, there is different levels of locking. As
you are reading so many lines, the row locks will probably escalate into
page locks or a table lock to preserve resources. When that happens, it
will also lock other rows than the ones that you have selected.
>
You can specify (ROWLOCK) in your query. That should keep the database
from escalating the locks.
>
--
Göran Andersson
_____
http://www.guffa.com

[Charles Law]

Re: ExecuteReader Blocks Inserts on a Table

Hi Alex

I have just been reading about snapshot isolation and it also looks like a
likely candidate, as are some of the other suggestions.

Thanks.

Charles


"Alex Kuznetsov" <alkuzo@gmail.c omwrote in message
news:f5bac2a6-32c7-4efd-8e85-77d7a0d23cb3@a7 0g2000hsh.googl egroups.com...

On Jun 18, 8:17 pm, "Charles Law" <bl...@nowhere. comwrote:

>I have a sproc that returns data from a table using a simple SELECT.
>There
>are quite a few rows returned, e.g. ~150,000.
>>
>In my first application, I use a reader on the sproc and iterate through
>the
>rows, writing them out to a file. This takes about 5 minutes until I
>close
>the reader.
>>
>Whilst this is going on, I have another application that is trying to
>insert
>rows into the table. Normally, the inserts happen straight away, but when
>the reader is open each insert takes a very long time to complete.
>>
>I realise that this is not an unreasonable thing to happen, given that I
>am
>trying to write to the table whilst reading from it, but the rows being
>written will never be included in the WHERE clause in my select
>statement,
>and even if they were (which they won't), I wouldn't want them included
>in
>the selected rows.
>>
>Is there a way to read rows so that inserts can still occur without
>blocking?
>>
>I am using VB.NET in VS2005, and SQL Server 2005.
>>
>TIA
>>
>Charles

>
Consider using snapshot isolation, so that readers do not block
writers.

[Cor Ligthert[MVP]]

Re: ExecuteReader Blocks Inserts on a Table

Charles,

I thought that I had read once from William Vaughn that in fact the
datareader is processing a big resultset, which is simply build in one time.
Therefore the problem seems strange for me.

Try it in the AdoNet newsgroup, this is honey for Bill.

Cor

"Charles Law" <blank@nowhere. comschreef in bericht
news:OaWNELe0IH A.4704@TK2MSFTN GP05.phx.gbl...

Hi Kerry
>
The two applications are on different machines, connected by a fairly slow
link, which is why it takes so long to iterate through the rows returned
by the reader. The database server is clustered on another machine, which
is on a Gb link to the second application, but the first (reader)
application is at the other end of the slow connection.
>
I could run both on the same machine, but then I would have to transfer
the resulting file over the slow link, and that would take longer in real
time.
>
Charles
>
>
"Kerry Moorman" <KerryMoorman@d iscussions.micr osoft.comwrote in message
news:6A4C73DD-8153-4A76-9DAE-288CE51305F4@mi crosoft.com...

>Charles,
>>
>Are the 2 applications on the same machine or different machines?
>>
>Is the database server on a different machine than the applications?
>>
>Kerry Moorman
>>
>>
>"Charles Law" wrote:
>>

>>I have a sproc that returns data from a table using a simple SELECT.
>>There
>>are quite a few rows returned, e.g. ~150,000.
>>>
>>In my first application, I use a reader on the sproc and iterate through
>>the
>>rows, writing them out to a file. This takes about 5 minutes until I
>>close
>>the reader.
>>>
>>Whilst this is going on, I have another application that is trying to
>>insert
>>rows into the table. Normally, the inserts happen straight away, but
>>when
>>the reader is open each insert takes a very long time to complete.
>>>
>>I realise that this is not an unreasonable thing to happen, given that I
>>am
>>trying to write to the table whilst reading from it, but the rows being
>>written will never be included in the WHERE clause in my select
>>statement,
>>and even if they were (which they won't), I wouldn't want them included
>>in
>>the selected rows.
>>>
>>Is there a way to read rows so that inserts can still occur without
>>blocking?
>>>
>>I am using VB.NET in VS2005, and SQL Server 2005.
>>>
>>TIA
>>>
>>Charles
>>>
>>>
>>>

>
>