Hi all,
I am having quite serious problem with my SAP DB installation.
Here is the description:
I have the following table:
CREATE TABLE entitytable (id bigint PRIMARY KEY,orgid bigint, statsid bigint, entityid
bigint, creationdate timestamp,lastmodified timestamp)
The bigint is defined as FIXED(21.0) through CREATE DOMAIN statement.
There are indexes defined for the orgid, statsid and entityid fields.
The logic that I have includes the following select
(1)SELECT id FROM entitytable WHERE orgid=? AND statsid=? AND entityid=?
If there is no row that satisfies to the condition a new row is inserted into the
database.
(2)INSERT INTO entitytable VALUES(<all the values here>)
By my mistake I haven't put the composite unique index on the orgid, statsid and
entityid columns (that is obviously required to avoid the
duplicates in the case of the concurrent inserts).
I am using the READ_COMMITED isolation level.
At some point of time I experiencied the _infinite_ blocking while running the query
(1).
There were about 1700000 rows in the table by that moment.
I have 8 sessions that performs this logic (SELECT INSERT IF NOT FOUND)
_concurrently_.
I realised that the actual problem seems to be with access to the indexes defined on
the entitytable.
_ANY_ operation that touches these indexes (even "explain select * from entitytable
where entityid = 1017") blocks infinitely.
I can not even drop them. Any select that doesn't touch the indexes works fine (like
"select * from entitytable where rowno < 10")
Below is extract from the results of the "show regions" command for this database:
I put here only the regions with high collision rate that I believe might be relevant
for this problem.
I guess that TREE<X> regions are used to synchronize the access to the indexes and
there is
too high collision rate for some of them. Another thing that I noticed is the
incorrect results for the region
named "SERVER". I have no ideas if it is relevant or not but I believe it might be.
8 DIAGCACH 11669 0 2833 20 0 24.28 %
21 SERVER 824 0 966 7 0 117.23 %
42 TREE1 16435488 1778 22824 305 0 0.14 %
43 TREE2 912286784 2489656 26090985 5005 0 2.86 %
44 TREE3 388132938 10065860 94011223 9467 0 24.22 %
45 TREE4 506792095 1105799 15039456 1513 0 2.97 %
46 TREE5 2455953461 70625252 739469642 85672 0 30.11 %
47 TREE6 87831789 17484 194812 3970 0 0.22 %
48 TREE7 2568070566 90683915 930402058 134552 0 36.23 %
49 TREE8 311133142 3478939 36104204 798 0 11.60 %
I appreciate any advice that allows to solve this problem with deadlock while
accessing the indexes.
This problem is reproduceable but not very easily.
Thanks in advance.
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
