TriggerViollationnError.

Does anyone know if this causes concurrency issues in a trigger and how to fix.

I need to select a production control number from an assigned pool in a table.

AvailableNumbers
----------------
control_no  varchar2(10)


I have an after insert trigger on table C1 that insert a record on T1.
When i selet 3 records to insert into C1, I got an error from the trigger about T1 primary key violation.

Could the 2nd select be happening before the 1st available control number is deleted?

I am also thinking there can multiple sessions that fire the trigger. I tried
select from availablenumber from update
to lock that number so no other session select it but i got an invalid error
"for update of this query is not allowed".



create or replace trigger C1_after_insert
for each row

declare

l_control_no

begin


select to_Char(min(to_number(control_no)))) into l_control_no from availablenumbers;


insert into T1(col1,col2,...) select l_control_no, col1, col2 from T1 where med='DVD';

insert into T3(Col1,col2) select l_control_no, col1,.. from T3 where ...


DELETE from avialablenumbers where control_no=l_control_no;

END;
sam15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
First: Oracle does not allow row-level triggers (those that include the "for each row" clause) to either select from or modify (insert, update or delete) any tables that are related by a foreign key, whether as a parent table or a child table.  So, that is one possible problem you have to be aware of.  There is a multi-step, two trigger work-around for this problem if you need this functionality.

Second: Using a control table to provide a sequential value for a primary key is *NOT* a good idea in a multi-user system.  Oracle offers sequences that can provide sequential numbers in a multi-user situation and avoid the problems caused by using a control table for this.
sam15Author Commented:
I know i is not a good idea bit i dont have option in this case. That is what management wants.

Are you sure this is not allowed. it worked fine for other applications.

I found an issue with SELECT to_char(MIN(to_number(control_no))) in that
it was dropping the leading ZERO if the number is "07892344" and so it was trying to insert and delete "7892344" which is not valid.

is there a way to confir selecting the MIN number without conversion to number?
DO i need to LOCK TABLE availablenumbers before selection to avoid other sessions selecting the numbers before he first session deletes it.


Mark GeerlingsDatabase AdministratorCommented:
Maybe management needs to be educated on how Oracle works best, instead of trying to force Oracle to work they way they think is best  (Yes, show this to your management, it's not you telling them they are wrong, I'm telling them that.)

If your control table really is a varchar2 (or char) value but you want it to be treated as a number, so 7 is less than 10 for example, then yes you need to do a to_number conversion on it.  (But, this is doing two things poorly: first using a control table to provide sequential values; second: using a non-numeric column to hold values that need to be treated as numbers.)  If you need the value to actually be a character value, but left-padded with zeroes, you need to use a format mask with a leading 0 (zero) something like this: to_char(...,'0999999').
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
One additional point to bring up with using a character field to store a number.

It appears as though you are using this as an invoice number or order number or something similar.  How are your users going to search for it?  They are going to have to know to add leading 0s to the number and how many to add.  When a customer calls on the phone, they aren't going to give you leading 0s.

Also, what happens when you exceed the number?  If you use a 7 digit number now and you go over 9,999,999 invoices, then what happens?  In some cases you'll have 1 leading 0 in some cases 2.

I'm still a little confused why this wouldn't work though:

select control_no into l_control_no from (select control_no from availablenumbers order by to_number(control_ no) desc) where rownum = 1 for update of control_no;
Mark GeerlingsDatabase AdministratorCommented:
I agree, a "select ... for update" should work.  Trying to use both "min" and "for update of" in a simple query may cause an error.  (I'm not sure, I've never tried that combination.)  I also usually don't like using a nested select with "rowlevel =1" to limit the results returned (because Oracle is forced to fetch all of the matching records, then discard all but one of them, so this can be a performance problem) but that may give the desired result in this case.
johnsoneSenior Oracle DBACommented:
I don't think the original with min will work because of the functions involved.  Not sure though.

I also am not too thrilled with the subquery and forcing it to pick a row that way, but I cannot think of a different way to do it and still allow the FOR UPDATE.  I think analytic queries would get complicated and may lose the row information for FOR UPDATE needs and negate their use anyway.

As you stated, I believe that a sequence, a number column and a TO_CHAR on the select to pad the 0s is the best solution.
sam15Author Commented:
This set of numbers is issued to us by the client. WE dont hae much control.
They wants us to use these numbers. The first two digita represent the year
01= 2001
07= 2007
99 = 1999

There is no requirement to get the lowest numbers but that is how they are loaded into the table and i wa trying to get the MIN first.. Would oracle select them in order or i guess there is no gurantee?
I was wondering if it is worth it to add another number field for the table and fill it with numbers and sort the list by it before selecting.

I may have tried the select ...for update using MIN or without "for update of control_no". let me try that.
You are saying this should take care of the concurrency issues?

I was thinking of LOCK TABLE in exclusive mode but i guess there is no need for that.
sam15Author Commented:
I tried this sql statement in the trigger.
it compiles fine but on runtine i get an error

ORA-02014: cannot select for update for view with DISTINCT, GROUP BY

select control_no into l_control_no from (select control_no from availablenumbers order by to_number(control_ no) desc) where rownum = 1 for update of control_no;


I also found out that select ...for update does not lock other sessions from reading the number.
It only locks it for updates. so other session can read that same number i beleive.
Even LOCK TABLE for exclusive mode does not lock SELECTS. would not that be a problem
johnsoneSenior Oracle DBACommented:
Select for update will block another select for update.  That is why we want to use it.

Lock table is a possibility.  You need to lock the table before you do the select.  If every process uses that rule that you should be OK.

If you don't care about order, then I suggest using select for update without trying to get the min.  This allows for a little more concurrency than lock table.
sam15Author Commented:
Try this

create table test 9
x   number );

insert into test values (1);
insert into test values (2);
insert into test values (3);
commit;


go to session 1 and do this

select x from test where rownum=1 for update of x;

1

go to session 2 and do this

select x from test where rownum=1 for update of x

it keep waiting - i was expecting t osee the number 2 here since the first record was locked by session 1.



johnsoneSenior Oracle DBACommented:
That is correct.  You are waiting for the lock.  The second session will not continue until the lock is released via commit or rollback.

If the first session commits, then the second will get 2.

If the first session rolls back, then the second will get 1.
sam15Author Commented:
BUt the lock is at the ROW level. Why session 2 has to wait? Cat they get the 2nd avaiable number since the First row has a lock on it.

Would the select for update handle the concurrency control needed in this case for my trigger?
johnsoneSenior Oracle DBACommented:
Session 2 has to wait because it wants to lock the row held by session 1.  At the time of the select that session 2 ran, the row held by session 1 is the row that satisfies the query.  The database is going to wait until it can determine which row to lock.  I would hope the time between one sessions insert and commit would be pretty short.

Because of the locking there shouldn't be an issue with concurrency.
sam15Author Commented:
Geat explanation! you are correct

I did a test where Session 1 selected the ROW where x=1 for update;

then session 2 selected the ROW where x=2 for update

and there was no locking. Every session got the number.

I assume I wont be able to write the trigger such that each session would give you a number that does not have a lock on it to increase scalability so you do not wait for session 1 to release that lock on ROW 1.

IT will wok sort of like Sequence Geenrator. Every SEQ.NEXTVAL wil be unique number for a pool of available numbers. You will never get the same number.
johnsoneSenior Oracle DBACommented:
Yes, it should work fine.

Even with a sequence there is an amount of locking involved to ensure concurrency.  The difference is a latch is used rather than a row lock.  This makes the locking faster.

As long as this is part of one fluid transaction and a commit is done after the insert, you shouldn't see much of the locking that is going on behind the scenes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.