Home » SQL & PL/SQL » SQL & PL/SQL » Deadlock experienced but Blocking lock expected (12.1.0.2)
icon5.gif  Deadlock experienced but Blocking lock expected [message #653844] Wed, 20 July 2016 03:20 Go to next message
epep-pdm
Messages: 3
Registered: July 2016
Junior Member
I have a stored procedure which is called by different sessions/users to delete entries in some tables.
When two sessions/users try to execute the Procedure at the same time, one session terminates with an ORA-00060: Deadlock encountered

But why? To my understand there should be a blocking lock, one session should have to wait until the other has completed the procedure and released the lock on the tables.

Our procedure Looks like:
create or replace PROCEDURE  alpha (  aLocationID IN VARCHAR2) 
AS
BEGIN
delete "SCHEMA_Z".TABLE_A where columnLocation = aLocationID and columnNotChanged = 'true';
delete "SCHEMA_Z".TABLE_B where columnLocation = aLocationID and columnNotChanged = 'true';
delete "SCHEMA_Z".TABLE_C where columnLocation = aLocationID and columnNotChanged = 'true';
delete "SCHEMA_Z".TABLE_D where columnLocation = aLocationID and columnNotChanged = 'true';
delete "SCHEMA_Z".TABLE_E where columnLocation = aLocationID and columnNotChanged = 'true';
delete "SCHEMA_Z".TABLE_F where columnLocation = aLocationID and columnNotChanged = 'true';
delete "SCHEMA_Z".TABLE_G where columnLocation = aLocationID and columnNotChanged = 'true';
commit;
END alpha

What is the Problem here?
Re: Deadlock experienced but Blocking lock expected [message #653846 is a reply to message #653844] Wed, 20 July 2016 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Check foreign keys and bitmap indexes.

Re: Deadlock experienced but Blocking lock expected [message #653874 is a reply to message #653846] Thu, 21 July 2016 01:58 Go to previous messageGo to next message
epep-pdm
Messages: 3
Registered: July 2016
Junior Member
Deleting all foreing keys on that tables did solve the Problem, thank you.

But I still don't understand the "Why".
Can somebody give me a hint?
Re: Deadlock experienced but Blocking lock expected [message #653878 is a reply to message #653874] Thu, 21 July 2016 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Most likely unindexed foreign keys.

Re: Deadlock experienced but Blocking lock expected [message #653881 is a reply to message #653878] Thu, 21 July 2016 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So I strongly suggest you reinstate all those foreign keys you dropped and index them.
Re: Deadlock experienced but Blocking lock expected [message #653891 is a reply to message #653881] Thu, 21 July 2016 06:30 Go to previous messageGo to next message
epep-pdm
Messages: 3
Registered: July 2016
Junior Member
Thanks for the link.
Found a detailed Explanation under:
Concurrency Control, Indexes, and Foreign Keys
Re: Deadlock experienced but Blocking lock expected [message #653896 is a reply to message #653891] Thu, 21 July 2016 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback and link.

Re: Deadlock experienced but Blocking lock expected [message #653902 is a reply to message #653896] Thu, 21 July 2016 12:20 Go to previous message
Solomon Yakobson
Messages: 3079
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 21 July 2016 09:13

Thanks for the feedback and link.


Well, as usual, there are cases where FK index is deadlock cause.

Session 1:

SQL> -- session 1
SQL> drop table child purge
  2  /

Table dropped.

SQL> drop table master purge
  2  /

Table dropped.

SQL> create table master(
  2                      master_id number
  3                     )
  4  /

Table created.

SQL> alter table master
  2    add constraint master_pk
  3      primary key(
  4                  master_id
  5                 )
  6  /

Table altered.

SQL> create table child(
  2                     child_id number,
  3                     master_id number,
  4                     val number
  5                    )
  6  /

Table created.

SQL> alter table child
  2    add constraint child_pk
  3      primary key(
  4                  child_id
  5                 )
  6  /

Table altered.

SQL> alter table child
  2    add constraint child_fk
  3      foreign key(
  4                  master_id
  5                 )
  6      references master
  7  /

Table altered.

SQL> create index child_fk
  2    on child(
  3             master_id
  4            )
  5  /

Index created.

SQL> insert
  2    into master
  3    values(1)
  4  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>

Session 2:

SQL> -- session 2
SQL> insert
  2    into child
  3    values(1,1,1)
  4  /

1 row created.

SQL>

Back to Session 1:

SQL> -- session 1
SQL> delete master
  2    where master_id = 1
  3  /

Session 1 waits. Meanwhile Session 2:

SQL> -- session 2
SQL> insert
  2    into child
  3    values(2,1,2)
  4  /

A second later Session 1:

SQL> -- session 1
SQL> delete master
  2    where master_id = 1
  3  /
  where master_id = 1
        *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource


SQL> 

Why?

bug 2546492

Documentation incorrectly describes locking in case of indexed/unindexed foreign keys. Correct behavior is as follows:

Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table. Further, there will be a ROW-SS lock taken on the child table for any DELETE/UPDATE issued against the parent table when an index on the foreign key column of the child table is defined. There will be a ROW-SX lock taken on the child table for an DELETE issued against the parent table when no index on the foreign key column of the child table is defined and a delete cascade constraint is defined. In both cases, the lock is held until the transaction is committed or rolled back. This change in behaviour was introduced in version 9.2.0 to address problems with parallel DML hanging in certain situations.


Let's see what happens in our case:

1. First insert into child table performed by "Session 2" results in ROW-SS lock on master table regardless if foreign key is indexed or not.
2. DELETE from master performed by "Session 1" finds that there is a lock, put by "Session 1" in Step 1, so it waits for the lock to be released. However, if foreign key is indexed, DELETE will cause a ROW-SS lock on child table.
3. If foreign key is not indexed, second insert into child table performed by "Session 2" does not encounter any locks preventing the insert. However, if foreign key is indexed, it runs into ROW-SS lock on child table put by "Session 1" in Step 2. As a result "Session 2" has to wait for "Session 1" to release Step 2 lock, while "Session 1" has to wait for "Session 2" to release Step 1 lock - and we have a deadlock.

SY.

[Updated on: Thu, 21 July 2016 12:22]

Report message to a moderator

Previous Topic: Getting Parent and Child from the same table & same column
Next Topic: GETTING NULLS WHEN USING PIVOT
Goto Forum:
  


Current Time: Fri Sep 24 04:07:02 CDT 2021