Andre Reitz wrote: > On Wed, 2 Jun 2004 09:55:25 +0200 > "Zabach, Elke" <[EMAIL PROTECTED]> wrote: > > > Andre Reitz wrote: > > > > > > Sorry, but still a little question. > > > > > > Is it possible that "temp" "tab_share" Locks > > > result from: > > > > > > - Joined-Selects on several Tables > > > - DELETE FROM Table WHERE A1=... > > > - SELECT FROM Table WHERE A1=... AND A2 IN (1,2,3,...) > > > > > > (perhaps in combination with Lock Escalation?) > > > > > > > The check of referential constraints during DELETE/UPDATE will cause > TEMP table share locks. > > Everytime? or only sometimes?
EVERYTIME > > > Queries, no matter if join, subselect or the like, will cause TEMP table > locks only with ISOLATION LEVEL 15/2/20/3/30. > > LOCK ESCALATION will cause non-TEMP table locks. > > Thanks, > but what will be done first: > > Will the ROW to be deleted / updated be locked first, > or the TEMP Table Share Locks be set on the referenced tables? > TEMP TABLE LOCK first, then row exclusive Elke SAP Labs Berlin > > Greetings, Andre' > > > > > > > Elke > > SAP Labs Berlin > > > > > > In other words: > > > > > > - The question is: what kind of queries make "temp" "tab_share" Locks? > > > - Or: What kind of Queries make Locks that will be transformed into > "temp" > > > "tab_share" Locks > > > in case of a LOCK ESCALATION? > > > > > > [all in Isolation Level 1] > > > > > > > > > (I looked into our sources, but I cannot imagine where the temp > tab_share- > > > Locks on > > > IQ_ZUO_FB_SA, IQ_ZUO_FB_WF,IQ_ZUO_FB_TF, IQ_ZUO_FB_MA, > > > IQ_ZUO_FB_EA and IQ_ZUO_FB_AV come from...) > > > > > > By the way: what means LOCKSTATE/REQSTATE "temp"? > > > > > > > > > Greetings, Andre' > > > > > > > > > > > > > > > On Tue, 1 Jun 2004 13:18:43 +0200 > > > "Schroeder, Alexander" <[EMAIL PROTECTED]> wrote: > > > > > > > If there are lock escalations, table locks (their nature depending > on > > > the > > > > requested lock kind) exist afterwards. > > > > > > > > There are, as Elke wrote, also other conditions that can cause a tab > > > share lock, so > > > > until you see that an escalation happens when the situation occurs, > you > > > cannot be > > > > sure (at least you cannot be sure without having looked on all SQL > > > statements). > > > > Your dump seems to show a failed escalation. > > > > > > > > The deadlock detection of MaxDB detects only conflicting rows to > some > > > extent and > > > > deepness (as defined by the parameter DEADLOCK_DETECTION). > > > > > > > > All not-detected lock conditions that are impossible to fulfill > > > (including, > > > > but not limited to all failed escalations) are left to be picked up > by > > > > REQUEST_TIMEOUT, i.e. 'detected' by hanging around in a waiting > state > > > too > > > > much time. Take note this is at least 30 seconds. > > > > > > > > In your case, the reading transaction (the one having and wanting > share > > > locks)# > > > > does something on the IQ_ZUO_FB_SA table, and later tries to read > from > > > the > > > > IQ_ZUO_FB_AV table, whereas the writing transaction performs it in > the > > > opposite > > > > order. You may want to look whether you can change the access order > of > > > one > > > > of them, this probably will help a lot. Both transaction had > possibly > > > never > > > > collided without escalation because they do access different rows, > and > > > if > > > > they had accessed the same row and requested row locks, the deadlock > > > would > > > > have been detected. > > > > > > > > If you need to be prepared with handling escalation in a graceful > way > > > for > > > > all participants, the order in which tables are accessed should be > the > > > same > > > > even if the rows accessed are not the same, just like it is usually > done > > > if > > > > you have concurring transactions on the same row. > > > > > > > > If this cannot be done, MAXLOCKS, a reasonable REQUEST_TIMEOUT and > and > > > > possibly explicit lock options on some statements (e.g. WITH LOCK > > > NOWAIT) > > > > are the only ways to work around this problem. > > > > > > > > Regards > > > > > > > > Alexander Schr�der > > > > SAP DB, SAP Labs Berlin > > > > > > > > > -----Original Message----- > > > > > From: Andre Reitz [mailto:[EMAIL PROTECTED] > > > > > Sent: Tuesday, June 01, 2004 11:08 AM > > > > > To: [EMAIL PROTECTED] > > > > > Subject: Re: Pleas help: DEADLOCK ? > > > > > > > > > > > > > > > Sorry, sending my question again: > > > > > > > > > > > > > > > Scenario: > > > > > - We use Isolationlevel 1 > > > > > - We *do not* use any explicit LOCKS > > > > > > > > > > Questions: > > > > > - are the "tab_share" locks caused only by the > > > > > Lock Escalation strategy of sapdb, or is there > > > > > an other possible cause? > > > > > - Does the Dump show a real DEADLOCK? > > > > > - Why is the DEADLOCK not determined by the automatic > > > > > DEADLOCK-Detection? > > > > > > > > > > > > > > > Greetings, and thank you very much in advance. > > > > > > > > > > P.S.: I increased the MAXLOCKS Parameter to 50000. > > > > > The problem is that we have huge peaks in our > > > > > webapplication an so we cannot realy say if the > > > > > problem is solved. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Thu, 27 May 2004 19:18:05 +0200 > > > > > Andre Reitz <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > Hy all, we have a "hang" in our web-based databaseapplication. > > > > > > > > > > > > Something blocks our clients. > > > > > > > > > > > > Please see the queryresult below or in the attachment. > > > > > > > > > > > > Please tell me if my interpretation of the output is correct: > > > > > > > > > > > > - There are Two sessions (different connections: 1809398 > > > > > and 1809399) > > > > > > - Session 1809398 waits for a "row_exclusive" > > > > > > lock on table "IQ_ZUO_FB_SA" for row > "0x00c6193799000000c52633" > > > > > > - Session 1809399 waits for a "tab_share" > > > > > > lock on table "IQ_ZUO_FB_AV" for the complete table > > > > > > - both sessions can never succeed because: > > > > > > * Session 1809398 has already a "row_exclusive" lock on > > > > > "IQ_ZUO_FB_AV" > > > > > > * Session 1809399 has already a "tab_share" lock on > > > > > "IQ_ZUO_FB_SA" > > > > > > - This situation is a "Deadlock" > > > > > > > > > > > > If my interpretation is correct: > > > > > > - What kind of queries could be responsible for the > > > > > "tab_share" locks > > > > > > in Session 1809399? > > > > > > - Why is this deadlock not encountered by the automatic > > > > > deadlockdetection? > > > > > > > > > > > > > > > > > > > > > > > > Greetings, and thank you very much in advance. > > > > > > > > > > > > Andre' > > > > > > > > > > > > P.S.: We use sapdb 7.4.3.27-1 on Linux (rpm distribution) > > > > > > The clients connect with python. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > SELECT * FROM LOCKSTATISTICS; > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > SESSION | TRANSCOUNT | SUB_TRANS | WRITE_TRANS > > > > > | PROCESS | USERNAME | DATE | TIME | TERMID | > > > > > REQTIMEOUT | LASTWRITE | LOCKMODE | LOCKSTATE | > > > > > REQMODE | REQSTATE | APPLPROCESS | APPLNODE | > > > > > OWNER | TABLENAME | TABLEID | ROWIDLENGTH | > > > > > ROWIDHEX | ROWID > > > > > > > > > > > --------+----------------------+-----------+----------------+- > > > > > --------+-----------------+----------+----------+--------+---- > > > > > --------+------------+---------------+-----------+------------ > > > > > ---+----------+-------------+-------------+-----------------+- > > > > > --------------+--------------------+-------------+------------ > > > > > --------------+------------ > > > > > > 1809398 | 46194 | 0 | 0x0000001df4e6 > > > > > | 53 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | 150 | row_exclusive | write | > > > > > NULL | NULL | 28082 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_FRAGEBOGEN | 0x0000000000003c98 | 5 | > > > > > 0x00c6193799 | > > > > > > 1809398 | 46194 | 0 | 0x0000001df4e6 > > > > > | 53 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | 150 | NULL | NULL | > > > > > row_exclusive | write | 28082 | xxxxxxxxxxx | > > > > > XXXXXXXXXXXXXXX | IQ_ZUO_FB_SA | 0x0000000000003cb7 | > > > > > 11 | 0x00c6193799000000c52633 | 193799 > > > > > > 1809398 | 46194 | 0 | 0x0000001df4e6 > > > > > | 53 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | 150 | row_exclusive | write | > > > > > NULL | NULL | 28082 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_AV | 0x0000000000003eb4 | 5 | > > > > > 0x00c6193799 | > > > > > > 1809398 | 46194 | 0 | 0x0000001df4e6 > > > > > | 53 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | 150 | row_exclusive | write | > > > > > NULL | NULL | 28082 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_AK | 0x0000000000003eb5 | 5 | > > > > > 0x00c6193799 | > > > > > > 1809398 | 46194 | 0 | 0x0000001df4e6 > > > > > | 53 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | 150 | row_exclusive | write | > > > > > NULL | NULL | 28082 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_PB | 0x0000000000003eb6 | 5 | > > > > > 0x00c6193799 | > > > > > > 1809399 | 46195 | 0 | NULL > > > > > | 54 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | NULL | tab_share | temp | > > > > > NULL | NULL | 28079 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_SA | 0x0000000000003cb7 | 0 | > > > > > NULL | NULL > > > > > > 1809399 | 46195 | 0 | NULL > > > > > | 54 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | NULL | tab_share | temp | > > > > > NULL | NULL | 28079 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_WF | 0x0000000000003cb8 | 0 | > > > > > NULL | NULL > > > > > > 1809399 | 46195 | 0 | NULL > > > > > | 54 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | NULL | tab_share | temp | > > > > > NULL | NULL | 28079 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_TF | 0x0000000000003cb9 | 0 | > > > > > NULL | NULL > > > > > > 1809399 | 46195 | 0 | NULL > > > > > | 54 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | NULL | tab_share | temp | > > > > > NULL | NULL | 28079 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_MA | 0x0000000000003cba | 0 | > > > > > NULL | NULL > > > > > > 1809399 | 46195 | 0 | NULL > > > > > | 54 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | NULL | tab_share | temp | > > > > > NULL | NULL | 28079 | xxxxxxxxxxx | XXXXXXXXXXXXXXX > > > > > | IQ_ZUO_FB_EA | 0x0000000000003cbb | 0 | > > > > > NULL | NULL > > > > > > 1809399 | 46195 | 0 | NULL > > > > > | 54 | XXXXXXXXXXXXXXX | 20040527 | 00153933 | web2 | > > > > > 4850 | NULL | NULL | NULL | > > > > > tab_share | temp | 28079 | xxxxxxxxxxx | > > > > > XXXXXXXXXXXXXXX | IQ_ZUO_FB_AV | 0x0000000000003eb4 | > > > > > 0 | NULL | NULL > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > > > > > ______________________________________________________________ > > > > > ____________ > > > > > > > > > > > > Als Technologieunternehmen konzipieren und entwickeln wir > > > > > ma�geschneiderte Feedback- und > > > > > > Monitoring-Systeme - wie beispielsweise L�sungen f�r > > > > > Beschwerde- und Ideenmanagement. > > > > > > Mit dem Inquery� Survey Server bieten wir eine der > > > > > leistungsf�higsten Standardl�sungen f�r > > > > > > Online-Umfragen mit dem Schwerpunkt auf der Messung von > > > > > Kundenzufriedenheit an. > > > > > > > > > > > ______________________________________________________________ > > > > > ____________ > > > > > > > > > > > > > > > > > > Inworks GmbH > > > > > > Andre Reitz, Leiter Entwicklung > > > > > > H�rvelsinger Weg 39, 89081 Ulm, Germany > > > > > > Tel +49 (0) 731 / 93807-21 > > > > > > Fax +49(0)731/93807-18 > > > > > > Internet: http://www.inworks.de > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > ______________________________________________________________ > > > > > ____________ > > > > > > > > > > Als Technologieunternehmen konzipieren und entwickeln wir > > > > > ma�geschneiderte Feedback- und > > > > > Monitoring-Systeme - wie beispielsweise L�sungen f�r > > > > > Beschwerde- und Ideenmanagement. > > > > > Mit dem Inquery� Survey Server bieten wir eine der > > > > > leistungsf�higsten Standardl�sungen f�r > > > > > Online-Umfragen mit dem Schwerpunkt auf der Messung von > > > > > Kundenzufriedenheit an. > > > > > ______________________________________________________________ > > > > > ____________ > > > > > > > > > > > > > > > Inworks GmbH > > > > > Andre Reitz, Leiter Entwicklung > > > > > H�rvelsinger Weg 39, 89081 Ulm, Germany > > > > > Tel +49 (0) 731 / 93807-21 > > > > > Fax +49(0)731/93807-18 > > > > > Internet: http://www.inworks.de > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > __________________________________________________________________________ > > > > > > Als Technologieunternehmen konzipieren und entwickeln wir > ma�geschneiderte > > > Feedback- und > > > Monitoring-Systeme - wie beispielsweise L�sungen f�r Beschwerde- und > > > Ideenmanagement. > > > Mit dem Inquery� Survey Server bieten wir eine der leistungsf�higsten > > > Standardl�sungen f�r > > > Online-Umfragen mit dem Schwerpunkt auf der Messung von > > > Kundenzufriedenheit an. > > > > __________________________________________________________________________ > > > > > > > > > Inworks GmbH > > > Andre Reitz, Leiter Entwicklung > > > H�rvelsinger Weg 39, 89081 Ulm, Germany > > > Tel +49 (0) 731 / 93807-21 > > > Fax +49(0)731/93807-18 > > > Internet: http://www.inworks.de > > > > > > > > > > > > -- > > > MaxDB Discussion Mailing List > > > For list archives: http://lists.mysql.com/maxdb > > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > __________________________________________________________________________ > > Als Technologieunternehmen konzipieren und entwickeln wir ma�geschneiderte > Feedback- und > Monitoring-Systeme - wie beispielsweise L�sungen f�r Beschwerde- und > Ideenmanagement. > Mit dem Inquery� Survey Server bieten wir eine der leistungsf�higsten > Standardl�sungen f�r > Online-Umfragen mit dem Schwerpunkt auf der Messung von > Kundenzufriedenheit an. > __________________________________________________________________________ > > > Inworks GmbH > Andre Reitz, Leiter Entwicklung > H�rvelsinger Weg 39, 89081 Ulm, Germany > Tel +49 (0) 731 / 93807-21 > Fax +49(0)731/93807-18 > Internet: http://www.inworks.de -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
