pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConcurrency problem
21+ messages / 11 participants
[nested] [flat]
* Concurrency problem
@ 2000-02-04 11:33 Taral <taral@taral.net>
0 siblings, 1 reply; 21+ messages in thread
From: Taral @ 2000-02-04 11:33 UTC (permalink / raw)
To: pgsql-sql
I have a small problem, and was wondering if there was a better fix than
the one I have.
Here goes: I have tables A and B, both with primary keys. A has a field
which refers to rows of B, and vice versa. When these fields are modified,
I want to ensure referential integrity, but without using triggers. Is it
possible to avoid deadlock and preserve integrity in all situations
without serializing the updates?
Details: Without any locking, an update of A concurrent with a delete on B
will cause an integrity breach. With locking, concurrent updates of A and
B can cause a deadlock...
Taral
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] Concurrency problem
@ 2000-02-04 12:10 Jan Wieck <wieck@debis.com>
parent: Taral <taral@taral.net>
0 siblings, 1 reply; 21+ messages in thread
From: wieck@debis.com @ 2000-02-04 12:10 UTC (permalink / raw)
To: Taral <taral@taral.net>; +Cc: pgsql-sql
> I have a small problem, and was wondering if there was a better fix than
> the one I have.
>
> Here goes: I have tables A and B, both with primary keys. A has a field
> which refers to rows of B, and vice versa. When these fields are modified,
> I want to ensure referential integrity, but without using triggers. Is it
> possible to avoid deadlock and preserve integrity in all situations
> without serializing the updates?
>
> Details: Without any locking, an update of A concurrent with a delete on B
> will cause an integrity breach. With locking, concurrent updates of A and
> B can cause a deadlock...
You cannot use a regular trigger because of your circular
dependency. What you really need to insure integrity is the
FOREIGN KEY support coming with 7.0. It is implemented as
triggers too, but they are handled a little different by the
trigger manager and their execution can be delayed until
COMMIT.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] Concurrency problem
@ 2000-02-04 12:47 Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 1 reply; 21+ messages in thread
From: Gerhard Dieringer @ 2000-02-04 12:47 UTC (permalink / raw)
To: " <"<pgsql-sql@postgresql.org>
Jan Wieck <wieck@debis.com> wrote:
> You cannot use a regular trigger because of your circular
> dependency. What you really need to insure integrity is the
> FOREIGN KEY support coming with 7.0. It is implemented as
> triggers too, but they are handled a little different by the
> trigger manager and their execution can be delayed until
> COMMIT.
I am also waiting for foreign key support so my question is:
When will version 7.0 be available for download?
Thank you
-------------
Gerhard
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] Concurrency problem
@ 2000-02-04 13:43 Jan Wieck <wieck@debis.com>
parent: Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 0 replies; 21+ messages in thread
From: wieck@debis.com @ 2000-02-04 13:43 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: " <" <pgsql-sql@postgreSQL.org>
>
> Jan Wieck <wieck@debis.com> wrote:
>
> > You cannot use a regular trigger because of your circular
> > dependency. What you really need to insure integrity is the
> > FOREIGN KEY support coming with 7.0. It is implemented as
> > triggers too, but they are handled a little different by the
> > trigger manager and their execution can be delayed until
> > COMMIT.
>
> I am also waiting for foreign key support so my question is:
>
> When will version 7.0 be available for download?
Up to now we're planning to go BETA on February 15th. But I'm
not 100% sure if it's me this time to delay BETA. There are
some important items still open on our internal project TODO.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] Concurrency problem
@ 2000-02-04 19:31 Taral <taral@taral.net>
parent: Jan Wieck <wieck@debis.com>
0 siblings, 1 reply; 21+ messages in thread
From: Taral @ 2000-02-04 19:31 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; +Cc: pgsql-sql
On Fri, 4 Feb 2000, Jan Wieck wrote:
> You cannot use a regular trigger because of your circular
> dependency. What you really need to insure integrity is the
> FOREIGN KEY support coming with 7.0. It is implemented as
> triggers too, but they are handled a little different by the
> trigger manager and their execution can be delayed until
> COMMIT.
Does the CVS version have this support?
Taral
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] Concurrency problem
@ 2000-02-04 19:44 Jan Wieck <wieck@debis.com>
parent: Taral <taral@taral.net>
0 siblings, 0 replies; 21+ messages in thread
From: wieck@debis.com @ 2000-02-04 19:44 UTC (permalink / raw)
To: Taral <taral@taral.net>; +Cc: Jan Wieck <wieck@debis.com>; pgsql-sql
> On Fri, 4 Feb 2000, Jan Wieck wrote:
>
> > You cannot use a regular trigger because of your circular
> > dependency. What you really need to insure integrity is the
> > FOREIGN KEY support coming with 7.0. It is implemented as
> > triggers too, but they are handled a little different by the
> > trigger manager and their execution can be delayed until
> > COMMIT.
>
> Does the CVS version have this support?
Mostly. Look at
http://www.PostgreSQL.org/projects/
to get a clue about the syntax. And follow the TODO link on
http://www.PostgreSQL.org/~wieck/
to see what we still need to fix/finish until BETA.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 21+ messages in thread
* Concurrency problem
@ 2004-10-03 10:58 Dag Gullberg <dag.gullberg@telia.com>
0 siblings, 1 reply; 21+ messages in thread
From: Dag Gullberg @ 2004-10-03 10:58 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a problem with concurrency,
where ordinary selects render me
Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
/home/site/PHP/db_func.php on line 301
Code of get_rights:
GRANT SELECT ON c.users TO
adm,w3;
SELECT rights INTO result
FROM c.users
WHERE usr_id=uid;
REVOKE ALL ON c.users FROM w3,adm;
RETURN result;
What function is in error at a specific point in time appears to
be random. There are no "update" SQL commands issued between two loads
(actually two meny selections in sequence, the first not being served
completely). Still the db complain about "updates". I use triggers only
at "create" and in some instances "update", not when doing "select".
Tables are interconnected by foreign keys, sometimes mutliple.
Is there anybody out there with similar experiences?
Someone who might point in some directions in terms of
documentation of *how* to design and use a database to
avoid concurrency problems?
Any thoughts on this are greatly appreciated.
/Dag
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Concurrency problem
@ 2004-10-03 17:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dag Gullberg <dag.gullberg@telia.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2004-10-03 17:15 UTC (permalink / raw)
To: Dag Gullberg <dag.gullberg@telia.com>; +Cc: pgsql-sql
Dag Gullberg <dag.gullberg@telia.com> writes:
> Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
> CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
> /home/site/PHP/db_func.php on line 301
> Code of get_rights:
> GRANT SELECT ON c.users TO
> adm,w3;
> SELECT rights INTO result
> FROM c.users
> WHERE usr_id=uid;
> REVOKE ALL ON c.users FROM w3,adm;
> RETURN result;
Do you have a bunch of clients doing this in parallel? If so, the
problem is probably coming from two instances of the function trying to
update the catalog entry for c.users at the same time. The approach
is fundamentally broken anyhow, because there is nothing stopping
client 2 from revoking the rights in between client 1 doing his GRANT
and his SELECT.
The right way to do what you seem to want (allow only this function to
access the table) is not to flap the rights settings back and forth
like that. Grant SELECT rights to a specific userid and make the
function be owned by that userid and be labeled SECURITY DEFINER.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Concurrency problem
@ 2004-10-04 11:35 Dag Gullberg <dag.gullberg@telia.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 21+ messages in thread
From: Dag Gullberg @ 2004-10-04 11:35 UTC (permalink / raw)
To: pgsql-sql
Hi Tom,
Yes, I have several clients connecting to the db, using the same
username, doing the same things, pretty much.
Please educate me:
When a table is accessed, is there an entry that is updated in
pg_catalog.pg_tables (or somewhere else) in such a fashion that the MVCC
cannot handle it and that consecutive selects from other clients will
cause concurrency errors? Are these entries not "locked" while accessed?
What's more in your response is very clear and it is amazing how easy it
is to slip into singel user reasoning, if not careful, when writing
code for a multi user environment. Its a blunder, pure and simple. I
stand corrected (and wiser). Thanks!
/Dag
(apologies for sending this to you private address first)
sön 2004-10-03 klockan 19.15 skrev Tom Lane:
> Dag Gullberg <dag.gullberg@telia.com> writes:
> > Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
> > CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
> > /home/site/PHP/db_func.php on line 301
>
> > Code of get_rights:
> > GRANT SELECT ON c.users TO
> > adm,w3;
> > SELECT rights INTO result
> > FROM c.users
> > WHERE usr_id=uid;
>
> > REVOKE ALL ON c.users FROM w3,adm;
> > RETURN result;
>
> Do you have a bunch of clients doing this in parallel? If so, the
> problem is probably coming from two instances of the function trying to
> update the catalog entry for c.users at the same time. The approach
> is fundamentally broken anyhow, because there is nothing stopping
> client 2 from revoking the rights in between client 1 doing his GRANT
> and his SELECT.
>
> The right way to do what you seem to want (allow only this function to
> access the table) is not to flap the rights settings back and forth
> like that. Grant SELECT rights to a specific userid and make the
> function be owned by that userid and be labeled SECURITY DEFINER.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Concurrency problem
@ 2004-10-04 11:39 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 21+ messages in thread
From: Magnus Hagander @ 2004-10-04 11:39 UTC (permalink / raw)
To: Dag Gullberg <dag.gullberg@telia.com>; pgsql-sql
> Yes, I have several clients connecting to the db, using
> the same username, doing the same things, pretty much.
>
> Please educate me:
> When a table is accessed, is there an entry that is updated
> in pg_catalog.pg_tables (or somewhere else) in such a fashion
> that the MVCC cannot handle it and that consecutive selects
> from other clients will cause concurrency errors? Are these
> entries not "locked" while accessed?
No, but the GRANT statement is translated into doing an UPDATE on a
system catalog. The concurrency error only happens when you do UPDATEs,
you can do any number of parallell SELECTs. If you wrote the SQL
directly you would use SELECT FOR UPDATE, but with GRANT I don't think
there is a way to make it use that semantic.
And no, without using FOR UPDATE, rows are not locked when they are read
in MVCC. That's what's so great about it :-)
//Magnus
^ permalink raw reply [nested|flat] 21+ messages in thread
* concurrency problem
@ 2006-06-16 04:14 sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
0 siblings, 3 replies; 21+ messages in thread
From: sathish kumar shanmugavelu @ 2006-06-16 04:14 UTC (permalink / raw)
To: pgsql-sql
Dear group,
Let me explain my issue.
We use
Database - postgresql-8.1
JDBC Driver - postgresql-8.1-407.jdbc3.jar
Java - jdk1.5
The default transaction isolation level is - Read Committed
Auto Commit is false
In our application we used a single connection object. We open the
connection in the MDI form and close it only when the MDI closes , simply
when the application closes.
I give a insert statment like
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,?,?,?,?,?,?,?,?,?,?)
The point to note here is the select statement which gets the max
entry_no and adds one to it and save the new value. entry_no is the primary
key of the above table.
Now i run the same program (different instance) from two systems, save
the form simultaneously, only one entry is saved, in the other system the
error says - duplicate key violates.
If i use the transaction level - Serializable - again one entry is saved.
Only on closing this application (closing the connection) the application
running in other system is getting saved.
If i lock the table and create a transaction - by sending the commands
con.createStatement().executeUpdate("begin");
con.createStatement().executeUpdate("lock table
rcp_patient_visit_monitor");
int rows = psSave.executeUpdate();
con.createStatement().executeUpdate("commit");
The form in one system is saved, in another system an error says - '
Deadlock detected .....'
When i test the above said commands in dbvisualizer from two different
systems , it works, but here it does not. why.
how to solve this concurrency problem.
Thanks in advance,
--
Sathish Kumar.S
SpireTEK
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-16 06:28 Richard Huxton <dev@archonet.com>
parent: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
2 siblings, 0 replies; 21+ messages in thread
From: Richard Huxton @ 2006-06-16 06:28 UTC (permalink / raw)
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>; +Cc: pgsql-sql
sathish kumar shanmugavelu 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,?,?,?,?,?,?,?,?,?,?)
>
> The point to note here is the select statement which gets the max
> entry_no and adds one to it and save the new value. entry_no is the primary
> key of the above table.
>
> Now i run the same program (different instance) from two systems, save
> the form simultaneously, only one entry is saved, in the other system the
> error says - duplicate key violates.
BEGIN;
LOCK TABLE ...
INSERT ...
COMMIT;
You'll need to handle possible errors where one client fails to get a
lock and times out. It won't happen often, but you do need to consider
the option.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 21+ messages in thread
* concurrency problem
@ 2006-06-16 13:39 sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: sathish kumar shanmugavelu @ 2006-06-16 13:39 UTC (permalink / raw)
To: pgsql-sql
Dear all,
I tried the lock table option today.
yes it works fine when saving simultaneously.
but after two or three times even when all of us close our application,
the lock exists there in the database. when we run the select query from
other db tool it hangs up.
our code looks like
Statement stmt = con.createStatement();
while(true){
try{
stmt.execute("begin");
stmt.execute("lock table rcp_patient_visit_monitor");
break;
}catch(SQLException e){
stmt.execute("commit");
}
}
psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("commit");
if saved then
con.commit(); is called
if not saved then
con.rollback(); is called
First we run in two system, we did not faced the problem, then we run in
three system the record is saved, after that we simulate the run once again,
we face the problem,
i could not predict the problem precisely - what might be the problem.
thanks in advance
- Show quoted text -
On 6/16/06, Richard Huxton <dev@archonet.com> wrote:
sathish kumar shanmugavelu 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,?,?,?,?,?,?,?,?,?,?)
>
> The point to note here is the select statement which gets the max
> entry_no and adds one to it and save the new value. entry_no is the
primary
> key of the above table.
>
> Now i run the same program (different instance) from two systems, save
> the form simultaneously, only one entry is saved, in the other system the
> error says - duplicate key violates.
BEGIN;
LOCK TABLE ...
INSERT ...
COMMIT;
You'll need to handle possible errors where one client fails to get a
lock and times out. It won't happen often, but you do need to consider
the option.
--
Richard Huxton
Archonet Ltd
--
Sathish Kumar.S
SpireTEK
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-16 14:00 Aaron Bono <postgresql@aranya.com>
parent: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
2 siblings, 0 replies; 21+ messages in thread
From: Aaron Bono @ 2006-06-16 14:00 UTC (permalink / raw)
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>; +Cc: pgsql-sql
I would use a BIGSERIAL for the ID. It simplifies your inserts, you don't
have to mess with any locking and the sequence is maintained for you outside
your transaction so two transactions can do inserts without stepping on each
other.
This is how I handle auto generated numbers.
The only downside is if an insert fails for some reason - then a number will
be skipped. You would have to have some really restrictive requirements for
this to matter though.
-Aaron Bono
On 6/15/06, sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
wrote:
>
> Dear group,
> Let me explain my issue.
> We use
> Database - postgresql-8.1
> JDBC Driver - postgresql-8.1-407.jdbc3.jar
> Java - jdk1.5
> The default transaction isolation level is - Read Committed
> Auto Commit is false
> In our application we used a single connection object. We open the
> connection in the MDI form and close it only when the MDI closes , simply
> when the application closes.
> I give a insert statment like
> 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,?,?,?,?,?,?,?,?,?,?)
>
> The point to note here is the select statement which gets the max
> entry_no and adds one to it and save the new value. entry_no is the primary
> key of the above table.
>
> Now i run the same program (different instance) from two systems, save
> the form simultaneously, only one entry is saved, in the other system the
> error says - duplicate key violates.
>
> If i use the transaction level - Serializable - again one entry is
> saved. Only on closing this application (closing the connection) the
> application running in other system is getting saved.
>
> If i lock the table and create a transaction - by sending the commands
> con.createStatement().executeUpdate("begin");
> con.createStatement().executeUpdate("lock table
> rcp_patient_visit_monitor");
> int rows = psSave.executeUpdate();
> con.createStatement().executeUpdate("commit");
>
> The form in one system is saved, in another system an error says - '
> Deadlock detected .....'
>
> When i test the above said commands in dbvisualizer from two different
> systems , it works, but here it does not. why.
>
> how to solve this concurrency problem.
>
> Thanks in advance,
> --
> Sathish Kumar.S
> SpireTEK
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-16 14:08 Ash Grove <ash_grv7@yahoo.com>
parent: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
2 siblings, 1 reply; 21+ messages in thread
From: Ash Grove @ 2006-06-16 14:08 UTC (permalink / raw)
To: pgsql-sql
>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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-16 14:33 Aaron Bono <postgresql@aranya.com>
parent: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Aaron Bono @ 2006-06-16 14:33 UTC (permalink / raw)
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>; +Cc: pgsql-sql
I know this is a Java issue but I would recommend something more like:
Statement stmt = con.createStatement();
try {
stmt.execute("begin");
stmt.execute("lock table rcp_patient_visit_monitor");
psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("commit");
con.commit(); is called
} catch (Exception e) {
con.rollback(); is called
} finally {
stmt.close();
}
On 6/16/06, sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
wrote:
>
> Dear all,
> I tried the lock table option today.
> yes it works fine when saving simultaneously.
> but after two or three times even when all of us close our application,
> the lock exists there in the database. when we run the select query from
> other db tool it hangs up.
>
> our code looks like
> Statement stmt = con.createStatement();
> while(true){
> try{
> stmt.execute("begin");
> stmt.execute("lock table rcp_patient_visit_monitor");
> break;
> }catch(SQLException e){
> stmt.execute("commit");
> }
> }
> psSave.executeUpdate(); //psSave is a prepared statement
> stmt.execute("commit");
>
> if saved then
> con.commit(); is called
> if not saved then
> con.rollback(); is called
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-17 03:53 sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
parent: Ash Grove <ash_grv7@yahoo.com>
0 siblings, 2 replies; 21+ messages in thread
From: sathish kumar shanmugavelu @ 2006-06-17 03:53 UTC (permalink / raw)
To: Ash Grove <ash_grv7@yahoo.com>; +Cc: pgsql-sql
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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Sathish Kumar.S
SpireTEK
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-17 14:52 Aaron Bono <postgresql@aranya.com>
parent: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Aaron Bono @ 2006-06-17 14:52 UTC (permalink / raw)
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>; +Cc: Ash Grove <ash_grv7@yahoo.com>; pgsql-sql
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,?,?,?,?,?,?,?,?,?,?)
> >
> >
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-17 22:24 Ash Grove <ash_grv7@yahoo.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 21+ messages in thread
From: Ash Grove @ 2006-06-17 22:24 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>; +Cc: Ash Grove <ash_grv7@yahoo.com>; 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 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-18 21:24 Aaron Bono <postgresql@aranya.com>
parent: Ash Grove <ash_grv7@yahoo.com>
0 siblings, 0 replies; 21+ messages in thread
From: Aaron Bono @ 2006-06-18 21:24 UTC (permalink / raw)
To: Ash Grove <ash_grv7@yahoo.com>; +Cc: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>; pgsql-sql
Looks good but you really shoud put your stmt.close() and conn.close() in a
finally block so even if there is an error everything gets cleaned up
properly. That syntax is for Java but the principle is the same for any
programming language - always make sure you clean up your connections no
matter what errors occur.
-Aaron Bono
On 6/17/06, Ash Grove <ash_grv7@yahoo.com> wrote:
>
> 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
> }
> }
> }
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: concurrency problem
@ 2006-06-19 15:28 Andrew Sullivan <ajs@crankycanuck.ca>
parent: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Andrew Sullivan @ 2006-06-19 15:28 UTC (permalink / raw)
To: pgsql-sql
On Sat, Jun 17, 2006 at 09:23:17AM +0530, sathish kumar shanmugavelu wrote:
> 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
Don't do that. Fetch the number from a sequence first: select
nextval(). Then you can insert it as currval() for all your other
INSERTs. No, there is no race condition or concurrency problem: see
the docs on these functions. No locks.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2006-06-19 15:28 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-04 11:33 Concurrency problem Taral <taral@taral.net>
2000-02-04 12:10 ` Jan Wieck <wieck@debis.com>
2000-02-04 19:31 ` Taral <taral@taral.net>
2000-02-04 19:44 ` Jan Wieck <wieck@debis.com>
2000-02-04 12:47 Re: [SQL] Concurrency problem Gerhard Dieringer <DieringG@eba-haus.de>
2000-02-04 13:43 ` Jan Wieck <wieck@debis.com>
2004-10-03 10:58 Concurrency problem Dag Gullberg <dag.gullberg@telia.com>
2004-10-03 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-04 11:35 ` Dag Gullberg <dag.gullberg@telia.com>
2004-10-04 11:39 Re: Concurrency problem Magnus Hagander <mha@sollentuna.net>
2006-06-16 04:14 concurrency problem sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
2006-06-16 06:28 ` Re: concurrency problem Richard Huxton <dev@archonet.com>
2006-06-16 14:00 ` Re: concurrency problem Aaron Bono <postgresql@aranya.com>
2006-06-16 14:08 ` Re: concurrency problem Ash Grove <ash_grv7@yahoo.com>
2006-06-17 03:53 ` Re: concurrency problem sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
2006-06-17 14:52 ` Re: concurrency problem Aaron Bono <postgresql@aranya.com>
2006-06-17 22:24 ` Re: concurrency problem Ash Grove <ash_grv7@yahoo.com>
2006-06-18 21:24 ` Re: concurrency problem Aaron Bono <postgresql@aranya.com>
2006-06-19 15:28 ` Re: concurrency problem Andrew Sullivan <ajs@crankycanuck.ca>
2006-06-16 13:39 concurrency problem sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
2006-06-16 14:33 ` Re: concurrency problem Aaron Bono <postgresql@aranya.com>
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