db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre.Tjeldv...@Sun.COM
Subject Re: Derby & webapp devpt - best practice for data integrity
Date Thu, 16 Feb 2006 09:57:53 GMT
pierre madiot <pitonew@yahoo.fr> writes:

> Hello,
>
> we are developing a webapp, that accesses a Derby
> database.
> This webapp, will be deployed on multiple distinct
> webcontainers.
>
> our Database contains a handful of tables, each in the
> form :
> create table T1(
> t1_id int not null GENERATED ALWAYS as identity
> primary key,
> t1_name varchar(100) not null,
> ...etc....
> );
>
> create table T2(
> answer_id int not null GENERATED ALWAYS as identity
> primary key,
> t2_id int not null references T1(t1_id),
> t2_name varchar(400)
> ...etc....
> );
>
> we are facing a double problem :
>
> 1) Websphere 5.1x doesn't support:
>    - getGeneratedKeys() and
>    - preparedStatmement(SQLStr,
> Statement.RETURN_GENERATED_KEYS)
>    Due to this first problem, we are trying to find a
> work arround in a global transaction to insert data
> rows and get the GENERATED keys, and insert in other
> tables related data with that retrieve generated keys.
> We mad the assumption that we could use :
> select max(t1_id) from t1, to get the generated id.
> This could be safe, if the whole jdbc transaction was
> thread synchronized to avoid multithreaded servlet
> conflicts.
> But then we hit the second issue.
>
> 2) as we will deploy our webapp accross multiple
> distinct webcontainers accessing the same backend.
> the jdbc synchroniation safety net, would not be
> effective.
> So it seems that the handling of this database data
> insertion integrity can only be handled at the RDBMS
> server itself. using jdbc only as a trigger to to all
> the work on the target RDBMs.
>
> we would very much appreciate your help, experience,
> pointers to solutions to these 2 problems (considering
> potential RDBMs independance see postscriptum).

First I have to say that I have never used WebSphere or worked with
web apps so I cannot comment on the specifics of your question, and I'm
not sure I understand what you ask. 

Are you asking how to ensure that your tables remain consistent even
if they are accessed by multiple threads in your web app?

If that is the case, the answer is simple: As long as your threads
have their own connection to the database you don't have to do
anything to synchronize access to shared data (in the
database). Except ensuring that you are using an isolation level that
is suitable for your app.

If that is not your question, maybe you can clarify?

-- 
dt


Mime
View raw message