Deadlocks

Deadlocks

Post by Chuck Hamilto » Thu, 05 Jul 2001 00:00:22



Anyone ever gotten a deadlock detected error where the trace file doesn't
show the row locks causing the deadlock? What causes this type of deadlock.
I had several this morning and here's the deadlock graph from the trace
file.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)------
---
Resource Name          process session holds waits  process session holds
waits
TM-00000afb-00000000        22      24     S             14      43
SX
TM-00000b07-00000000        14      43    SX             22      24
S
session 24: DID 0001-0016-00000002      session 43: DID 0001-000E-00000002
session 43: DID 0001-000E-00000002      session 24: DID 0001-0016-00000002
Rows waited on:
Session 43: no row
Session 24: no row

--
Never share a foxhole with anyone braver than yourself!

Chuck Hamilton

 
 
 

Deadlocks

Post by Chris Weis » Thu, 05 Jul 2001 00:18:08


There could be several reasons why this is occurring.  Here are some that I
have seen:

1)  You have a context index and a function based index on the same column
of a table and are attempting to return several rows from the table in a
cursor.

2)  The same stored procedure is being called by the two different users and
both engage in a long set of DML statements with no commits.  If the stored
procedure does not commit at all, this almost guaranteed to cause a
deadlock.

3)  Same scenario as #2 as a set of ad hoc queries or scripts.  Some times a
user will run a script over and over and over again thinking that somehow
the database issue will go away.

4) You could have a failing component in your system.  A flaky disk or
controller can cause unidentified deadlock.

Possible resolutions:

Scenario 1)  Limit the number of rows returned, or convert the cursors to
bulk collects.

Scenario 2)  Commit more frequently or verify that the stored procedure is
committing.  Relying on auto-commit from a client is never a good thing.

Scenario 3)  Restructure the script to avoid using so many resources at
once.  You can either commit more frequently or re-write the query.

Scenario 4)  Run diagnostics and call support :)

Additional things to look at:

*) Up the resources on your db, including increasing the data block buffers.
*) Partition your data better across your I/O system.  I/O contention can
cause deadlock - especially with context or function based index
*) Set up some sort of tracing on your client sessions so you can better
trace the chain of events leading to this issue.


> Anyone ever gotten a deadlock detected error where the trace file doesn't
> show the row locks causing the deadlock? What causes this type of
deadlock.
> I had several this morning and here's the deadlock graph from the trace
> file.

> Deadlock graph:
>                        ---------Blocker(s)--------  ---------Waiter(s)----
--
> ---
> Resource Name          process session holds waits  process session holds
> waits
> TM-00000afb-00000000        22      24     S             14      43
> SX
> TM-00000b07-00000000        14      43    SX             22      24
> S
> session 24: DID 0001-0016-00000002      session 43: DID 0001-000E-00000002
> session 43: DID 0001-000E-00000002      session 24: DID 0001-0016-00000002
> Rows waited on:
> Session 43: no row
> Session 24: no row

> --
> Never share a foxhole with anyone braver than yourself!

> Chuck Hamilton



 
 
 

Deadlocks

Post by Thomas Kyt » Thu, 05 Jul 2001 02:47:11



>Anyone ever gotten a deadlock detected error where the trace file doesn't
>show the row locks causing the deadlock? What causes this type of deadlock.
>I had several this morning and here's the deadlock graph from the trace
>file.

>Deadlock graph:
>                       ---------Blocker(s)--------  ---------Waiter(s)------
>---
>Resource Name          process session holds waits  process session holds
>waits
>TM-00000afb-00000000        22      24     S             14      43
>SX
>TM-00000b07-00000000        14      43    SX             22      24
>S
>session 24: DID 0001-0016-00000002      session 43: DID 0001-000E-00000002
>session 43: DID 0001-000E-00000002      session 24: DID 0001-0016-00000002
>Rows waited on:
>Session 43: no row
>Session 24: no row

>--
>Never share a foxhole with anyone braver than yourself!

>Chuck Hamilton


Sure, run a script like:

drop table t1 cascade constraints;
drop table c1;
drop table t2 cascade constraints;

create table t1 ( x int primary key, y references t1 );

create table t2 ( x int primary key );

insert into t1 values ( 1, null );
insert into t1 values ( 2, null );

commit;

delete from t1 where x = 1;

prompt in another session issue:
prompt insert into t2 values ( 1 ) ;;
prompt insert into t1 values ( 3, null ) ;;
pause

insert into t2 values ( 1 );
--------------------------------------------------------

and do what the prompt says to do and you'll get a deadlock graph like:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0003001e-00000c35        13       9     X             12       8           S
TM-0000836a-00000000        12       8   SSX             13       9          SX
session 9: DID 0001-000D-00000002   session 8: DID 0001-000C-00000002
session 8: DID 0001-000C-00000002   session 9: DID 0001-000D-00000002
Rows waited on:
Session 8: no row
Session 9: no row

You have some inserts or updates colliding with eachother. (index t1(y) and the
above will not happen)

do you have unindexed foreign keys (#1 cause of deadlocks in my experience). Use





  2       cname1 || nvl2(cname2,','||cname2,null) ||
  3       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  4       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  5       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  6              columns
  7    from ( select b.table_name,
  8                  b.constraint_name,
  9                  max(decode( position, 1, column_name, null )) cname1,
 10                  max(decode( position, 2, column_name, null )) cname2,
 11                  max(decode( position, 3, column_name, null )) cname3,
 12                  max(decode( position, 4, column_name, null )) cname4,
 13                  max(decode( position, 5, column_name, null )) cname5,
 14                  max(decode( position, 6, column_name, null )) cname6,
 15                  max(decode( position, 7, column_name, null )) cname7,
 16                  max(decode( position, 8, column_name, null )) cname8,
 17                  count(*) col_cnt
 18             from (select substr(table_name,1,30) table_name,
 19                          substr(constraint_name,1,30) constraint_name,
 20                          substr(column_name,1,30) column_name,
 21                          position
 22                     from user_cons_columns ) a,
 23                  user_constraints b
 24            where a.constraint_name = b.constraint_name
 25              and b.constraint_type = 'R'
 26            group by b.table_name, b.constraint_name
 27         ) cons
 28   where col_cnt > ALL
 29           ( select count(*)
 30               from user_ind_columns i
 31              where i.table_name = cons.table_name
 32                and i.column_name in (cname1, cname2, cname3, cname4,
 33                                      cname5, cname6, cname7, cname8 )
 34                and i.column_position <= cons.col_cnt
 35              group by i.index_name
 36           )
 37  /

to see if you have any unindexed foreign keys

 Are you using some explicit table lock commands?

--

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

 
 
 

Deadlocks

Post by Ricky Sanche » Thu, 05 Jul 2001 08:52:55


A lack of itl entries in a table can also cause this phenomenon. High
row density with a default 1 value of INITRANS can produce this "no
rows" deadlock if the itl list cannot grow an additional entry and two
transactions are updating blocks from the same table in different
orders.

Fix it by recreating the table with a higher INITRANS.

- ricky


> Anyone ever gotten a deadlock detected error where the trace file doesn't
> show the row locks causing the deadlock? What causes this type of deadlock.
> I had several this morning and here's the deadlock graph from the trace
> file.

> Deadlock graph:
>                        ---------Blocker(s)--------  ---------Waiter(s)------
> ---
> Resource Name          process session holds waits  process session holds
> waits
> TM-00000afb-00000000        22      24     S             14      43
> SX
> TM-00000b07-00000000        14      43    SX             22      24
> S
> session 24: DID 0001-0016-00000002      session 43: DID 0001-000E-00000002
> session 43: DID 0001-000E-00000002      session 24: DID 0001-0016-00000002
> Rows waited on:
> Session 43: no row
> Session 24: no row

> --
> Never share a foxhole with anyone braver than yourself!

> Chuck Hamilton


 
 
 

Deadlocks

Post by Jonathan Lewi » Sat, 07 Jul 2001 05:30:40


Then there's the primary key insert -

User A inserts pk = 'XXX' in table 1

User B inserts pk 'YYY' into table 2

User A inserts pk='YYY' into table 2 - but waits on B
without showing a row wait.

User B inserts pk = 'XXX' into table 1 - deadlock
detected.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.


>A lack of itl entries in a table can also cause this phenomenon. High
>row density with a default 1 value of INITRANS can produce this "no
>rows" deadlock if the itl list cannot grow an additional entry and two
>transactions are updating blocks from the same table in different
>orders.

>Fix it by recreating the table with a higher INITRANS.

>- ricky

 
 
 

Deadlocks

Post by Sanjay Sharm » Fri, 03 Aug 2001 00:00:17


I am running into a deadlock situation and would like to get some suggestions on
how to avoid it. I am using OCI to do the following

Starting OCI transacation (implicit)
//Execute the following sql through OCI
Delete MyTable where id="123"; // Delete multiplr rows
if delete fails OCITransRollback;
Insert INTO MyTable (id, name, val) VALUES ('123', 'n1', 'v1')
if insert fails OCITransRollback;
Insert INTO MyTable (id, name, val) VALUES ('123', 'n2', 'v2')
if insert fails OCITransRollback;
Insert INTO MyTable (id, name, val) VALUES ('123', 'n3', 'v3')
if insert fails OCITransRollback;
else OCITransCommit;

Under heavy load above pesdu methods causes deadlock. Note it is possible that
multiple clients are using the same id to insert and delet rows. I have checked
to make sure I rollback in case of all failures and commit in case of success.

Sanjay

 
 
 

Deadlocks

Post by Galen Boye » Fri, 31 Aug 2001 11:29:18



> I am running into a deadlock situation and would like to get
> some suggestions on how to avoid it. I am using OCI to do the
> following

> Starting OCI transacation (implicit)
> //Execute the following sql through OCI
> Delete MyTable where id="123"; // Delete multiplr rows
> if delete fails OCITransRollback;
> Insert INTO MyTable (id, name, val) VALUES ('123', 'n1', 'v1')
> if insert fails OCITransRollback;
> Insert INTO MyTable (id, name, val) VALUES ('123', 'n2', 'v2')
> if insert fails OCITransRollback;
> Insert INTO MyTable (id, name, val) VALUES ('123', 'n3', 'v3')
> if insert fails OCITransRollback;
> else OCITransCommit;

> Under heavy load above pesdu methods causes deadlock. Note it
> is possible that multiple clients are using the same id to
> insert and delet rows. I have checked to make sure I rollback
> in case of all failures and commit in case of success.

> Sanjay

--
Galen Boyer
It seems to me, I remember every single thing I know.
 
 
 

1. Deadlocked by Deadlocks

I'm new to SQL and even newer to replication so this is really flustrating -
I have set up a merge publication and have successfully set up two push
subscriptions, the only problem is that the replication keeps getting
stopped by Deadlocks (Error 1205).  How do I stop these and can anyone tell
me what these are getting caused by?????

2. Report Smith and IDAPI

3. Deadlock situtation (deadlock victim) - help needed!!

4. add user

5. Your server command (process id 14) was deadlocked with another process and has been chosen as deadlock victim. Re-run your command

6. Recordset.AddNew behavior

7. Deadlock on update

8. (Q) Generating cover letters with FoxPro 2.6 for Mac

9. deadlock and timeout

10. fixing deadlocking even when only single SPID

11. Deadlock involving single select with a join

12. bcp --> deadlock

13. deadlocking