Deadlock SQL Abfrage über JDBC

Question

Hallo!

Ich habe einen Jasper Report Server welcher über JDBC auf meinen SQL Server zugreift.

Mittels diesen Reportserver erstellen wir Auswertungen über Verkaufszahlung usw.

Sobald ich Abfragen mit einer großen Zeitspanne und einer hohen Verdichtung mache gibt es Probleme.

Es kommt zu einem Deadlock. Ich kann mir aber nicht erklären warum.

Im Management Studio funktioniert die Abfrage ganz normal.

Report Server:

ESX 5.1 VM

Centos 6.5

4x2,2 GHz

4 Gb RAM

4x15k SAS HDD Raid 10

10GB



Datenbankserver

Microsoft Server 2008 R2

Microsoft SQL Server 2008 R2

32 GB RAM

4x2,93 GHz Intel X5570

8x15k SAS

Die Abfrage

select 
Kunden.Zeit as Zeit,
Kunden.Verdichtung as Verdichtung,
Kunden.Kunden as Kunden,
Damen.Damen as Damen,
Herren.Herren as Herren,
Kinder.Kinder as Kinder
from 

(select
COUNT(DISTINCT Bons.Id) as Kunden,
CAST(Bons.SaleDate as date) as Zeit ,
Bons.Verbucht as Verdichtung
from Bons, 
Salon, 
Mitarbeiter, 
Bereich,
ArtikelVerkauf
where 
Bons.SalonId=Salon.Id AND
Bons.Abgeschlossen=1 AND
Bons.Verbucht=1 AND
Bons.Mitarbeiter=0 AND
Bons.SaleDate < '03.08.2014'  AND
Bons.SaleDate > '03.08.2013'  AND



Mitarbeiter.ID=Bons.MitarbeiterId AND
Salon.BereichID=Bereich.ID AND
ArtikelVerkauf.BonId=Bons.Id AND

ArtikelVerkauf.Refund=0 AND
ArtikelVerkauf.Anzahlung=0 AND
ArtikelVerkauf.Verkaufsgutschein=0 AND
Bons.Mitarbeiter=0 


group by
CAST(Bons.SaleDate as date),
Bons.Verbucht

) as Kunden FULL OUTER JOIN


(select
COUNT(DISTINCT Bons.Id) as Damen,
CAST(Bons.SaleDate as date) as Zeit ,
Bons.Verbucht as Verdichtung
from Bons, 
Salon, 
Mitarbeiter, 
Bereich,
ArtikelVerkauf
where 
Bons.SalonId=Salon.Id AND
Bons.Abgeschlossen=1 AND
Bons.Verbucht=1 AND
Bons.Mitarbeiter=0 AND
Bons.SaleDate < '03.08.2014'  AND
Bons.SaleDate > '03.08.2013'  AND

Mitarbeiter.ID=Bons.MitarbeiterId AND
Salon.BereichID=Bereich.ID AND
ArtikelVerkauf.BonId=Bons.Id AND

ArtikelVerkauf.Refund=0 AND
ArtikelVerkauf.Anzahlung=0 AND
ArtikelVerkauf.Verkaufsgutschein=0 AND
Bons.Dame=1 

group by
CAST(Bons.SaleDate as date),
Bons.Verbucht



) as Damen ON Damen.Verdichtung=Kunden.Verdichtung and Damen.Zeit=Kunden.Zeit

FULL OUTER JOIN


(select
COUNT(DISTINCT Bons.Id) as Herren,
CAST(Bons.SaleDate as date) as Zeit ,
Bons.Verbucht as Verdichtung
from Bons, 
Salon, 
Mitarbeiter, 
Bereich,
ArtikelVerkauf
where 
Bons.SalonId=Salon.Id AND
Bons.Abgeschlossen=1 AND
Bons.Verbucht=1 AND
Bons.Mitarbeiter=0 AND
Bons.SaleDate < '03.08.2014'  AND
Bons.SaleDate > '03.08.2013'  AND

Mitarbeiter.ID=Bons.MitarbeiterId AND
Salon.BereichID=Bereich.ID AND
ArtikelVerkauf.BonId=Bons.Id AND

ArtikelVerkauf.Refund=0 AND
ArtikelVerkauf.Anzahlung=0 AND
ArtikelVerkauf.Verkaufsgutschein=0 AND
Bons.Herr=1 

group by
CAST(Bons.SaleDate as date),
Bons.Verbucht



) as Herren ON Herren.Verdichtung=Kunden.Verdichtung and Herren.Zeit=Kunden.Zeit


FULL OUTER JOIN


(select
COUNT(DISTINCT Bons.Id) as Kinder,
CAST(Bons.SaleDate as date) as Zeit ,
Bons.Verbucht as Verdichtung
from Bons, 
Salon, 
Mitarbeiter, 
Bereich,
ArtikelVerkauf
where 
Bons.SalonId=Salon.Id AND
Bons.Abgeschlossen=1 AND
Bons.Verbucht=1 AND
Bons.Mitarbeiter=0 AND
Bons.SaleDate < '03.08.2014'  AND
Bons.SaleDate > '03.08.2013'  AND

Mitarbeiter.ID=Bons.MitarbeiterId AND
Salon.BereichID=Bereich.ID AND
ArtikelVerkauf.BonId=Bons.Id AND

ArtikelVerkauf.Refund=0 AND
ArtikelVerkauf.Anzahlung=0 AND
ArtikelVerkauf.Verkaufsgutschein=0 AND
Bons.Kind=1 

group by
CAST(Bons.SaleDate as date),
Bons.Verbucht



) as Kinder ON Kinder.Verdichtung=Kunden.Verdichtung and Kinder.Zeit=Kunden.Zeit

order by 
Zeit,  
Verdichtung

Der Stacktrace

2014-08-06 10:28:24,922 ERROR AsyncJasperPrintAccessor,pool-4-thread-7:299 - Error during report execution
net.sf.jasperreports.engine.JRException: Unable to get next record.
        at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:113)
        at net.sf.jasperreports.engine.fill.JRFillDataset.advanceDataSource(JRFillDataset.java:1405)
        at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1254)
        at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1233)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1577)
        at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:149)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:932)
        at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:880)
        at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:836)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1702)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1057)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:991)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:902)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:724)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Die Transaktion (Prozess-ID 55) befand sich auf Sperre | Kommunikationspuffer Ressourcen aufgrund eines anderen Prozesses in einer Deadlocksituation und wurde als Deadlockopfer ausgewählt. Führen Sie die Transaktion erneut aus.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034)
        at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:168)
        at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:109)
        ... 17 more
2014-08-06 10:28:24,928 ERROR errorPage_jsp,http-bio-80-exec-7:287 - JSException:
2014-08-06 10:28:24,930 ERROR errorPage_jsp,http-bio-80-exec-7:573 - stack trace of exception that redirected to errorPage.jsp
com.jaspersoft.jasperserver.api.JSException: Error filling report
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1233)
        at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211)
        at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:880)
        at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:836)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1702)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1057)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:991)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:902)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:724)
Caused by: net.sf.jasperreports.engine.JRException: Unable to get next record.
        at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:113)
        at net.sf.jasperreports.engine.fill.JRFillDataset.advanceDataSource(JRFillDataset.java:1405)
        at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1254)
        at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1233)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1577)
        at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:149)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:932)
        at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)
        ... 10 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Die Transaktion (Prozess-ID 55) befand sich auf Sperre | Kommunikationspuffer Ressourcen aufgrund eines anderen Prozesses in einer Deadlocksituation und wurde als Deadlockopfer ausgewählt. Führen Sie die Transaktion erneut aus.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034)
        at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:168)
        at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:109)
        ... 17 more

Answer 1

Hi,

herrlicher Verschreibser :) Japser Report. Der japst bestimmt nach Luft wegen dem Deadlock :) (Edit: Och Menno, hättest es ruhig stehen lassen können, ich fands lustig^^)

Zum Thema selbst:

Probier mal, das SELECT Statement so aufzubauen, dass Du mit Tabellenhinweisen arbeitest:

  http://msdn.microsoft.com/de-de/library/ms187373.aspx

In deinem Fall wäre dann bspw.:

SELECT ...
FROM   Tabelle WITH ( NOLOCK )

SELECT ...
FROM   Tabelle WITH ( READUNCOMMITED )

...

wohl angebracht.

Ich denke mal, dass zeitgleich mit deiner Abfrage noch Daten in einer oder mehreren Tabelle(n) aktualisiert werden und es dadurch zu deinem Problem kommt.

Lies dir bitte aber auch die Hinweise zu den Vor- und Nachteilen der Tabellenhinweise durch.

Wenn das alles nichts hilft, poste bitte mal die Tabellenstrukturen als CREATE TABLE Statement und einige Beispieldaten als INSERT Statement. Das SELECT Statement oben wäre etwas übersichtlicher formatiert auch ganz nett.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Vielen Dank hat funktioniert.

READUNCOMMITED  schreibt man aber mit 2 T

Lg christian

Answer 3

Hallo Christian,

Recht hast Du :) Schreibfehlerchen halt.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Ich rate dringend von der Verwendung von NOLOCK bzw. READUNCOMMITTED ab!

Ausser, Du kannst damit leben, dass die angezeigten Ergebnisse nur "Näherungswerte" sind, d.h. manche Verkaufszahlen doppelt gezählt werden bzw. gar nicht berücksichtigt. Die Abfrage sollte also eher als "grobe Schätzung" interpretiert werden.

Wenn ich die Abfrage richtig interpertiere, versuchst Du, sowohl alle Ergebnisse als auch getrennt nach Damen, Herren und Kindern auszugeben.

Hierfür würde ich nicht 4x die nahezu identische (komplexe) Abfrage ausführen, sondern nur 1x und dann im SELECT über CASE-Anweisungen aggregieren. Damit sollte nicht nur der Deadlock verschwunden sein, sondern die Performance ingesamt erheblich verbessert werden.

Anbei ein "Schuß ins Blaue" (da ich keinerlei Testdaten habe...):

SELECT Kunden.Zeit AS Zeit
	,Kunden.Verdichtung AS Verdichtung
	,Kunden.Kunden AS Kunden
	,Kunden.Damen AS Damen
	,Kunden.Herren AS Herren
	,Kunden.Kinder AS Kinder
FROM (
	SELECT COUNT(DISTINCT Bons.Id) AS Kunden
		,COUNT(DISTINCT (
				CASE 
					WHEN Bons.Dame = 1
						THEN Bons.Id
					ELSE NULL
					END
				)) AS Damen
		,COUNT(DISTINCT (
				CASE 
					WHEN Bons.Herr = 1
						THEN Bons.Id
					ELSE NULL
					END
				)) AS Herren
		,COUNT(DISTINCT (
				CASE 
					WHEN Bons.Kind = 1
						THEN Bons.Id
					ELSE NULL
					END
				)) AS Kinder
		,CAST(Bons.SaleDate AS DATE) AS Zeit
		,Bons.Verbucht AS Verdichtung
	FROM Bons
		,Salon
		,Mitarbeiter
		,Bereich
		,ArtikelVerkauf
	WHERE Bons.SalonId = Salon.Id
		AND Bons.Abgeschlossen = 1
		AND Bons.Verbucht = 1
		AND Bons.Mitarbeiter = 0
		AND Bons.SaleDate < '03.08.2014'
		AND Bons.SaleDate > '03.08.2013'
		AND Mitarbeiter.ID = Bons.MitarbeiterId
		AND Salon.BereichID = Bereich.ID
		AND ArtikelVerkauf.BonId = Bons.Id
		AND ArtikelVerkauf.Refund = 0
		AND ArtikelVerkauf.Anzahlung = 0
		AND ArtikelVerkauf.Verkaufsgutschein = 0
		AND Bons.Mitarbeiter = 0
	GROUP BY CAST(Bons.SaleDate AS DATE)
		,Bons.Verbucht
	) AS Kunden
ORDER BY Zeit
	,Verdichtung

Answer 5

Danke für deine Antwort

Hat leider nicht zum gewünschten Erfolg geführt.

Mit der Case-Variante braucht das Query mehr als doppelt so lange.

Aktuell braucht mein Query 18 Sekunden und dein Vorschlag 57 Sekunden

Ein Deadlock tritt zurzeit nicht auf, da wahrscheinlich zu wenige Benutzer aktuell die Datenbank verwenden.

Answer 6

Ich hoffe, dass diese Variante die gleichen Ergebnisse bringt, was ich mangels Testdaten nicht nachprüfen kann. Hoffe auch, dass die Syntax stimmt und die Zeit besser ist! ;-)

With Vorberechnung as
(SELECT DISTINCT Bons.Id AS Dist_ID,
1 as Kunden,
CASE WHEN Bons.Dame = 1        THEN 1
     ELSE 0
END AS Damen,
CASE WHEN Bons.Herr = 1 THEN 1
     ELSE 0
END AS Herren,
CASE WHEN Bons.Kind = 1 THEN 1
     ELSE 0
END AS Kinder,
CAST(Bons.SaleDate AS DATE) AS Zeit,
Bons.Verbucht AS Verdichtung
FROM Bons
,Salon
,Mitarbeiter
,Bereich
,ArtikelVerkauf
WHERE Bons.SalonId = Salon.Id
AND Bons.Abgeschlossen = 1
AND Bons.Verbucht = 1
AND Bons.Mitarbeiter = 0
AND Bons.SaleDate < '03.08.2014'
AND Bons.SaleDate > '03.08.2013'
AND Mitarbeiter.ID = Bons.MitarbeiterId
AND Salon.BereichID = Bereich.ID
AND ArtikelVerkauf.BonId = Bons.Id
AND ArtikelVerkauf.Refund = 0
AND ArtikelVerkauf.Anzahlung = 0
AND ArtikelVerkauf.Verkaufsgutschein = 0
AND Bons.Mitarbeiter = 0
)

SELECT Zeit
,Verdichtung
,Sum(Kunden) AS Kunden
,Sum(Damen) AS Damen
,Sum(Herren) AS Herren
,Sum(Kinder) AS Kinder
FROM Vorberechnung
GROUP BY Zeit, Verdichtung
ORDER BY Zeit, Verdichtung;

Falls die Felder Dame, Herr, Kind nur die Werte 1 und 0 kennen, kann das Case entfallen.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 7

Danke Christoph

Das ist genau das was ich wollte.

Die CASE brauch ich nicht, da die Felder nur binäre Werte haben.

Lg 

Christian