MS-SQL Queue Deadlocks

boernard · 06.08.2009, 10:33

Hi,

habe wie hier so üblich eine Frage.

Ich habe eine Tabelle " Queue ":
ID (autoIncrement / Identity)
ConfigDaten1
ConfigDaten2
ConfigDaten3
Werte
...

Eine Tabelle " Final":
ID (autoIncrement / Identity)
ConfigID (FK zu Config)
Werte
...

Eine Tabelle " Config":
ConfigID (autoIncrement / Identity)
ConfigDaten1
ConfigDaten2
ConfigDaten3

Tabelle Queue ist, wie der Name schon sagt, eine Queue Tabelle. Die Daten die hier reingeschrieben werden, werden nach einem Insert durch einen Prozedur (von einem Trigger aufgerufen) weiterverarbeitet. Es wird geprüft, ob die ConfigDaten-Konfiguration bereits in der Tabelle "Config" vorhanden ist. Falls ja wird der entsprechende Key geholt, falls nein wird ein neuer Datensatz in der Tabelle Config angelegt. In der Tabelle Final werden die Datensätze mit dem FK zu der entsprechenden Config-Einstellung gespeichert. Falls der Insert in die Tabelle "Final" nicht funzt (aufgrund falscher Datenwerte) wird der Datensatz in der Tabelle FalseData gespeichert, inklusive der Config-Werte. Der angelegte Datensatz in Config wird wieder gelöscht.

Hier der Code der Prozedur

PHP-Code:

   

DECLARE @ConfigDaten1int

DECLARE @ConfigDaten2 int

DECLARE @ConfigDaten3 int

DECLARE @ConfigDaten4 int

 

DECLARE @configID int

DECLARE @newInsert int

DECLARE @currentRow int

 

WHILE (SELECT count(*) FROM Queue) > 0

BEGIN

 

 BEGIN TRAN Queue

 

 SET @newInsert = 0

 CREATE TABLE #temp (

 [ConfigDaten1] [int],

 [ConfigDaten2] [int],

 [ConfigDaten3] [int],

 [ConfigDaten4] [int]

)

 

 SELECT @currentRow = MIN(ID) FROM [db3].[Queue] WITH (updlock, readpast)

 INSERT INTO #temp SELECT ConfigDaten1, ConfigDaten2, ConfigDaten3, ConfigDaten FROM [db3].[Queue] WHERE ID = @currentRow

 SELECT @ms1 = ms1,@ms2= ms2,@ms3 = ms3,@ms4 = ms4,@msp1 = msp1,@msp2 = msp2, @msp3 = msp3,@msp4 = msp4 FROM #temp

 

 SET @configID = -1

 SELECT @configID = ConfigID FROM [db3].[Config] 

 WHERE ConfigDaten1= @ConfigDaten1 AND

 ConfigDaten2 = @ConfigDaten2 AND

 ConfigDaten3= @ConfigDaten3 AND

 ConfigDaten4 = @ConfigDaten4 AND

 

 IF @configID = -1

 BEGIN

  INSERT INTO [db3].[Config] (ConfigDaten1, ConfigDaten2, ConfigDaten3, ConfigDaten4) 

  VALUES (@ConfigDaten1, @ConfigDaten2, @ConfigDaten3, @ConfigDaten4)

  SET @configID = @@IDENTITY

 

  SET @newInsert = 1

 END

 DROP table #temp

 BEGIN TRY

  INSERT INTO [db3].[Final] (

  Werte

  .

  .

  .

  )

  SELECT 

  @configID,

  Werte

  .

  .

  .

  FROM [db3].Queue WHERE ID = @currentRow

 

 END TRY

 BEGIN CATCH

 

  IF @newInsert = 1

  BEGIN

   DELETE FROM [db3].[Config] WHERE ConfigID = @configID

  END

  INSERT INTO [db3].[FalseData] (

  SqlErrorCode,

  Werte

  .

  .

  )

  SELECT

  ERROR_MESSAGE(),

  Werte

  .

  .

  )

  FROM [db3].Queue WHERE ID = @currentRow

 END CATCH

 

 IF @@ERROR = 0

 BEGIN

  DELETE FROM [db3].Queue WHERE ID = @currentRow

 END

 COMMIT

END

Erste Frage: Was kann man an dem Code einfacher machen / verbessern?
Zweite Frage: Ich habe mit Quest Benchmark Factory einen Test gemacht, in dem ich 3 Clients parallel Inserts in die Tabelle Queue machen. Zwischen den Inserts eines Clients sind 500ms Pause. Bekomme hier dauernd Deadlocks o.o Soll ich nun vom Trigger weggehen und die Prozedur minütlich aufrufen lassen?

Chriz · 06.08.2009, 21:34

Kann ich mal umgekehrt fragen, warum du eine Prozedur dafür verwendest? Ich finde Prozeduren sehr sehr schwer zu warten, außerdem kennen sich die wenigsten damit aus (ich inklusive, hab mittlerweile alles vergessen seit dem letzten Vorfall).

boernard · 07.08.2009, 08:53

was für eine alternative zu einer prozedur gibt es denn?

Chriz · 07.08.2009, 09:44

Zitat:

Die Daten die hier reingeschrieben werden, werden nach einem Insert durch einen Prozedur (von einem Trigger aufgerufen) weiterverarbeitet.

Na entweder nach dem INSERT eine entsprechende Verarbeitung durch ein Skript (PHP, ASP, ..) oder aber ein Cronjob (bei dir wohl eher Task), der das Skript aufruft, du bei einem einfachen INSERT also nicht auf die Verarbeitung durch das Skript warten musst. Falls das für dich nicht in Frage kommt, kann ich dir leider bei deiner Prozedur nicht weiterhelfen, MSSQL und Prozeduren fallen nicht in mein Fachgebiet.

boernard · 07.08.2009, 09:55

Skriptseitige Verarbeitung ist eher nicht möglich, das ganze sollte rein datenbankbasiert bleiben. Aber ich bin nun letztendlich auch auf die Sache mit dem Task umgestiegen.
Lasse nun minütlich die Prozedur ausführen, die die Daten aus Queue holt und in Final einfügt.
Thx.

DaFox · 07.08.2009, 11:46

Zitat:

Zitat von boernard

Skriptseitige Verarbeitung ist eher nicht möglich, das ganze sollte rein datenbankbasiert bleiben. Aber ich bin nun letztendlich auch auf die Sache mit dem Task umgestiegen.
Lasse nun minütlich die Prozedur ausführen, die die Daten aus Queue holt und in Final einfügt.
Thx.

Hat sich damit das Problem mit den Deadlocks auch gelöst? Hatte hier mal eine ähnliche Geschichte und konnte es durch Änderung des Isolationslevels der Transaktion beheben.

boernard · 10.08.2009, 10:13

@DaFox:
Jep, jetzt gibt es keine Deadlocks mehr.
Aber kannst du trotzdem sagen, wie du die Isolationslevel eingestellt hast? Bestimmt nützlich zu wissen.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

06.08.2009, 10:33
boernard Neuer Benutzer Registriert seit: 05.12.2008 Beiträge: 17	MS-SQL Queue Deadlocks Hi, habe wie hier so üblich eine Frage. Ich habe eine Tabelle " Queue ": ID (autoIncrement / Identity) ConfigDaten1 ConfigDaten2 ConfigDaten3 Werte ... Eine Tabelle " Final": ID (autoIncrement / Identity) ConfigID (FK zu Config) Werte ... Eine Tabelle " Config": ConfigID (autoIncrement / Identity) ConfigDaten1 ConfigDaten2 ConfigDaten3 Tabelle Queue ist, wie der Name schon sagt, eine Queue Tabelle. Die Daten die hier reingeschrieben werden, werden nach einem Insert durch einen Prozedur (von einem Trigger aufgerufen) weiterverarbeitet. Es wird geprüft, ob die ConfigDaten-Konfiguration bereits in der Tabelle "Config" vorhanden ist. Falls ja wird der entsprechende Key geholt, falls nein wird ein neuer Datensatz in der Tabelle Config angelegt. In der Tabelle Final werden die Datensätze mit dem FK zu der entsprechenden Config-Einstellung gespeichert. Falls der Insert in die Tabelle "Final" nicht funzt (aufgrund falscher Datenwerte) wird der Datensatz in der Tabelle FalseData gespeichert, inklusive der Config-Werte. Der angelegte Datensatz in Config wird wieder gelöscht. Hier der Code der Prozedur PHP-Code: DECLARE @ConfigDaten1int DECLARE @ConfigDaten2 int DECLARE @ConfigDaten3 int DECLARE @ConfigDaten4 int DECLARE @configID int DECLARE @newInsert int DECLARE @currentRow int WHILE (SELECT count(*) FROM Queue) > 0 BEGIN BEGIN TRAN Queue SET @newInsert = 0 CREATE TABLE #temp ( [ConfigDaten1] [int], [ConfigDaten2] [int], [ConfigDaten3] [int], [ConfigDaten4] [int] ) SELECT @currentRow = MIN(ID) FROM [db3].[Queue] WITH (updlock, readpast) INSERT INTO #temp SELECT ConfigDaten1, ConfigDaten2, ConfigDaten3, ConfigDaten FROM [db3].[Queue] WHERE ID = @currentRow SELECT @ms1 = ms1,@ms2= ms2,@ms3 = ms3,@ms4 = ms4,@msp1 = msp1,@msp2 = msp2, @msp3 = msp3,@msp4 = msp4 FROM #temp SET @configID = -1 SELECT @configID = ConfigID FROM [db3].[Config] WHERE ConfigDaten1= @ConfigDaten1 AND ConfigDaten2 = @ConfigDaten2 AND ConfigDaten3= @ConfigDaten3 AND ConfigDaten4 = @ConfigDaten4 AND IF @configID = -1 BEGIN INSERT INTO [db3].[Config] (ConfigDaten1, ConfigDaten2, ConfigDaten3, ConfigDaten4) VALUES (@ConfigDaten1, @ConfigDaten2, @ConfigDaten3, @ConfigDaten4) SET @configID = @@IDENTITY SET @newInsert = 1 END DROP table #temp BEGIN TRY INSERT INTO [db3].[Final] ( Werte . . . ) SELECT @configID, Werte . . . FROM [db3].Queue WHERE ID = @currentRow END TRY BEGIN CATCH IF @newInsert = 1 BEGIN DELETE FROM [db3].[Config] WHERE ConfigID = @configID END INSERT INTO [db3].[FalseData] ( SqlErrorCode, Werte . . ) SELECT ERROR_MESSAGE(), Werte . . ) FROM [db3].Queue WHERE ID = @currentRow END CATCH IF @@ERROR = 0 BEGIN DELETE FROM [db3].Queue WHERE ID = @currentRow END COMMIT END Erste Frage: Was kann man an dem Code einfacher machen / verbessern? Zweite Frage: Ich habe mit Quest Benchmark Factory einen Test gemacht, in dem ich 3 Clients parallel Inserts in die Tabelle Queue machen. Zwischen den Inserts eines Clients sind 500ms Pause. Bekomme hier dauernd Deadlocks o.o Soll ich nun vom Trigger weggehen und die Prozedur minütlich aufrufen lassen?


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

06.08.2009, 21:34
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.266	Kann ich mal umgekehrt fragen, warum du eine Prozedur dafür verwendest? Ich finde Prozeduren sehr sehr schwer zu warten, außerdem kennen sich die wenigsten damit aus (ich inklusive, hab mittlerweile alles vergessen seit dem letzten Vorfall). __________________ "Nuschel ich?" - "Was?"

07.08.2009, 08:53
boernard Neuer Benutzer Registriert seit: 05.12.2008 Beiträge: 17	was für eine alternative zu einer prozedur gibt es denn?

07.08.2009, 09:55
boernard Neuer Benutzer Registriert seit: 05.12.2008 Beiträge: 17	Skriptseitige Verarbeitung ist eher nicht möglich, das ganze sollte rein datenbankbasiert bleiben. Aber ich bin nun letztendlich auch auf die Sache mit dem Task umgestiegen. Lasse nun minütlich die Prozedur ausführen, die die Daten aus Queue holt und in Final einfügt. Thx.

10.08.2009, 10:13
boernard Neuer Benutzer Registriert seit: 05.12.2008 Beiträge: 17	@DaFox: Jep, jetzt gibt es keine Deadlocks mehr. Aber kannst du trotzdem sagen, wie du die Isolationslevel eingestellt hast? Bestimmt nützlich zu wissen.