Application freezes upon performing insert - OO4O and Wizards(Archived)

Well, I think I found my problem and this may be useful to someone else ...
Basically,it has to do with transactions. When I did an insert into a table, and then follow it with an insert into another table that required a value from the first table to satisfy a foreign key constraint, it locked. This was because I was using a new OraDatabase object to perform the insert, and established the transactions at the OraSession level. I guess in the 8.1.7 client, this does not work. The OraDatabase object manages transactions on it's own, and if you have more than one they cannot "see" each other. The app waits until the table is free, which of course it will not be until you commit the transactions.
If anyone has any other advice, please let me know.


optimistic locking / versioning on association tables

Is there a way to do optimistic locking on the association table that drives a many:many relationship?
For example, if you had a SITE table and a BUILDING table and then a table called SITE_BUILDING that was used to establish the many:many relationship between SITE and BUILDING, you can set up optimistic locking with a row version on SITE and on BUILDING, but because SITE_BUILDING has no descriptor, there doesn't seem to be a way to do optimistic locking on this table.
How can we get optimistic locking on this association? 
In general if your relationship is being qualified with more information then just the FKs then you probably want to introduce a class to represent the relationship. This would introduce a class and thus a descriptor to allow for the appropriate configuration.
On this specific scenario I am confused. The optimistic version column is used to indicate if the value read from the database has changed by another user prior to the write. In the case of an association in a M:M there is really only the existence or non-existence of the association. It has not mutable values that can change and cause the version to increment. All you would ever see in it being written with an initial value and then when the association is removed from the model the row would be deleted.
Maybe you can clarify what behaviour you are expecting from optimistic locking on the association.
We take advantage of Toplink's row versioning to do logging of changes. When a row is changed, a trigger stores the new value in another table along with the version number. This allows us to review how a row looked at any point in time, and because we collect other information when transactions begin we can also tell who made the change and from which workstation the change was made. It's basically a row auditing system.
So in a case like this, we want to keep a record of who created and destroyed associations. I think I'm starting to see your point though: the version number would always be 1, because you'd never update a row once it had been inserted, and once it had been deleted, the next time it was inserted, it would be a "new" association (if it were an object, it would have a new identity). 
One more related question: suppose you have two UOWs and they both want to delete the same row from an association table like this. One is committed, and then the other.
Despite not explicitly having locking on this table, will Toplink still throw an exception to the second UOW that tries to commit a delete on a row that's already been deleted?

TopLink, use of Oracle Temporary tables

A project using TopLink has the following questions. Could someone help answer them?
Does TopLink support the use of Oracle Temporary tables?
Is there any recommendation regarding the two possible ways of setting them up, see below:
ON COMMIT Setting Description DELETE ROWS This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.
PRESERVE ROWS This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.
We appreciate your assistance.
TopLink has no explicit support for temporary tables but it should be possible. It is tough to answer your question since it really depends upon connection and transaction management for either case. What is your planned configuration TopLink with respect to external versus internal connection pooling as well as the JTA TX management.
I believe you are deploying your applications within WebSphere but would just like to confirm that you are using WAS data sources and transaction management.

Transaction in doubt when deleting single entity

Platform: Win7, VS2012, SL5, NET4.5, DevForce2012, Oracle 11g, ODT 1120320
Simple case; one table with single column numeric primary key. Execute entity query to select one record only by PK. Then mark entity for deletion and call to save changes. Failes with TransactionInDoubtException.
If I try to update the entity instead of delete, then save executes sucessfully. Both SQL statements use a WHERE clause identifying the record by PK value (checked using EFProfiler).
With other entities in the same database deletion is no problem. Also there are no concurrency conflicts, neither is this a distributed transaction. Neither are referantial constraints the issue. Also, I can copy the DELETE statement from EFProfiler into SQL*Plus and it executes prefectly.
I've been banging my head for weeks now, trying to figure out what is going on with this table resulting in an in-doubt transaction. I have also posted this same question to both DevForce and EF forums with no luck.
Any hints or clues as to what I can check or change to get this to work is most welcome. Thanks in advance for any input.

Handling multiple Transactions from website.

Hello experts my question is not really related to a particular problem but more from the perspective of proper approach. I have a website that will be inserting transactions into a table and then calling a procedure to process those transactions. My question is simply how to I prevent transactions from getting intermingle. In other words  every transaction is coming right in and I would like to keep every transaction separate from each other. Now I have been reading about table locks and row locks but I'm not certain this is the answer. Can you guys give me some pointers, tips or book that I can read on transactions handling. My fear is that if two transactions come in at the exact same time one person might get the answer from another persons web session. Simply because they are sharing the same table.Any Replies on this topic would be most appreciated.Thanks in Advance.Miguel
Hi, I'm not clear that your question is related to the Oracle Data Provider for .NET which is the subject of this forum. It may be that the Database forum is a better location: General Database Discussions Having said that, perhaps the chapter on transactions from the Database Concepts guide might be beneficial: A couple of key points about transactions in Oracle Database: - The result of a transaction is not visible to any other session until that transaction is committed.- Readers do not block writers.- Writers do not block readers. Please clarify if I've misunderstood, etc. Regards,Mark
Mark Thanks for taking the time to answer my question I posted the question here since this is the dataprovider I typically use when making calls to the database from a website and my fear is that i don't understand how two simultaneous transactions operating on the same table from the same procedure will not get picked up by another web session. I would like to make oracle kind of setup a transaction que sort of speak, however this is lamest terms speak. I'm trying to understand how to deal with transactions in a table. Let me give you an example. I have one transaction in a table I call a store procedure which will then read the data from a table and start operating on it. however  while working on the transaction another transaction comes in and it calls the stored procedure and now that second call to the procedure is going to start operating on two transactions, not just the transaction that it input into the table. I hope this makes more sense,  i have read about transactions before but I'm not sure I understand how to prevent a stored procedure from operating on only specific rows on a table and not other rows sort of speak.
Hi, Do the rows that are inserted into the "staging" table have a primary or unique key? If so, then the stored procedure would use this key to ensure that only a single "transaction" is processed at a time. If there is no natural unique key then a surrogate key (I generally recommened a sequence) could be used. Apologies if I've misunderstood the situation here! Regards,Mark
Thanks for the answer mark it makes perfect sense.You are the man!

Oracle DEFERRABLE foreign key constraints. has anybody tried this.??

There are some integrity constraints for which Toplink can not resolve the order of inserts. it's not even possible to explicitly call 'addConstraintDependencies' method to define the constraint.
for such cases, I'm planning to make those constraints DEFERRABLE in oracle and send a query ("SET CONSTRAINTS ALL DEFERRED") to database through UnitOfWork so Oracle would try to check integrity constraints at commit time.
do you think this would have a bad impact on performance or some other unexpected results with Toplink?.
There isn't really any performance impacts per se, it's more of a "fail fast" vs "fail later" kind of trade off.
I'm surprised that you're in a situation where you can't specify the constraint dependencies though, I've never run into that situation.
- Don
This is because of a model definition. I explain :
Table A :
aId PK
bId FK to B.bId and NULLABLE
Table B :
bId PK
business wants a NOT READ-ONLY 1-1 relation from A to B but not from B to A in Toplink.
in my implementation, when there is a not read-only relation (in A - B case), I execute the folowing (this is necessary to set the correct dependencies) :
but since B object must be inserted first, I execute the following explicitly :
1 - how would Toplink resolve the dependancy in such a case?
2 - not to take the risk of wrong dependancy resolution by Toplink, I use DEFERRABLE solution. when you set the constraint to be DEFERRED and then send the insert queries to database it doesn't 'fail later' as you said, but can correctly insert the records.
basically this problem arises when there is a NULLABLE foreign key constraint from one table to another and child object (from the constraint point of view) is the root object in the request objects' tree.
If the FK is nullable, why not just always insert B first (which is what TopLink would do without any intervention). Then if there is a relationship then B is written, then A. If there isn't, then just the A is inserted... I don't see the problem.
- Don
Hi Don,
that's the requirement from business guys. they don't want to make two different calls because they want to keep two insertions in the same transaction and that makes sense to me.
what they want to do is :
A a = new A();
// set a properties
B b = new B();
// set b properties
that's the call. when session bean's create method is called, first the root object (a) is processed meaning :
and then iterating on the child objects (ps : Toplink is not allowed to iterate on the child objects itself, there are many reasons for this)
if (a.getB() != null)
when the create method exits, OC4J triggers the commit and Toplink sends the insert queries to the db.
how can you make sure the order so that insertion of B object is performed first.
thanks & regards
If you have A mapped 1-1 to B, and there if a FK in table A to table B, then TopLink will automatically always make sure it inserts B before A.
- Don
thanks Don,
I am sure it will do it. but the problem is I'm not using Toplink as it's supposed to be used. When the session is loaded, I remove all the object relations from the descriptors. and if the relation is NOT read-only, I add the constraint as I mentinoned in the original message. please do not ask me why, there are many reasons.
but in the scenario I mentioned, this solution has failed because the relation is still NOT read-only but Toplink doesn't know about the foreign key anymore because the relation is not there.
coming back to my original question (forget about all this stuff), if you run the following code, how would Toplink behave in such a case you have one classA object and one class B object to be inserted? what would be Toplink's resolution?.
descriptorB.addConstraintDependencies( classA.class );
descriptorA.addConstraintDependencies( classB.class );
thanks & regards.
Not sure, my guess would be last one wins...
- Don