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