Re: some problems when i use postgresql 8.4.2 in my projects .

> 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.