how do you handle multiple update request on the database?

[AboutJAV]

Hi,

I was thinking of using MSMQ to handling multiple simultaneous
database request to update or insert records to table. There could be
hundreds of database existing connections trying to access the same
table. I was thinking to writing an app with threads to listen and 1
at a time handle each request on the queue.

Is this a good approach? If not, is there a better way with .NET/C#?

Is there a good link to view an example on how to do this?

Thanks

 

[Nicholas Paldino [.NET/C# MVP]]

This depends on the database that you are using. If it is SQL server,
then I would just set connection pooling on for your app, and then just make
the requests as they come in. SQL Server will handle the concurrency issues
(as far as updating the same tables at the same time, but not making sure
that changes don't overwrite each other, that's up to you).

Hope this helps.

 

[Egghead]

Actually, one more here -- transcation

http://www.codeproject.com/cs/database/transactions.asp

cheers,
RL

This depends on the database that you are using. If it is SQL server,
then I would just set connection pooling on for your app, and then just
make the requests as they come in. SQL Server will handle the concurrency
issues (as far as updating the same tables at the same time, but not
making sure that changes don't overwrite each other, that's up to you).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi,

I was thinking of using MSMQ to handling multiple simultaneous
database request to update or insert records to table. There could be
hundreds of database existing connections trying to access the same
table. I was thinking to writing an app with threads to listen and 1
at a time handle each request on the queue.

Is this a good approach? If not, is there a better way with .NET/C#?

Is there a good link to view an example on how to do this?

Thanks

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

This depends on the database that you are using. If it is SQL server,
then I would just set connection pooling on for your app, and then just make
the requests as they come in. SQL Server will handle the concurrency issues
(as far as updating the same tables at the same time, but not making sure
that changes don't overwrite each other, that's up to you).

There are two aspects of the subject:
1) 2+ requests with 1 SQL statement each
2) 2+ requests with 2+ SQL statements each

#1 is handled fine by all databases I know about out of the box.

#2 is not not generally handled by any database I know of. Depending
on the SQL statements various solution exists (often involving
a high transaction isolation level).

I do not see SQLServer as being special.

Arne

 

[Nicholas Paldino [.NET/C# MVP]]

I made the assumption that the OP was using an RDBMS like SQL Server,
but the statement applies for any RDBMS. It will handle multiple update
requests that come in at the same time to the server. Things such as
Access, FoxPro, Excel etc, etc, have to be handled differently, as they do
not scale well when hit with many requests at the same time (the
drivers/providers just aren't up for it).

If the OP was using Oracle, mySql, the same applies.

Any database will handle #2 fine. The OP wasn't clear about how he
wants sequential statements handled. If a database couldn't handle
sequential statements, then I would be pretty wary of it, wouldn't you?
He/she didn't mention if he wanted it in a transaction or not, but from the
original post, my impression was that the OP was concerned about the
database handling that many updates/requests at a time.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I made the assumption that the OP was using an RDBMS like SQL Server,
but the statement applies for any RDBMS. It will handle multiple update
requests that come in at the same time to the server. Things such as
Access,

Access should handle multiple updates fine. At least consistent.
Performance will not be good.

Any database will handle #2 fine. The OP wasn't clear about how he
wants sequential statements handled. If a database couldn't handle
sequential statements, then I would be pretty wary of it, wouldn't you?

??

It is basic knowledge that logic like:

x = SELECT MAX(id) FROM table
INSERT INTO table VALUES(x+1,'foobar')

is not generally safe.

Arne

 

[Nicholas Paldino [.NET/C# MVP]]

Arne,

Try having more than 4-5 concurrent requests to an access database and
see what happens. It most definitely doesn't scale well.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Try having more than 4-5 concurrent requests to an access database and
see what happens. It most definitely doesn't scale well.

I believe you.

But poor performance is not the same as data corruption.

Arne