Date: Thu, 04 Feb 2010 08:32:43 +0100
To: wyx6fox@sina.com, pgsql-performance@postgresql.org
Subject: Re: some problems when i use postgresql 8.4.2 in my
 projects .
From: =?utf-8?Q?Pierre_Fr=C3=A9d=C3=A9ric_Caillau?= =?utf-8?Q?d?=
	<lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <20100203022830.2A375358C60@mail175-236.sinamail.sina.com.cn>
Content-Transfer-Encoding: 7bit
Message-ID: <op.u7ldwtkccke6l8@soyouz>
In-Reply-To: <20100203022830.2A375358C60@mail175-236.sinamail.sina.com.cn>
User-Agent: Opera Mail/9.62 (Linux)


> 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&nbsp; 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.