From: Ash Grove <ash_grv7@yahoo.com>
To: Aaron Bono <postgresql@aranya.com>
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
Cc: Ash Grove <ash_grv7@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: concurrency problem
Date: Sat, 17 Jun 2006 15:24:25 -0700 (PDT)
Message-ID: <20060617222425.42929.qmail@web52508.mail.yahoo.com> (raw)
In-Reply-To: <bf05e51c0606170752qf0efea6m7bd68be8c2d194ab@mail.gmail.com>
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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ash_grv7@yahoo.com, postgresql@aranya.com, sathishkumar.shanmugavelu@gmail.com
Subject: Re: concurrency problem
In-Reply-To: <20060617222425.42929.qmail@web52508.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox