Re: Multithread app performs concurrent command and transaction execution

Hi,

Pls. see below:

"Lynne" <lrandolph_at_swri.org> wrote in message news:4eb4a26b.0406161357.49fc66dd_at_posting.google.com...
> I have a multi-threaded application which has a single oracle
> connection. We're using C#.NET and OracleClient. Many of the threads
> perform database updates and an exception was being thrown when a
> transaction was executing and another thread tried to execute a
> command.

Please elaborate. The update transactions should rather wait in the case of a conflict, not fail with an error message, unless you are using SERIALIZABLE isolation level.

>So, I attempted to put locking around all of the functions
> which were performing queries.

This is sort of defeating any kind of concurrency. Essentially, you've converted the DB to the one-user-at-a-time mode, no ?

> I placed a variable in the connection
> class (we have wrapped all of the OracleClient classes with generics
> in case the database needs to change) which I am trying to use to
> lock.

Arguably, with a database, Java/C# 'generics' is almost a sure way to a failed project due to dramatic differences in concurrency implementation amongst different vendors. SQL dialect pecularities is perhaps the second serious consideration that cannot be abstracted away with generics. Here, the problem might be severely degraded performance (in comparison to writing the DB code/SQL so that it benefited from DB specific features). Performance issues can be solved, partially, by brute force, by adding more hardware in order to make the generic solution work.

Unfortunately, today, various database products are so different that perhaps only implementing db interaction in stored procedures would be a[the only] reasonably 'generic' approach.

.
>
> However, what happens is the first thread gets in and starts the
> transaction fine, but when it attempts to execute the first command,
> the thread basically stops. Any ideas how to handle this issue? Or

You need to understand Oracle's (or any other DB's) concurrency model before even starting to *design* the application. You cannot just rush directly to coding and hope it'll somehow work.

Please describe your transaction schedules/patterns in as many detail as necessary to understand what kind of behaviour you expect from the application. Then, you can probably get a more 'specific' answer (as opposed to a 'generic' one ;)

VC

>
> Lynne
Received on Wed Jun 16 2004 - 17:32:06 CDT