| Deadlocks: "Rows waited on" section explanation [message #416075] |
Thu, 30 July 2009 06:24  |
donnadoe
Messages: 2
Registered: September 2007
|
Junior Member |
|
|
I'm dealing with a deadlock issue between two sessions running the same PL/SQL procedure. Both of them are trying to insert rows in the same heap table, which has neither primary key nor unique keys nor foreign keys defined on it. I have ruled out an ITL slot shortage because the statements involved in the deadlock are INSERTs and also because the statistic 'ITL waits' value for the objects involved in the deadlock (table and indexes) is 1 at most. I have ruled out an overlapping of key values (simple row-level locking) as well since the table has no keys of any kind defined on it. Finally, I have also ruled out a freelist contention because the number of process freelists is the default (1).
I only have the bitmap indexes to blame (there are eleven defined on the table). Nevertheless, every single article I have read about this kind of deadlock shows a "Rows waited on" section of the trace file similar to this:
Rows waited on:
Session 35: no row
Session 37: no row
My trace files, however, show that the sessions are waiting for single rows:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00280023-0000044a 27 62 X 123 128 S
TX-00030012-00027729 123 128 X 27 62 S
Rows waited on:
Session 62: obj - rowid = 00013920 - AAATkgAAFAAASr2AAA
(dictionary objn - 80160, file - 5, block - 76534, slot - 0)
Session 128: obj - rowid = 00013920 - AAATkgAAFAAASr2AAA
(dictionary objn - 80160, file - 5, block - 76534, slot - 0)
So my first question is: is this trace file compatible with a bitmap index deadlock?.
Among the trace files generated by Oracle because due to my deadlock issue, I have seen three kind of "Rows waited on" section:
Rows waited on:
Session 62: obj - rowid = 00013920 - AAATkgAAFAAASr2AAA
(dictionary objn - 80160, file - 5, block - 76534, slot - 0)
Session 128: obj - rowid = 00013920 - AAATkgAAFAAASr2AAA
(dictionary objn - 80160, file - 5, block - 76534, slot - 0)
Where the dictionary object 80160 is a partition of a bitmap index defined on the table mentioned before.
Rows waited on:
Session 128: obj - rowid = 0000172F - AAABcvAAEAAAAAAAAA
(dictionary objn - 5935, file - 4, block - 0, slot - 0)
Session 62: obj - rowid = 00013932 - AAATkyAAFAAASkEAAA
(dictionary objn - 80178, file - 5, block - 76036, slot - 0)
Where the dictionary object 80178 is a bitmap index partition and the dictionary object 5935 is a B*tree index defined on another table: the session blocks some rows of this table before trying to run the insert that leads to the deadlock. I don't understand how this two sessions can deadlock since they execute exactly the same PL/SQL block (no way they can hold crossed enqueues... unless I am missing something) and the table where they are trying to insert a row have no keys defined on it.
Rows waited on:
Session 83: obj - rowid = 00000000 - D/////AACAAAFx/AAA
(dictionary objn - 0, file - 2, block - 23679, slot - 0)
Session 85: obj - rowid = 0001396A - AAATlqAAFAAAS6HAAA
(dictionary objn - 80234, file - 5, block - 77447, slot - 0)
Where the dictionary object 80234 is a bitmap index partition and the block 23679 in the file 2 is part of the undo tablespace. This case simply astonishes me: how can the undo tablespace be involved in a deadlock?.
I have also read that the "Rows waited on" section may have invalid information, if deadlock is not row-level (which I think is the case with bitmap indexes), but I can't starting my analysis supposing that the trace file is not right. I don't want to establish blindingly that the problem are the bitmap indexes, either, even if they shouldn't be there.
Can anyone throw some light on all this deadlock stuff?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
