Database strategy in a multithreaded app.

[Frank Rizzo]

Hello,

I am writing a server that will be inserting/reading/updating the
database on multiple threads. I understand that you can't have more
than 1 reader open on a database connection. Are there any other
limitations? Like can I have more than one command object inserting on
the same connection? Any other pitfalls, I should look out for?

Thanks.

[Jon Skeet [C# MVP]]

Frank Rizzo <no****@nospam.com> wrote:

I am writing a server that will be inserting/reading/updating the
database on multiple threads. I understand that you can't have more
than 1 reader open on a database connection. Are there any other
limitations? Like can I have more than one command object inserting on
the same connection? Any other pitfalls, I should look out for?

Each operation should usually open a connection, do its stuff, and then
close the connection. Let connection pool handling take care of the
rest for you.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor Ligthert]

Frank,

I am curious what is your goal with this.

The client is accessing a single thread of updating.
It can not read in that time inside one table because it does not know the
concurrency situation and with more tables it has to handle relations.

The connection is accessing over the network or over internet. However it is
one line, you cannot sinful optimize it by using more connections when it
would be to slow what is the normal reason for multithreading here. (Just
give more priority on that line when it is dealt with others)

The database is accessing at full speed the given commands from the clients
one by one and needs as well concurrency synchronization. (However even
without that and with completly unrelated datatables can it only handle one
thing at time)

I am really interested what you want to achieve and than how you would
handle your dependencies.

Cor

[Frank Rizzo]

Jon Skeet [C# MVP] wrote:

Frank Rizzo <no****@nospam.com> wrote:

I am writing a server that will be inserting/reading/updating the
database on multiple threads. I understand that you can't have more
than 1 reader open on a database connection. Are there any other
limitations? Like can I have more than one command object inserting on
the same connection? Any other pitfalls, I should look out for?

Each operation should usually open a connection, do its stuff, and then
close the connection. Let connection pool handling take care of the
rest for you.

Would the application not be slowed down by constant opening/reopening
of the connection?

[Frank Rizzo]

Cor Ligthert wrote:

Frank,

I am curious what is your goal with this.

The client is accessing a single thread of updating.
It can not read in that time inside one table because it does not know the
concurrency situation and with more tables it has to handle relations.
That's not true, depending on the locking mechanism you can select and
insert at the same time. In my database design, there are very little
concurrency issues. In other words, the clients access a different set
of data - never the same.

The connection is accessing over the network or over internet. However it is
one line, you cannot sinful optimize it by using more connections when it
would be to slow what is the normal reason for multithreading here. (Just
give more priority on that line when it is dealt with others)
Yes, you can, because the bandwidth needed for the commands to the
database is very low. The work is mostly done at either the client
level or the database level.
The database is accessing at full speed the given commands from the clients
one by one and needs as well concurrency synchronization. (However even
without that and with completly unrelated datatables can it only handle one
thing at time).
That's true and that's ok. I can optimize at the database level by
storing all indexes on a different drive, for instance. This way
physical I/O can work in parallel (sometimes). And, of course, you can
always throw more money at the machine.
I am really interested what you want to achieve and than how you would
handle your dependencies.

I am looking for robustness first, speed second. Mostly because you can
buy speed. You can't buy robustness.

[Jon Skeet [C# MVP]]

Frank Rizzo <no**@none.com> wrote:

Each operation should usually open a connection, do its stuff, and then
close the connection. Let connection pool handling take care of the
rest for you.

Would the application not be slowed down by constant opening/reopening
of the connection?

No - it doesn't close the physical connection, it just returns it to
the pool.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Frank Rizzo]

Jon Skeet [C# MVP] wrote:

Frank Rizzo <no**@none.com> wrote:

Each operation should usually open a connection, do its stuff, and then
close the connection. Let connection pool handling take care of the
rest for you.

Would the application not be slowed down by constant opening/reopening
of the connection?

No - it doesn't close the physical connection, it just returns it to
the pool.

Thank you very much.