an odd problem about unique key when inserting into table [message #318676] |
Wed, 07 May 2008 09:40  |
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 #318745 is a reply to message #318676] |
Wed, 07 May 2008 17:01   |
flyboy
Messages: 1903 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  |
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.
|
|
|