Re: Can two sessions be bound in Oracle ?

"ben brugman" <ben_at_niethier.nl> wrote in message news:3f952008$0$245$4d4ebb8e_at_read.news.nl.uu.net...
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3f94ff18$0$246$ed9e5944_at_reading.news.pipex.net...
> > I don't believe that there is any similar feature. However the
likelihood
> of
> > you actually needing it is pretty remote given the different locking
> > mechanism between the two systems.
>
> The need in Oracle for this feature is definitely less than in SQL-server.
>
> > Perhaps you could give an example of why
> > you think you would need it?
>
> Do we need it ? I do not know, at the moment I am looking into
possibilities
> to in using databases under modular design. Here one (or more) databases
are
> used by different software modules. The software modules are aware of each
> other and communicate with each other, but are not aware off how the
module
> handles it's own data. It could be that a master detail relation is split
> over two different modules. One module is responsible for the detail the
> other for the master. If present in the same database, a referential
> constrait will be laid down in the database. On transaction might consist
of
> two modules one responsible one table the other for the other table. Both
> inserting rows depending on each other.
>
> Options to solve this problem.
> 1. Do not define a referential constraint.
> 2. Make sure the order of inserts (update/deletes) is always correct and
> that one transaction finishes before the other begins.
> 3. use the same connection.
> 4. use different connections but use bounded connections.
>
> Offcourse each solution comes with it's problems.
> (Some known, some unknown problems).

It seems to me that Option 5, "do not separate in application logic, entities which are logically related" would apply in the example you give. Orders being processed by the General Ledger module but Order Items by the stock control module (possibly on a different server) seems to me a daft separation.

There is also Option 6 "define a referential constraint but defer it", whih I don't like but might work or Option 2 which seems to me to be what I am trying to say with option 5. In essence the db design should model the business requirements and thus if two objects are related then every where hat they are dealt with (db,app server, presentation) they should also be related.

Of course I'm not an "architect" so what would I know?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************








>

> Each module has a persistency layer which communicates with the database,

> the persistency layer maps the data to real tables and fields.

> Modules are not aware how this persistency layer is implemented. Modules

> communicate with each other with a 'sort off' messaging system and doing

> requests etc. they do not know how the request will be implemented. It

could


> be that something is calculated (not using a database), but the module's

can


> also access its persistency layer which communicates with the RDBMS.

>

> Because I have no experience with this type of modular design and

databases,


> any insight is welcomed by me.

> ====================

>

> What I am trying to prevent in my organisation that the database get's

> modularised, where constraints are not implemented in the database but

> 'only' in the business layer.

> (Things as we insert a child but the mother is not known yet so the field

> must be capable of being/holding NULL, and/or the referential constraint

can


> not be forced by the database, because the relation is only 'filled' at

the


> end of the transaction).

>

> What the developers would like is that they can pospone the enforcing of

> constraints till the moment they say, alle modules are ready.

>

> I would like to meet (over the net) others having the similar

requirements,


> or opinions about the above.

>

>

> ben brugman.

>

>

> >

> >

> > --

> > Niall Litchfield

> > Oracle DBA

> > Audit Commission UK

> > "ben brugman" <ben_at_niethier.nl> wrote in message

> > news:3f94fd54$0$248$4d4ebb8e_at_read.news.nl.uu.net...

> > > Can Oracle coordinate two or more connections, to prevent lock and

> > deadlock

> > > situations between two 'friendly' connections. For example one

> application

> > > having two connections but not wanting locks between them.

> > >

> > > Similar to :

> > >

> > > From the Books on Line from SQL-server :

> > > "

> > > Bound connections allow two or more connections to share the same

> > > transaction and locks. Bound connections can work on the same data

> without

> > > lock conflicts. Bound connections can be created from multiple

> connections

> > > within the same application, or from multiple applications with

separate


> > > connections. Bound connections make coordinating actions across

multiple


> > > connections easier.

> > > "

> > >

> > > thanks for your attention,

> > > ben brugman

> > >

> > >

> >

> >

>

>