From: Andre Reitz (reitzinworks.de)
Date: Fri May 28 2004 - 03:07:14 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On Fri, 28 May 2004 09:55:55 +0200
"Zabach, Elke" <elke.zabachsap.com> wrote:

> Andre Reitz 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?
> >
> >
> Which isolation level do you use?
> Tab_Share locks may be caused by isolation level 15 / 2/20 / 3/30 and not by lock escalations.

Only the default Isolation Level 10. :)

>
> Did you check your transaction-structure, meaning: do you hold locks too long because uncommitted transactions / not committed as often as would be convenient?

It is a web application. None of the Transactions are opened longer than around 0.5 upto 2 seconds.

>
> I assume that you do not lock the tables explicitly?
>

You are right.

Question: Is the Dump I sent a classic deadlock situation?

Greetings, Andre'

> Elke
> SAP Labs Berlin
>
> >
> > 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

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]