Re: concurrency problem - Mailing list pgsql-sql

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
statementabove 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("insertinto... )   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@aranya.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.shanmugavelu@gmail.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@yahoo.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