We have multiple programs hitting the database, but we want each
program to have a single access point to the database. It's not a
database intensive program normally, but I can simulate behavior where
two of the threads in the program might want database access
simultaneously.
"VC" <boston103_at_hotmail.com> wrote in message news:<GT3Ac.33276$Hg2.12685_at_attbi_s04>...
> 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 Thu Jun 17 2004 - 11:31:23 CDT
