[SQL] is an explicit lock necessary?
Hi, Does beginning a transaction put locks on the tables queried within the transaction? In the example below, is #2 necessary? My thought was that I would need to use an explicit lock to make sure that the sequence value I'm selecting in #4 is the same one that is generated from #3. I'm worried about another instance of the application doing an insert on table1 between #3 and #4. 1) From my app, I turn off autocommit. 2) I lock table1 in access exclusive mode 3) I do an insert into table1 which generates a primary key via nextval on sequence1 4) I grab grab the primary key value via currval on sequence1 5) I do an insert on table2 which includes table1's primary key so I can join the records later. 6) I manually commit Thanks! Ash __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] insert related data into two tables
You need to manage the transaction: begin a transaction, execute your first insert, retrieve the ID with curr_val(), execute the sencond insert and commit the transaction. --- [EMAIL PROTECTED] wrote: > Hello, > > I have two tables like these: > > TABLE_1: people registry > fields: ID_T1, SURNAME, NAME > ID_T1 is primary key > > TABLE_2: work groups > fields: ID_T2, TASK > ID_T2 is foreign key related to ID_T1 > > the first table is the list of employees, the second > the task. > > Sometime I need to insert a new employee and at the > same time the task > for him: > I want to INSERT TO table 1 JOHN DOE (the ID is > assigned automatically > since it's a primary key) and INSERT TO table 2 the > ID and the task > name for JOHN DOE. Is it possible to create a single > query? What is the > best way to do this? > > Thanks, > > Filippo > > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] concurrency problem
>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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] 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 <[EMAIL PROTECTED]> 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
> <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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 t
