Thread: Postgres concurrency : urgent
Hi, I'm having a postgres database accessed from perl using DBI and DBD. This application works fine when we are in single user. When we go into multi-user, the app has concurrency problems. The application has a sql query and updates onto the base table in the sql query. We are using Select FOR UPDATE to open the cursor and multiple update statements to update values on different base tables. When multiple users are running the program, after a few iterations postmaster startsputting out messages that there are concurrency problems due to which the execute before the fetch fails. What I want to do is to wait for the other transactions updates to get over and retry opening the cursor. This does not happen. The moment I try to open the cursor again (using execute), postmaster says that there is no connection !! If I try and reconnecting again it starts giving pqreaddata() problems. After sometime server crashes !! We are using postgres 6.5.1, DBI-1.13, DBD 0.92 Please somebody help. Thanks and regards, Kimi
On Fri, 12 Nov 1999, V Krishnaraj wrote:
> Hi,
>
> I'm having a postgres database accessed from perl using DBI and
> DBD.
>
> This application works fine when we are in single user. When we
> go into multi-user, the app has concurrency problems.
>
> The application has a sql query and updates onto the base table in
> the sql query. We are using Select FOR UPDATE to open the
> cursor and multiple update statements to update values on different
> base tables. When multiple users are running the program, after a
> few iterations postmaster startsputting out messages that there are
> concurrency problems due to which the execute before the fetch
> fails.
> What I want to do is to wait for the other transactions updates to
> get over and retry opening the cursor. This does not happen.
> The moment I try to open the cursor again (using execute),
> postmaster says that there is no connection !!
>
> If I try and reconnecting again it starts giving pqreaddata() problems.
> After sometime server crashes !!
>
> We are using postgres 6.5.1, DBI-1.13, DBD 0.92
>
> Please somebody help.
>
> Thanks and regards,
>
> Kimi
I 'm using :
LOCK <<tablename>> IN ACCESS EXCLUSIVE MODE;
to control concurrent transactions - it works for me
(postgres 6.5.2)
************************************
Marcin Inkielman
************************************
.~.
/V\
// \\
/( )\
^`~'^
powered by Linux
----
sibi omino similis?
************************************
V Krishnaraj wrote: > > This application works fine when we are in single user. When we > go into multi-user, the app has concurrency problems. > > The application has a sql query and updates onto the base table in > the sql query. We are using Select FOR UPDATE to open the > cursor and multiple update statements to update values on different FOR UPDATE cursors are not implemented, yet. How could you use them? > base tables. When multiple users are running the program, after a > few iterations postmaster startsputting out messages that there are > concurrency problems due to which the execute before the fetch > fails. What messages? Vadim
Marcin Inkielman wrote:
>
> I 'm using :
>
> LOCK <<tablename>> IN ACCESS EXCLUSIVE MODE;
^^^^^^
This blocks concurrent read access - is it what you really want?
> to control concurrent transactions - it works for me
> (postgres 6.5.2)
Vadim
Hi,
Thanks for looking into the problem immediately.
I'll answer all questions that have been raised
<bold>1. What error message does it put out</bold>
I'm getting 2 messages. First message is 'ERROR: Can't
serialize access due to concurrent update'. This is perfect. I am
trying to access already locked rows.
In such a case I want to wait for other transactions to complete.
Whenever I got the message, I put a wait, and retry,
programatically. I'm reopening the cursor programatically every 1
second, until the cursor open suceeds.
When I do this, it waits and goes thru the first few times.
After a few times, there lots of messages are spewed out in the
postgres server log. A typical message looks like this.
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally a
nd possibly corrupted shared memory.
I have rolled back the current transaction and am going to
terminate you
r database system connection and exit.
Please reconnect to the database system and repeat your
query.
2. I'm surprised that select for update is not implemented. I looked
at the manual and it says this is implemented in v 6.5.1. So also
my programs are behaving consistent with Select for Update
(Giving conccurent access message on already locked rows).
3.I'm not sure whether what I want is
<color><param>7F00,0000,0000</param>> > LOCK <<<<tablename>> IN ACCESS EXCLUSIVE MODE;
</color>I'll have to test this.
<bold>What exactly I want is,</bold> I want the selects on transactions to wait
till the main locking transaction updates and commits. I want to
queue all requests in a serialzed fashion.
Thanks and regards,
Kimi
Date sent: Fri, 12 Nov 1999 08:25:15 +0700
From: Vadim Mikheev <<vadim@krs.ru>
Organization: OJSC Rostelecom (Krasnoyarsk)
To: Marcin Inkielman <<marn@wsisiz.edu.pl>
Copies to: V Krishnaraj <<kimi@intercept.co.in>, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Postgres concurrency : urgent
<color><param>7F00,0000,0000</param>> Marcin Inkielman wrote:
> >
> > I 'm using :
> >
> > LOCK <<<<tablename>> IN ACCESS EXCLUSIVE MODE;
> ^^^^^^
> This blocks concurrent read access - is it what you really want?
>
> > to control concurrent transactions - it works for me
> > (postgres 6.5.2)
>
> Vadim
>
> ************
>
>
<nofill>
V Krishnaraj wrote: > > After a few times, there lots of messages are spewed out in the postgres > server log. A typical message looks like this. > NOTICE: Message from PostgreSQL backend: > The Postmaster has informed me that some other backend died abnormally a > nd possibly corrupted shared memory. > I have rolled back the current transaction and am going to terminate you > r database system connection and exit. > Please reconnect to the database system and repeat your query. Typical messages are not interest ones -:) Please find what backend exited with !0 code and was there message before that or not. If there was no message about some exceptional conditions then try to re-compile with CASSERT enabled (configure --enable-cassert) or post to us gdb' output for .../data/base/_your_database_/core file. > 2. I'm surprised that select for update is not implemented. I looked at > the manual and it says this is implemented inv 6.5.1. So also my > programs are behaving consistent with Select for Update (Giving > conccurent access message on already locked rows). Well, FOR UPDATE is not implemented for server side cursors. But works for SELECT. > 3.I'm not sure whether what I want is > > > LOCK <<tablename>> IN ACCESS EXCLUSIVE MODE; > I'll have to test this. > What exactly I want is, I want the selects on transactions to wait till > the main locking transaction updates and commits. I want to queue all > requests in a serialzed fashion. Vadim
On Fri, 12 Nov 1999, Vadim Mikheev wrote:
> > LOCK <<tablename>> IN ACCESS EXCLUSIVE MODE;
> ^^^^^^
> This blocks concurrent read access - is it what you really want?
>
I know it is rather restrictive but when applied to a sequence it inhibits
to a unterminated transaction to change sequence current value (with
for example SELECT nextval('seq')) - ROLLBACK doesn't revert
previous sequence curr. value.
(sorry for my English)
************************************
Marcin Inkielman
************************************
.~.
/V\
// \\
/( )\
^`~'^
powered by Linux
----
sibi omino similis?
************************************
Marcin Inkielman wrote:
>
> On Fri, 12 Nov 1999, Vadim Mikheev wrote:
>
> > > LOCK <<tablename>> IN ACCESS EXCLUSIVE MODE;
> > ^^^^^^
> > This blocks concurrent read access - is it what you really want?
> >
> I know it is rather restrictive but when applied to a sequence it inhibits
> to a unterminated transaction to change sequence current value (with
> for example SELECT nextval('seq')) - ROLLBACK doesn't revert
> previous sequence curr. value.
How do I lock a sequence?
I must be able to rollback to previous sequence curr.value, if the
transaction fails to work.
I have been looking this for ages, it seems I haven't found the answer
yet.
Regards,
Chai
On Fri, 12 Nov 1999, Chairudin Sentosa Harjo wrote:
> How do I lock a sequence?
> I must be able to rollback to previous sequence curr.value, if the
> transaction fails to work.
> I have been looking this for ages, it seems I haven't found the answer
> yet.
>
I use it like this:
BEGIN;
LOCK any_seq IN ACCESS EXCLUSIVE MODE;
SELECT any_seq.last_value+1...
INSERT into ... values(any_seq.last_value+1,....)
UPDATE ... set ...=any_seq.last_value+1...
...
SELECT nextval(any_seq);
END;
now I am sure that all SELECT, INSERT , ...
use exactly the same value of any_seq.last_value+1 and when any operation
fails my any_seq remains unchanged. I am also sure that only one
transaction that modify any_seq is running.
***********************************
Marcin Inkielman
************************************
.~.
/V\
// \\
/( )\
^`~'^
powered by Linux
----
sibi omino similis?
************************************