Hi Tom

Can we have the same query for an update of a different field other than primary key . I chnage the trigger for before update of field name but session waiting and I am not getting ORA-00054


create table flight_cap
2 (FLT_NO VARCHAR2(5),
3 FLT_DATE DATE,
4 CAPACITY NUMBER,
cap_id number);

insert some rows...


create or replace trigger test_trg
before update of capacity on flight_cap
for each row
declare
l_lock_id number;
resource_busy exception;
pragma exception_init (resource_busy, -54);
begin

if (dbms_lock.request(id=>:new.cap_id ,
lockmode => dbms_lock.x_mode,
timeout => 1,
release_on_commit => TRUE)=1)
then
raise resource_busy;
end if;
end;


update flight_cap
set capacity = capacity - 10;

What I am looking for is that an update should not wait if any of the row is locked by another session .. Similar to

select for update now wait
update flight_cap ...


Rgds
Anil

Hi Tom,

I have to populate two tables which have artificial keys as PKs. I was able to load the first one but the second one gave me this message

ERROR at line 1:
ORA-01400: cannot insert NULL into ("FDMS"."ADDRESSES"."DEPO_SEQ")
ORA-06512: at line 51

Please help.

Hi,

I didn't give you enough info to my question yesterday, let me explain more. The two table are Depositors(parent) and Addresses (child). They both have SEQ # as their PKs. I loaded Depositors and it has the SEQ# already. Now when I loaded Addresses I got the can not insert null error message. I think my question is how do I match SEQ# in Addresses to SEQ# in Depositors. Ex: Seq#2 in Addresses should match with Seq#2 in Depositors.

Thanks

Hi,
I think you can do the following:
Find out all the rows in the parent table which do not have a referencing key in the child table.
Then using that sequence number you can do a normal insert into the child table.
Hope I am correct.

Dear Tom,
I exactly have the same case as you have started this post with. I have two transactions hitting the table at the same time and there is a primary key violation (though very rarely as the primary key is defined on 7 fields). And the reason the primary key is defined this way is for a set of uniquie combination of values we want to have a single record in the table so that for reporting purposes its easier to pullout the data rather than summing up the data on run time.

So, the logic defined in the procedure is try inserting first, if it fails (check for DUP_VAL_ON_INDEX) then update. But in this case as both the transactions hit the db at the same time for inserts the 2nd transaction will not succeed as it does not have visibility to the uncommited trasaction by first session. For the 2nd transaction neither the insert nor the update succeeds. So, finally one transaction commits and the other is returned back
with a dead lock error and also says there is a unique constraint violation.

By some means, I want the second transaction to wait till the first transaction succeeds and then go for an update. As you were suggesting that we can take out the primary key constraint in the trigger and handle this duplication logic from the trigger, is there any way to raise an exception of resource busy for the 2nd transaction and wait till such time that the first transaction succeeds and then make an attempt to update for the 2nd transaction?

I guess a more appropriate approach would be is rather than insert first and update later, do the reverse, but still it can run into the same dead lock situation.

Can you please help me to figure out a solution for this? I'm eagerly looking for your response

My table structure

glentry(CNY#, RECORD#, STATUS, ENTRY_DATE, BATCH#, DOCUMENT, DESCRIPTION, ACCOUNT#, AMOUNT, UNITS, CURRENCY, USER#, LOCATION#, DEPT#)

basegltotals(CNY#, ACCOUNT#, LOCATION#, DEPT#, TIMEPERIOD, CURRENCY, AMOUNT, DEBIT, CREDIT, BOOKID)
PRIMARY KEY (CNY#, BOOKID, ACCOUNT#, LOCATION#, DEPT#, TIMEPERIOD, CURRENCY)

--Trigger on glentry
TRIGGER GLENTRY_DO_GLTOTALS AFTER DELETE OR INSERT OR UPDATE
OF RECORD# ,ENTRY_DATE ,BATCH# ... plus some other fields
ON GLENTRY

if inserting or updating then
acct_utils.updategltotalsinsert(:new.cny#, :new.entry_date, my_amount, :new.account#, :new.location#,
:new.dept#, :new.adj, :new.timeperiod, :new.batch#, :new.currency, my_trx_amount);

--The procedure updategltotalsinsert does the insertion into basegltotals
PROCEDURE updategltotalsinsert (
a_cny# IN glentry.cny#%TYPE,
a_entry_date IN glentry.entry_date%TYPE,
....
)
BEGIN
INSERT INTO basegltotals
(cny#, bookid, account#,
location#, dept#,
timeperiod, amount, debit, credit,
adjdebit, adjcredit, currency
)
VALUES (a_cny#, rec.bookid, a_account#,
NVL (a_location#, 0), NVL (a_dept#, 0),
my_time_period, a_amount, my_debit, my_credit,
my_adjdebit, my_adjcredit, basecurr
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE basegltotals gltotals
SET gltotals.amount = gltotals.amount + a_amount,
gltotals.debit = gltotals.debit + my_debit,
gltotals.credit = gltotals.credit + my_credit,
gltotals.adjdebit = gltotals.adjdebit + my_adjdebit,
gltotals.adjcredit =
gltotals.adjcredit + my_adjcredit
WHERE gltotals.cny# = a_cny#
AND gltotals.bookid = rec.bookid
AND gltotals.account# = a_account#
AND gltotals.location# = NVL (a_location#, 0)
AND gltotals.dept# = NVL (a_dept#, 0)
AND gltotals.timeperiod = my_time_period
AND gltotals.currency = basecurr;
WHEN OTHERS
THEN
RAISE;
END;

Adding more information to the above post

From the production queries that have failed for such cases, here is a scenario how it happened.. The first session posts entry into basegltotals to account 1000 followed by account 2000. The second second session posts to account 2000 followed by account 1000 (reverse of one).

If the first session transaction does not commit before the second session transaction begins, you will get a deadlock condition as transaction one waits for journal entry two to release the lock on account 2000 and vice versa.

The solution may be to order the account# in the same order for both transactions. But that is a difficult
situation as in a given transactions there can be lots of sets of entries which are typically coupled in pairs
as debits and credits and if we try ordering by account# before posting the line_no storage values in the glentry table would be messed up.

What I claimed was not quite true, line_no just maintains the sequence of entries for a given transaction, its just that the total debits should match the total credits.

Are you saying that ordering the account# in both sessions would solve this problem?

If its so, then not only that I've to get the account# in order but all the other fields on which there is a unique constraint needs to be ordered, which means it requires a lot more processing and can potentially slow down the system.

How do you suggest to handle this, is there a better way?

Do you think this approach of updating first and if it fails then insert will help?

BEGIN

UPDATE basegltotals gltotals
SET gltotals.amount = gltotals.amount + a_amount
.....
WHERE gltotals.cny# = a_cny#
.......

IF SQL%ROWCOUNT == 0 THEN
INSERT INTO basegltotals
(cny#, bookid, account#..
)
VALUES (a_cny#, rec.bookid, a_account#...
);
...
END IF;

END;

It makes sense to update first then insert, because there are lesser chances of update failing when compared to an insert as once a row is present, you will always want to update than insert.

Thinking again with the same kind of inserts, first session inserts into basegltotals to account 1000 followed by account 2000. The second second session inserts to account 2000 followed by account 1000 (reverse of one). In this case, since we are trying to update first, will one set of transaction wait for the transaction from the other session (insert) to complete?