ExecuteReader阻止表上的插入 [英] ExecuteReader Blocks Inserts on a Table

问题描述

我有一个使用简单SELECT从表中返回数据的sproc。

返回了很多行，例如~15,000。


在我的第一个应用程序中，我在sproc上使用了一个阅读器，并遍历

行，将它们写入文件。这需要大约5分钟，直到我关闭

读者。


虽然这种情况正在发生，但我还有另一个试图插入的应用程序

行进入表中。通常情况下，插入会立即发生，但是当读取器打开时，每个插入都需要很长时间才能完成。


我意识到这不是一个不合理的事情发生，因为我在读取它时试图写入表格，但写入的行将永远不会包含在WHERE子句中在我的选择声明中，

，即使它们是（他们不会），我也不希望它们包含在

所选行中。 />

有没有办法读取行，这样插入仍然可以在没有

阻塞的情况下发生？


我正在使用VB VS2005中的.NET和SQL Server 2005.


TIA


Charles

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

推荐答案

Charles，


两台应用程序是在同一台机器上还是在不同的机器上？


数据库服务器是否开启与应用程序不同的机器？


Ke rry Moorman

" Charles Law"写道：

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:

我有一个使用简单SELECT从表中返回数据的sproc。

返回了很多行，例如~15,000。


在我的第一个应用程序中，我在sproc上使用了一个阅读器，并遍历

行，将它们写入文件。这需要大约5分钟，直到我关闭

读者。


虽然这种情况正在发生，但我还有另一个试图插入的应用程序

行进入表中。通常情况下，插入会立即发生，但是当读取器打开时，每个插入都需要很长时间才能完成。


我意识到这不是一个不合理的事情发生，因为我在读取它时试图写入表格，但写入的行将永远不会包含在WHERE子句中在我的选择声明中，

，即使它们是（他们不会），我也不希望它们包含在

所选行中。 />

有没有办法读取行，这样插入仍然可以在没有

阻塞的情况下发生？


我正在使用VB VS2005中的.NET和SQL Server 2005.


TIA


Charles

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

Charles，


顺便说一句，你不使用事务锁定，因为这是

的正常行为。


Cor


" Charles Law" < bl *** @ nowhere.comschreef在bericht

新闻：Om ************** @ TK2MSFTNGP06.phx.gbl ...

Charles,

You are by the way not using transaction locking, because then this is the
normal behaviour.

Cor

"Charles Law" <bl***@nowhere.comschreef in bericht
news:Om**************@TK2MSFTNGP06.phx.gbl...

>我有一个使用简单SELECT从表中返回数据的sproc。
返回了很多行，例如~150,000。


在我的第一个应用程序中，我在sproc上使用了一个读取器，并在行中迭代

，将它们写入文件。这需要大约5分钟，直到我关闭阅读器。


虽然这种情况正在发生，但我有另一个应用程序正在尝试

在表中插入行。通常情况下，插件会立即发生，

但是当读卡器打开时，每个插件需要很长时间才能完成
。


我意识到这不是一件不合理的事情，因为我在读取它的同时试图写信给桌子，但行是

写的将永远不会包含在我的select语句的WHERE子句中，

，即使它们是（他们不会），我也不希望它们包括在
$ b中$ b所选行。


有没有办法读取行，这样插入仍然可以在没有

阻塞的情况下发生？


我在VS2005和SQL Server 2005中使用VB.NET。


TIA


Charles

>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

On Thu，2008年6月19日02:17:57 +0100，Charles Law < bl *** @ nowhere.com>

写道：

On Thu, 19 Jun 2008 02:17:57 +0100, "Charles Law" <bl***@nowhere.com>
wrote:

>我有一个sproc，它使用表返回数据一个简单的SELECT。
返回了很多行，例如~150,000。

在我的第一个应用程序中，我在sproc上使用了一个读取器并遍历
行，将它们写入文件。这需要大约5分钟，直到我关闭读者。

虽然这是继续，但我有另一个应用程序试图在表中插入
行。通常情况下，插入物会立即发生，但当读者打开时，每个插入物都需要很长时间才能完成。

我意识到这不是一个不合理的事情，因为我正在尝试在读取它时写入表格，但是写入的行将永远不会包含在我的select语句的WHERE子句中，
即使它们是（也就是他们不会），我不希望它们包含在选定的行中。

有没有办法读取行，这样插入仍然可以不用
阻止？

我在VS2005和SQL Server 2005中使用VB.NET。

TIA

Charles

>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

您可以查看Transact-SQL语句SET TRANSACTION

ISOLATION LEVEL。

You might take a look at the Transact-SQL statement SET TRANSACTION
ISOLATION LEVEL.

这篇关于ExecuteReader阻止表上的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！