lds wrote: > 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.
I assume it's a problem with locks generated from the selects running in isloation level read commited. If such a select collides with a exclsuive lock from an insert SAP DB generates a share lock for this row after the exclusive lock is released. You could check the output of "select * from lockstatistics" at the deadlock situation to see who is waiting on what. To avoid this problem you could run in autocommit mode or send explicite a commit after each select. With 7.4.3.10 (it's now available on www.sapdb.org) we changed this behaviour so the select shouldn't create a share lock on those rows. Kind regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
