Forum Stats

  • 3,826,571 Users
  • 2,260,666 Discussions
  • 7,897,004 Comments

Discussions

Transaction Deadlock

Pranay Deep
Pranay Deep Member Posts: 55
edited Aug 5, 2015 8:30AM in SQL & PL/SQL

Hi,

I have am employee table named EMP3 with below structure:

CREATE TABLE EMP3

  (

    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,

    "ENAME" VARCHAR2(10 BYTE),

    "JOB"   VARCHAR2(9 BYTE),

    "MGR"   NUMBER(4,0),

    "HIREDATE" DATE,

    "SAL"        NUMBER(7,2),

    "COMM"       NUMBER(7,2),

    "DEPTNO"     NUMBER(2,0),

    "INCOME_TAX" NUMBER(10,0)

  );

insert into emp3 values ('1','abc','clerk','7902','01-oct-2010',800,'','20','');

Then I created a trigger on this table, which updates the EMP3 table itself.

create or replace
trigger CALC_TAX
after insert or update of sal on EMP3
for each row
declare
pragma autonomous_transaction;
begin

update EMP3 set INCOME_TAX = :new.SAL*1.20 where EMPNO = :new.EMPNO;
commit;

end;
/

But when I update EMP3 table

update emp3 set sal=1000 where empno='1';

It givesme error as:

00060. 00000 -  "deadlock detected while waiting for resource"

*Cause:    Transactions deadlocked one another while waiting for resources.

*Action:   Look at the trace file to see the transactions and resources

           involved. Retry if necessary.

Kindly advise . Isn't it possible to update the same table using PRAGMA_AUTONONOUS in trigger.

Thanks

Tagged:
RogerTStew AshtonBluShadow

Best Answer

  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown
    edited Aug 5, 2015 5:51AM Answer ✓
    2913938 wrote:
    
    If I make my trigger as BEFORE, in that case update and commit should happen on EMP3 inside the trigger, before the actual update on EMP3...
    
    

    Before changing the data, you have to lock the row.

    Both transactions are trying to lock the same row.

    "Deadlock" has to do with locks, not with updates.

    Try this code:

    create or replace trigger CALC_TAX 
    after insert or update of sal on EMP3
    for each row
    declare
      pragma autonomous_transaction;
      l_rec emp3%rowtype;
    begin
      select * into l_rec from emp3 where EMPNO = :new.EMPNO for update;
      commit;
    end;
    /
    

    Now do an update: you will get a deadlock, even though the trigger doesn't update anything!

«1

Answers

  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown
    edited Aug 5, 2015 5:54AM

    I am sure this is the first of many replies saying "Don't even think about doing that!".

    You have two transactions going against the same row, the main and the autonomous. The main can't finish until the autonomous does, and the autonomous can't finish because the main has already locked the row. Classic deadlock.

    If you are in version 11.1 or later, just make INCOME_TAX a virtual column. Before 11.1, create a view with INCOME_TAX as a calculated column.

    What country has income tax as 120% of salary?

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    edited Aug 5, 2015 5:04AM
    Stew Ashton wrote:
    
    I am sure this is the first of many replies saying "Don't even think about doing that!".
    
    You have two transactions going against the same row, the main and the autonomous. The main can't finish until the autonomous does, and the autonomous can't finish because the main has already locked the row. Classic deadlock.
    
    If you are in 11.2 or later, just make INCOME_TAX a virtual column. Before 11.2, create a view with INCOME_TAX as a calculated column.
    
    What country has income tax as 120% of salary?
    

    Why not

    :new.income_tax := :new.sal *1.2

    in the trigger?

  • Pranay Deep
    Pranay Deep Member Posts: 55
    edited Aug 5, 2015 5:05AM

    Hi Stew,

    Thanks for the reply. It was just a hypothetical testing

    Will take about the income tax from the next

  • Bakhtiyar
    Bakhtiyar Member Posts: 155
    edited Aug 5, 2015 5:14AM

    Your triger is not valid.When you run update (update emp3 set sal=1000 where empno='1';) your triger run but you have no new value :new.sal and :new.empno

  • Pranay Deep
    Pranay Deep Member Posts: 55
    edited Aug 5, 2015 5:25AM

    Hi Stew,

    If I make my trigger as BEFORE, in that case update and commit should happen on EMP3 inside the trigger, before the actual update on EMP3. But this again gives me the same error. Kindly let me know reason, that why am I not able to do it even by using BEFORE type of trigger.

    Thanks

  • RogerT
    RogerT Member Posts: 1,858 Gold Trophy
    edited Aug 5, 2015 5:30AM

    Basically you DO NOT COMMIT in a trigger. Your before trigger would be:

    create or replace trigger CALC_TAX
    before insert or update of sal on EMP3
    for each row
    declare
    begin

    :new.income_tax := new.sal * 1.2commit;;

    end;
    /

    commit takes place when the changes that fired the trigger commit.

    hth

  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown
    edited Aug 5, 2015 5:54AM
    Paul Horth wrote:
    
    
    Why not
    
    :new.income_tax := :new.sal *1.2
    
    in the trigger?
    
    

    If the OP wants a physical INCOME_TAX column, yes that is the way, but why have a physical representation of a calculated value? Suppose the income tax laws change: would you rather change the table (or view) definition or do a batch update of the whole table?

    Calculated values are contrary to third normal form: see http://database-programmer.blogspot.fr/2008/01/database-skills-third-normal-form-and.html

    If you read the article, the author actually prefers the trigger approach with the view a close second. He doesn't mention Oracle or the possibility of virtual columns. In Oracle from version 11.1 on, I would argue for the virtual column.

    I am not discounting triggers for more complex scenarios where virtual columns are not applicable, though I approach triggers with fear and trembling.

    RogerT
  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown
    edited Aug 5, 2015 5:51AM Answer ✓
    2913938 wrote:
    
    If I make my trigger as BEFORE, in that case update and commit should happen on EMP3 inside the trigger, before the actual update on EMP3...
    
    

    Before changing the data, you have to lock the row.

    Both transactions are trying to lock the same row.

    "Deadlock" has to do with locks, not with updates.

    Try this code:

    create or replace trigger CALC_TAX 
    after insert or update of sal on EMP3
    for each row
    declare
      pragma autonomous_transaction;
      l_rec emp3%rowtype;
    begin
      select * into l_rec from emp3 where EMPNO = :new.EMPNO for update;
      commit;
    end;
    /
    

    Now do an update: you will get a deadlock, even though the trigger doesn't update anything!

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    edited Aug 5, 2015 5:47AM

    Not only is committing in a trigger logically wrong (what happens if the main transaction is rolled back?), but using an autonomous transaction in a trigger is just plain stupid (sorry to be so blunt).

    Paul is correct that something can be simply calculated within the trigger to set the NEW values of columns as it happens, but Stew is also correct that, if the data is something that is a simple calculated value of a column, then there is no point in storing that data as it wastes space storing something that a) can be calculated on the fly when you need to, and b) would need to be updated if the base values update too.

    If such a value is required, then the two main options would be:

    a) create a view on the table that includes the calcuation to provide the calculated value when querying the data

    b) (11g onwards) create a virtual column that provides the calculated value without actually storing it.

    Stew Ashton
  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    edited Aug 5, 2015 8:30AM
    Stew Ashton wrote:
    
    
    Paul Horth wrote:
    
    
    Why not
    
    :new.income_tax := :new.sal *1.2
    
    in the trigger?
    
    
    If the OP wants a physical INCOME_TAX column, yes that is the way, but why have a physical representation of a calculated value? Suppose the income tax laws change: would you rather change the table (or view) definition or do a batch update of the whole table?
    
    Calculated values are contrary to third normal form: see http://database-programmer.blogspot.fr/2008/01/database-skills-third-normal-form-and.html
    
    If you read the article, the author actually prefers the trigger approach with the view a close second. He doesn't mention Oracle or the possibility of virtual columns. In Oracle from version 11.2 on, I would argue for the virtual column.
    
    I am not discounting triggers for more complex scenarios where virtual columns are not applicable, though I approach triggers with fear and trembling.
    
    

    Sorry, didn't mean to suggest it was necessarily the way to go, but in a pre-11.1 system I thought I'd mention it as an option. Completely agree about 3rd normal form.

    Stew Ashton
This discussion has been closed.