SQL Server 2005 causes problems with second connection

13 Feb 2007 3:04 PM

Oldman

I have a scenario where I am making an update using multiple calls to the
database and all the calls are within the same transaction. However, before
I commit I have need to query some additional data. So I created a NEW
connection and performed the query. Problem is, when the database is SQL
Server 2005 this second query times out because of the other connection with
the transaction open.
This does not happen when the database is SQL Server 2000.

What is the problem? I have read that you can't have multiple readers open
on the same connection, etc. But if you have two separate connections what
I'm trying to do should be possible. Can anyone explain the behavior I'm
seeing?

Thanks!

13 Feb 2007 4:39 PM

Miha Markic [MVP C#]

It depends on the type of transaction. If first transaction is locking rows
then the other can't read them.
Why don't you read within first transaction?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"Oldman" <Old***@discussions.microsoft.com> wrote in message
news:35626ED2-425F-4A42-B480-708E3258CC7F@microsoft.com...
>I have a scenario where I am making an update using multiple calls to the
> database and all the calls are within the same transaction. However,
> before
> I commit I have need to query some additional data. So I created a NEW
> connection and performed the query. Problem is, when the database is SQL
> Server 2005 this second query times out because of the other connection
> with
> the transaction open.
> This does not happen when the database is SQL Server 2000.
>
> What is the problem? I have read that you can't have multiple readers
> open
> on the same connection, etc. But if you have two separate connections
> what
> I'm trying to do should be possible. Can anyone explain the behavior I'm
> seeing?
>
> Thanks!

13 Feb 2007 5:45 PM

Oldman

That is how I had to fix it. The main reason I wasn't doing it in a
transaction is the object model that wrapped the calls to retrieve the data
didn't take in a transaction as a parameter because they were retrieval
methods vs. methods that modify data in the database.

Thanks

Show quote

"Miha Markic [MVP C#]" wrote:

> It depends on the type of transaction. If first transaction is locking rows
> then the other can't read them.
> Why don't you read within first transaction?
>
> --
> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "Oldman" <Old***@discussions.microsoft.com> wrote in message
> news:35626ED2-425F-4A42-B480-708E3258CC7F@microsoft.com...
> >I have a scenario where I am making an update using multiple calls to the
> > database and all the calls are within the same transaction. However,
> > before
> > I commit I have need to query some additional data. So I created a NEW
> > connection and performed the query. Problem is, when the database is SQL
> > Server 2005 this second query times out because of the other connection
> > with
> > the transaction open.
> > This does not happen when the database is SQL Server 2000.
> >
> > What is the problem? I have read that you can't have multiple readers
> > open
> > on the same connection, etc. But if you have two separate connections
> > what
> > I'm trying to do should be possible. Can anyone explain the behavior I'm
> > seeing?
> >
> > Thanks!
>