Home » SQL & PL/SQL » SQL & PL/SQL » an odd problem about unique key when inserting into table (oracle,10i,linux)
an odd problem about unique key when inserting into table [message #318676] Wed, 07 May 2008 09:40 Go to next message
liougehooa
Messages: 2
Registered: May 2008
Location: china
Junior Member
my table "t_transfer_suspend" has an unique key(policy_id,suspend_type,collect_pay,policy_type) to avoid some unexpected data.

And we have an script of pl/sql to run on night to insert some data into this table. this script is run by ten sesions, we are sure to let one v_policy_id can be inserted in one session.
This table is only modified by these ten connected sessions when script is running;
our script just like
:
select count(1) into v_count
from t_transfer_suspend t
where and t.policy_id=v_policy_id
and t.suspend_type=v_suspend_type
and t.collect_pay=v_collect_pay;
if v_count<1 then
insert into t_ransfer_suspend values(v_policy_id,v_suspend_type,v_collect_pay,1/*polciy_type*/)
else
return
end if;

Sometimes it will have many errors about this unique key violent insert error ine one seesion and actually it run partial-successfully.
but when we rollback the data and rerun the script again and just found that's all right. and these cases are occured scarcely.

Does it have anything to do with my table created issue?
I use the same tablespace of this uqiue key index and the table.
Re: an odd problem about unique key when inserting into table [message #318738 is a reply to message #318676] Wed, 07 May 2008 16:21 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you use COUNT(1)? Is there a special reason for not using COUNT(*)?

Why IF v_count < 1? Why not simply IF v_count = 0? (I guess you don't expect count to return a negative or decimal number?)

Quote:
we are sure to let one v_policy_id can be inserted in one session
OK, but what about other unique key column values? I, somehow, rather believe Oracle when it says that you are violating unique key constraint than you.

Quote:
when we (...) rerun the script again (...) it's all right
Obviously, something has changed since the last execution.

Quote:
Does it have anything to do with my table created issue?
I don't think so (unless you'd want to drop the unique key constraint or, possibly, recreate it by the mean of including another column(s)).

Quote:
I use the same tablespace of this uqiue key index and the table.
I don't think it has any influence on unique key constraint violation.
Re: an odd problem about unique key when inserting into table [message #318745 is a reply to message #318676] Wed, 07 May 2008 17:01 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
This is one of the basic concepts in Oracle database. It is briefly stated in SET TRANSACTION command description, more deeply in Data Concurrency and Consistency chapter in the Concepts book. There are also very nice articles on AskTom about this behaviour.

As statement-level read consistency is default, each statement sees data from its invocation time. Moreover, the readers do not block each other, so they may work concurrently. So two sessions with the same values may happen in this order:
- session1 calls SELECT - 0 rows found
- session2 calls SELECT - 0 rows found
- session1 calls INSERT - successfully inserted
- session2 calls INSERT - error

There are some possible solutions:
1) lock the table before select - leads to serialization, not very good
2) try to INSERT and ignore DUP_VAL_ON_INDEX exception
Re: an odd problem about unique key when inserting into table [message #318953 is a reply to message #318745] Thu, 08 May 2008 09:02 Go to previous message
liougehooa
Messages: 2
Registered: May 2008
Location: china
Junior Member
thanks Littlefoot's suggestion.
I know COUNT(*) is better than count(1),but it is is an example of codes. than you.

the unique key is: (policy_id,suspend_type,collect_pay,
policy_type).
thanks for flyboy 's suggestions.
In order to get higth performance and avoid concurrecy, we don't lock the table in any ways. We firstly prepared the data to let a policy and related data in one stream which is proccessed only by one session.

And this happened just tiwce after our system runs 4 years. and before evry these jobs run, we restore the system firstly to avoid disaster happens.
We just rollback to the status before those jobs run. In order to avoid these data modified before. we do very strictly check.

"Obviously, something has changed since the last execution". Actually I hope it is what u said and maybe what u said is right. And these things happened just when data is two times than before.

thaks flyboy, I will read the book you suggested. and thanks Littlefoot.
Previous Topic: Organizing series of Dates into two different columns
Next Topic: Wrong arguments error message
Goto Forum:
  


Current Time: Fri Dec 02 20:46:42 CST 2016

Total time taken to generate the page: 0.07029 seconds