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. 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. 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] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
