[EMAIL PROTECTED] wrote:
Hi Serge!
We also have the database table locking problem with Jahia on Oracle, when using transactions. For now it happened one time when incrementing the counter in the "jahia_pages_prop" table and another time when saving the field definitions and getting the "jahia_fields_def_prop" counter from the "jahia_autoids" table.
For us using Oracle is a necessary requirement, so there is no option to switch to another database.
It is difficult to get all scenarios to analyze in which sequence the database statements are called. We are now in the process of understanding your database design. The first looks lead to some questions:
1.) Why don't you use any primary key and foreign key constraints?
We will introduce some in version 4.1 of Jahia, but we also have problems with these since they inforce a deletion order, which is not compatible with the current JDBC code in Jahia.
2.) Jahia nearly always does a DELETE-INSERT instead of an UPDATE! Why is that?
Old habits die hard :) Actually Jahia doesn't necessarily know which objets are present in RAM and/or in the database.
3.) Instead of using transactions you simply use autoCommit="true" and synchronize on the JVM level. What if Jahia is running in a cluster? You will need a special "distributed JVM" to be able to do it like you do. Otherwise you will get very strange behaviour as the delete is commited and then the insert is commited. When another machine in the cluster reads in between, you will normally get inconsistencies.
It is recommended to avoid thread synchronizing as much as possible. It would be surely better to use database transactions.
Don't get me wrong, I completely agree with you. Jahia's backend is now quite old (some parts haven't changed since their creation in 2000 !), and I am looking at ways to revamp it. To give you an insight I am currently evaluating going to a OR mapping system such as Hibernate or OJB. This would give us : transaction support, object locking, caching, etc.. all in one.
4.) Could you imagine adding special support for Oracle SEQUENCES (incremental keys), while for other databases you still use the generic solution?
I really woulnd't want to introduce anything Oracle specific, unless it is in a "pluggeable" fashion.
5.) Do you have some design documents, where you show the sequence of database statements or do we have to get everything out with the SQL profiler?
Sorry we don't have that. Going through the SQL Profiler is an idea.
6.) Did you try to fix the deadlock problems, when they occured? Is there maybe a quick solution to it? Would it be too much work to fix it and will the database structure have major changes in the near future, so it is not worth to invest into fixing the deadlock problem now?
No honestly this happen very close to the release of 4.0.4 and we didn't have time to find out what was going on. For 4.0.5 we didn't have transaction support planned so it's not going to happen in this version either, but you'd be more than welcome if you want to look into it, so that we can include this in the CVS HEAD version.
Regards, Serge Huber.
Regards, Benjamin Papez
Serge Huber <[EMAIL PROTECTED]> wrote on 02.09.2004 10:08:56:
>
> Hi Benjamin,
>
> Actually we did implement transactions but never activated them because
> we were having table locking problems with some databases (SQL Server if
> I remember correctly). So the system is there but we couldn't activate
> it by default. Also it hasn't been tested recently and there might be
> other problems.
>
> There is no requirement for the external workflow to activate this
> functionality, it was just not ready to be activated by default, that's all.
>
> Regards,
> Serge Huber.
>
> [EMAIL PROTECTED] wrote:
>
> >
> > We are running Jahia in JBoss and we develop our own add-ons. In a
> > previous thread
> > (http://www.mail-archive.com/[EMAIL PROTECTED]/msg00238.html) I asked
> > a question about running the Jahia AddContainer database statements
> > and our custom database access (via EJBs and Hibernate called from a
> > Jahia EventListener) under one single transaction. I have got no
> > answer to that question, but I continued to make some changes in our
> > local Jahia on my own.
> >
> > I saw that you support JDBC transactions, which can be configured with
> > db_transactions in jahia.properties. Instead of the JDBC transactions
> > I introduced using JTA UserTransactions in Jahia. In the
> > ConnectionDispenser I added some code to get the UserTransaction
> > object from JNDI and then begin the transaction before getting a
> > datasource connection in getConnection(). In abortConnection() I do a
> > rollback and in terminateConenction() I do a commit on that
> > transaction. This way the transaction is considered when calling EJBs
> > and all can run under one transaction and it looks like it works.
> >
> > I just wanted to know if there are some reasons, why db_transactions
> > is set to "false" on default? Do you know of any problems in Jahia, if
> > transactions are switched ON? Would it be possible to add JTA support
> > to Jahia officially instead or in addition to the JDBC transactions (I
> > made it configurable in our solution)?
> >
> > Does this requirement play into the domain of the Workflow Engine from
> > Codeva and will they provide a solution to integrate custom business
> > methods in AddContainer all running under one transaction?
> >
> > Greetings,
> > Benjamin Papez
>
>
