DB Transactions and Insert order

[Joshua Paine]

I'm building my first Helma app using an RDBMS (H2, in this case)--I've
used the XML Object DB before.

I've noticed two things:

1) Helma does not appear to use the database's transactions. I found a
thread about this from 2001 with Hannes offering to implement it and one
person voting it up (as a default-off datasource-selectable
feature)--can I add my upvote? Except that I think here in 2008 most
people not using MySQL know what transactions are and pretty much expect
they'll be used, so maybe it should be default on in a future version?
Even MySQL supports transactions if you use InnoDB tables. I've searched
the source code as best I can, and it doesn't look like this has
actually been added yet. Please let me know if I missed something. The
two advantages of implementing this would be (1) that if you get a DB
error in the middle of a commit, the DB is not left in an inconsistent
state, and (2) often much better performance in the case of bulk object
creation.

2) I designed my DB with some foreign key constraints not expecting
Helma to do anything particular about them but expecting that there
would not be any conflict. However, as I'm bulk importing hierarchical
data, I'm getting foreign key constraint violations. I always create the
parents before their children, so as best as I can tell during commit()
Helma doesn't necessarily insert nodes in the order they were created.
Perusing objectmodel/db/Transactor.java, I see that "dirty" nodes are
kept track of with a HashMap, which of course doesn't guarantee any
particular order when you convert it to an array. Am I reading it right?
Could this be fixed? If I'm reading right, it seems that just using a
LinkedHashMap instead of a HashMap would do the trick.

--
Joshua Paine (midnightmonster)

_______________________________________________
Helma-user mailing list
Helma-user@...
http://helma.org/mailman/listinfo/helma-user

[Maksim Lin for technical support mailling lists]

 

> I've noticed two things:
>
> 1) Helma does not appear to use the database's transactions.
> I found a thread about this from 2001 with Hannes offering to
> implement it and one person voting it up (as a default-off
> datasource-selectable feature)--can I add my upvote? Except
> that I think here in 2008 most people not using MySQL know
> what transactions are and pretty much expect they'll be used,
> so maybe it should be default on in a future version?
> Even MySQL supports transactions if you use InnoDB tables.
> I've searched the source code as best I can, and it doesn't
> look like this has actually been added yet. Please let me
> know if I missed something. The two advantages of
> implementing this would be (1) that if you get a DB error in
> the middle of a commit, the DB is not left in an inconsistent
> state, and (2) often much better performance in the case of
> bulk object creation.

Hmm, I wonder if this is just a matter of turning off "auto-commit" on
the JDBC connection?
I would then imagine the commit on the JDBC conn would go around line
418 of Transactor.java since helmas "own" transaction commit happens
there for the benefit of the XML DB. The tricky thing looks to be
figuring out how to get your hands on teh undedrlying JDBC conn object
in that commit() method of the Transactor class...

Though its been a while since I've done any "raw" JDBC coding so maybe
I'm making it sound easier then it iwould be to implement.

 

> 2) I designed my DB with some foreign key constraints not
> expecting Helma to do anything particular about them but
> expecting that there would not be any conflict. However, as
> I'm bulk importing hierarchical data, I'm getting foreign key
> constraint violations. I always create the parents before
> their children, so as best as I can tell during commit()
> Helma doesn't necessarily insert nodes in the order they were created.
> Perusing objectmodel/db/Transactor.java, I see that "dirty"
> nodes are kept track of with a HashMap, which of course
> doesn't guarantee any particular order when you convert it to
> an array. Am I reading it right?
> Could this be fixed? If I'm reading right, it seems that just
> using a LinkedHashMap instead of a HashMap would do the trick.
>

This on the other hand I think is correct. And I think you are right,
keeping a time ordered list of dirty Nodes pending to be commited to the
DB would be better.

Maks.
_______________________________________________
Helma-user mailing list
Helma-user@...
http://helma.org/mailman/listinfo/helma-user