Re: Deadlocks

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.

"Chuck Hamilton" <chuck_hamilton_at_hotmail.com> wrote in message news:9hsmma$g3p1k$1_at_ID-85580.news.dfncis.de...
> 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
> chuck_hamilton_at_hotmail.com
>
>
Received on Sat Jul 21 2001 - 16:24:27 CDT