From: Pierre Frédéric Caillaud <lists@peufeu.com>
To: wyx6fox@sina.com
To: pgsql-performance@postgresql.org
Subject: Re: some problems when i use postgresql 8.4.2 in my projects .
Date: Thu, 04 Feb 2010 08:32:43 +0100
Message-ID: <op.u7ldwtkccke6l8@soyouz> (raw)
In-Reply-To: <20100203022830.2A375358C60@mail175-236.sinamail.sina.com.cn>
References: <20100203022830.2A375358C60@mail175-236.sinamail.sina.com.cn>
> when concurrency insert violate the unique constraints , they block each
> other , i test this in oracle10g, has the same behavour. I think this
> may be reasonable because the uqniue check must be the seriazable
> check .
> for resolve this problem , i do the unique check in application as
> possible , but in big concurrency env , this is not good way .
You probably can't do that in the application.
About exclusive constraints :
Transaction A : begin
Transaction A : insert value X
Transaction A : do some work, or just wait for client
...
Meanwhile :
Transaction B : begin
Transaction B : insert same value X
Transaction B : locked because A hasn't committed yet so the exclusive
constraint can't be resolved
Transaction A : commit or rollback
Transaction B : lock is released, constraint is either OK or violated
depending on txn A rollback/rommit.
As you can see, the longer the transactions are, the more problems you
get.
Solution 1 : change design.
- Why do you need this exclusive constraint ?
- How are the unique ids generated ?
- What kind of data do those ids represent ?
- Can you sidestep it by using a sequence or something ?
- Without knowing anything about your application, impossible to answer.
Solution 2 : reduce the transaction time.
- Optimize your queries (post here)
- Commit as soon as possible
- Long transactions (waiting for user input) are generally not such a good
idea
- Anything that makes the txn holding the locks wait more is bad
(saturated network, slow app server, etc)
- Optimize your xlog to make writes & commits faster
Solution 3 : reduce the lock time
Instead of doing :
BEGIN
INSERT X
... do some stuff ...
COMMIT;
do :
BEGIN
... do some stuff that doesn't depend on X...
INSERT X
... do less stuff while holding lock ...
COMMIT;
Solution 4 :
If you have really no control over value "X" and you need a quick reply
"is X already there ?", you can use 2 transactions.
One transaction will "reserve" the value of X :
- SELECT WHERE col = X
ensures row and index are in cache whilst taking no locks)
- Set autocommit to 1
- INSERT X;
inserts X and commits immediately, else cause an error. Lock will not be
held for long, since autocommit means it commits ASAP.
- Perform the rest of your (long) operations in another transaction.
This is a bit less safe since, if the second transaction fails, insert of
X is not rolled back.
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-performance@postgresql.org
Cc: lists@peufeu.com, wyx6fox@sina.com
Subject: Re: some problems when i use postgresql 8.4.2 in my projects .
In-Reply-To: <op.u7ldwtkccke6l8@soyouz>
* 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