Re: concurrency problem

Locks are released when the containing transaction
commits. There is no explicit "release."

Instead of calling "begin" and "commit" as statements,
I do something more like below. As Aaron mentioned,
this is JDBC, not SQL. Sorry people.

try {
...
conn.setAutoCommit(false);

//do the insert on the table that generates the
primary key via a sequence
PreparedStatement pstmt =
conn.prepareStatement("my prepared statement");
pstmt.executeUpdate();

//your prepared statement above should do an
//insert on a table that calls nextval().
//Calling currval() below will guarantee that
you'll get
//the value created by the insert statement
//Check out the documentation on sequence
functions

//get the new primary key
String get_pkey = "{ ? = call currval('my_seq')
}";
CallableStatement = conn.prepareCall(get_pkey);
cstmt.registerOutParameter(1, Types.BIGINT);
cstmt.execute();

long new_pkey = cstmt.getLong(1);

//do all of your updates/inserts on tables using
new_pkey as a foreign key
//I like to do this in batches
Statement stmt = conn.createStatement();
stmt.addBatch("insert into... )
stmt.addBatch("update whatever set... )
stmt.executeBatch();

conn.commit();

stmt.close();
conn.close();

} catch(SQLException e1) {
//do something with error 1
if (conn != null) {
try {
conn.rollback();
} catch(SQLException e2) {
//do something with error 2
}
}
}

--- Aaron Bono <postgresql(at)aranya(dot)com> wrote:

> When in this situation I:
>
> 1. Wait until I have enough data to do a complete
> commit before even
> bothering to save any data to the database. I want
> the life of my
> transactions to last no more than milliseconds if
> possible.
> 2. Use a BIGSERIAL for the primary keys so the IDs
> are assigned
> automatically through triggers and sequence IDs.
> 3. Do a "SELECT currval('my_sequence') AS
> seq_number;" to determine what ID
> was assigned so I can use it on child tables.
>
> -Aaron Bono
>
> On 6/16/06, sathish kumar shanmugavelu
> <sathishkumar(dot)shanmugavelu(at)gmail(dot)com>
> wrote:
> >
> > Dear group
> > Its my mistake that i did not reveal the whole
> scenario.
> > Actually within that begin and commit, i
> insert in 10 tables. The
> > above said table is the key table.
> > I fetch the consultatioin_no and add one to it,
> i should know this
> > consultation_no to save the other 10 tables.
> because i use this number as
> > foreign key in other tables. Also in my program,
> the data for that 10 tables
> > are collected in different java classes and save
> coding is also there. I
> > initiate this save coding for all the 10 forms in
> the one form (some main
> > form).
> > so if any error occurs i have to roll back the
> whole transaction.
> >
> > Is there any method to release the lock
> explicitly, where postgres
> > store this locking information.
> > Is both
> > stmt.execute ("commit");
> > con.commit();
> > are both same. should i have to call
> con.commit() method after
> > stmt.execute("commit")
> >
> > Now Iam also thinking to use sequence. but
> please clear the above
> > doubts.
> >
> > --
> > Sathish Kumar.S
> > SpireTEK
> >
> >
> > On 6/16/06, Ash Grove <ash_grv7(at)yahoo(dot)com> wrote:
> > >
> > >
> > >
> > > >INSERT INTO rcp_patient_visit_monitor (
> > > > entry_no, patient_id,
> visit_date,
> > > > is_newpatient,
> > > > visit_type, is_medical,
> > > > is_review, is_labtest,
> is_scan,
> > > > is_scopy, is_xray,
> > > > weight, height)
> > > > VALUES ((SELECT
> > > > coalesce(max(entry_no)+1, 1) FROM
> > > >
> > >
>
rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
> > >
> > > You are only working on one table so you
> sholdn't have
> > > to manage a transaction or deal with explicit
> locking.
> > > Just let the database handle this for you with a
> > > sequence. Your concurrency issues will
> disappear.
> > >
> > > 1) create a sequence:
> > >
> > > create sequence entry_no_sequence
> > >
> > >
> > > 2) set the new sequence's value to your table's
> > > current entry_no value (n):
> > >
> > > select setval('entry_no_sequence',n)
> > >
> > >
> > > 3) recreate your table so that the entry_no will
> get
> > > it's value from calling nextval() on your new
> > > sequence:
> > >
> > > entry_no integer not null default
> > > nextval('entry_no_sequence')
> > >
> > >
> > > Thereafter, when an insert is made on your
> table, the
> > > enry_no field will get its value from the
> sequence and
> > > the sequence will be incremented. You would then
> drop
> > > entro_no from your insert statement and it would
> > > become something like:
> > >
> > > INSERT INTO rcp_patient_visit_monitor (
> > > patient_id, visit_date, is_newpatient,
> visit_type,
> > > is_medical, is_review,
> > > is_labtest, is_scan, is_scopy, is_xray, weight,
> > > height)
> > > VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
> > >
> > >
> >
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com