none
Aiuto su deadlock RRS feed

  • Domanda

  • Salve,
    ho un problema di deadlock che non so come risolvere.
    Ho creato un esempio per simulare in piccolo quello che succede.

    use tempdb
    go
    /*creo oggetti*/
    create table [Saldi](
    Conto nvarchar(20),
    Dare money,
    Avere money,
    SaldoDare money,
    SaldoAvere money
    )
    go
    create table [PrimaNotaTesta](
    Id int
    )
    go
    create table [PrimaNotaCorpo](
    Id int identity,
    TestaId int,
    Conto nvarchar(20), Dare money, Avere money ) go CREATE PROCEDURE [AggiornaSaldi] @Conto nvarchar(20) AS SET NOCOUNT,XACT_ABORT ON MERGE [Saldi] WITH (HOLDLOCK) AS t USING ( SELECT [Conto], SUM([Dare]) AS [Dare], SUM([Avere]) AS [Avere], CASE WHEN SUM([Dare]) > SUM([Avere]) THEN SUM([Dare]) - SUM([Avere]) ELSE 0 END AS [SaldoDare], CASE WHEN SUM([Avere]) > SUM([Dare]) THEN SUM([Avere]) - SUM([Dare]) ELSE 0 END AS [SaldoAvere] FROM [PrimaNotaCorpo] WHERE [Conto] = @Conto GROUP BY [Conto] ) AS s ON t.[Conto] = s.[Conto] WHEN NOT MATCHED BY SOURCE AND t.[Conto] = @Conto THEN DELETE WHEN MATCHED THEN UPDATE SET t.[Dare] = s.[Dare], t.[Avere] = s.[Avere], t.[SaldoDare] = s.[SaldoDare], t.[SaldoAvere] = s.[SaldoAvere] WHEN NOT MATCHED THEN INSERT ( [Conto], [Dare], [Avere], [SaldoDare], [SaldoAvere] ) VALUES ( s.[Conto], s.[Dare], s.[Avere], s.[SaldoDare], s.[SaldoAvere] );


    il primo utente lancia la seguente istruzione

    use tempdb
    go
    /*simulo utente 1*/
    declare @id1 int = 1000
    while @id1 < 2000
    begin begin tran insert [PrimaNotaTesta] (Id) VALUES (@id1) insert [PrimaNotaCorpo] (TestaId,Conto,Dare,Avere) VALUES (@id1,'1111',100, 0) exec [AggiornaSaldi] '1111' insert [PrimaNotaCorpo] (TestaId,Conto,Dare,Avere) VALUES (@id1,'2222',0, 100) exec [AggiornaSaldi] '2222' commit tran set @id1 = @id1 + 1 end
    il secondo utente lancia la seguente istruzione

    use tempdb
    go
    /*simulo utente 2*/
    declare @id2 int = 2000
    while @id2 < 3000
    	begin
    	begin tran
    	insert [PrimaNotaTesta] (Id) VALUES (@id2)
    	insert [PrimaNotaCorpo] (TestaId,Conto,Dare,Avere) VALUES (@id2,'1111',100, 0)
    	exec [AggiornaSaldi] '1111'
    	insert [PrimaNotaCorpo] (TestaId,Conto,Dare,Avere) VALUES (@id2,'2222',0, 100)
    	exec [AggiornaSaldi] '2222'
    	commit tran
    	set @id2 = @id2 + 1
    	end
    
    
    a questo punto dovreste aver ottenuto un deadlock!
    Avete qualche consiglio su come evitarlo?
    Grazie
    giovedì 18 febbraio 2010 14:42

Risposte

  • Ciao Palegra,

    per avere i saldi in tempo reale e meno grattacapi, valuta se usare una vista indicizzata
    esempio:
    USE TEMPDB
    GO
    create table dbo.PrimaNotaTesta(
    Id int
    )
    go
    
    create table dbo.PrimaNotaCorpo(
    Id int identity,
    TestaId int,
    Conto nvarchar(20) NOT NULL,
    Dare money NOT NULL,
    Avere money NOT NULL
    )
    go
    
    CREATE VIEW dbo.uvs_Saldi WITH SCHEMABINDING
    AS
    SELECT Conto,
           Dare = SUM(Dare),
           Avere = Sum(Avere),
           COUNT_BIG(*) AS ConteggioReg
    FROM   dbo.PrimaNotaCorpo
    GROUP BY Conto
    GO
    CREATE UNIQUE CLUSTERED INDEX PK_Conto ON dbo.uvs_Saldi (Conto)
    GO
    

    per maggiori info:
    ----
    Improving Performance with SQL Server 2005 Indexed Views
    http://msdn.microsoft.com/en-us/library/cc917715.aspx
    ----

    Ciao
    Giorgio Rancati
    • Contrassegnato come risposta fcavicchi venerdì 19 febbraio 2010 12:55
    giovedì 18 febbraio 2010 15:13
    Moderatore
  • Ciao,

    per semplificarla, potresti evitare la derived table
    ----
    select
    isnull(IdPadre, Id) as Id
    sum(Dare) as Dare,
    sum(Avere) as Avere
    from
    dbo.Scadenzario
    group by
    isnull(IdPadre, Id)
    having sum(Dare) <> sum(Avere)
    ----

    la vista indicizzata di base la crei così
    ----
    Create View dbo.uvs_q WITH SCHEMABINDING AS
    select
    IsNull(IdPadre,Id) AS Id,
    sum(Dare) as Dare,
    sum(Avere) as Avere,
    COUNT_BIG(*) AS CountRighe
    from
    dbo.Scadenzario
    group by
    IsNull(IdPadre,Id)

    CREATE UNIQUE CLUSTERED INDEX PK_Id ON dbo.uvs_q (Id)
    ----

    poi se vuoi crei una vista semplice che attinge dalla vista indicizzata per avere solo i saldi <> da 0

    Ciao
    Giorgio Rancati

    • Contrassegnato come risposta fcavicchi venerdì 19 febbraio 2010 12:55
    venerdì 19 febbraio 2010 11:18
    Moderatore

Tutte le risposte

  • Ciao Palegra,

    per avere i saldi in tempo reale e meno grattacapi, valuta se usare una vista indicizzata
    esempio:
    USE TEMPDB
    GO
    create table dbo.PrimaNotaTesta(
    Id int
    )
    go
    
    create table dbo.PrimaNotaCorpo(
    Id int identity,
    TestaId int,
    Conto nvarchar(20) NOT NULL,
    Dare money NOT NULL,
    Avere money NOT NULL
    )
    go
    
    CREATE VIEW dbo.uvs_Saldi WITH SCHEMABINDING
    AS
    SELECT Conto,
           Dare = SUM(Dare),
           Avere = Sum(Avere),
           COUNT_BIG(*) AS ConteggioReg
    FROM   dbo.PrimaNotaCorpo
    GROUP BY Conto
    GO
    CREATE UNIQUE CLUSTERED INDEX PK_Conto ON dbo.uvs_Saldi (Conto)
    GO
    

    per maggiori info:
    ----
    Improving Performance with SQL Server 2005 Indexed Views
    http://msdn.microsoft.com/en-us/library/cc917715.aspx
    ----

    Ciao
    Giorgio Rancati
    • Contrassegnato come risposta fcavicchi venerdì 19 febbraio 2010 12:55
    giovedì 18 febbraio 2010 15:13
    Moderatore
  • Ciao Giorgio, grazie per il consiglio.
    ho già cominciato ad usare una vista indicizzata come mi hai indicato e sembra funzionare bene.
    Ci sarebbe anche un'altra situazione, in cui potrei usare la stessa tecnica, che vorrei sottoporti.
    Si tratta dei saldi delle scadenze contabili aperte.

    use tempdb
    go

    create table dbo.Scadenzario (
    Id int identity,
    IdPadre int,
    Dare money NOT NULL,
    Avere money NOT NULL
    )
    go

    attualmente per ottenere i saldi uso la seguente query

    select
    q.*
    from
    (
    select
    isnull(IdPadre, Id) as Id
    sum(Dare) as Dare,
    sum(Avere) as Avere
    from
    dbo.Scadenzario
    group by
    isnull(IdPadre, Id)
    ) q
    where
    q.Dare <> q.Avere

    Secondo te si può ottenere lo stesso risultato in un altro modo?
    E come posso trasformarla in una vista indicizzata?
    Grazie

     

     

    venerdì 19 febbraio 2010 08:26
  • Ciao,

    per semplificarla, potresti evitare la derived table
    ----
    select
    isnull(IdPadre, Id) as Id
    sum(Dare) as Dare,
    sum(Avere) as Avere
    from
    dbo.Scadenzario
    group by
    isnull(IdPadre, Id)
    having sum(Dare) <> sum(Avere)
    ----

    la vista indicizzata di base la crei così
    ----
    Create View dbo.uvs_q WITH SCHEMABINDING AS
    select
    IsNull(IdPadre,Id) AS Id,
    sum(Dare) as Dare,
    sum(Avere) as Avere,
    COUNT_BIG(*) AS CountRighe
    from
    dbo.Scadenzario
    group by
    IsNull(IdPadre,Id)

    CREATE UNIQUE CLUSTERED INDEX PK_Id ON dbo.uvs_q (Id)
    ----

    poi se vuoi crei una vista semplice che attinge dalla vista indicizzata per avere solo i saldi <> da 0

    Ciao
    Giorgio Rancati

    • Contrassegnato come risposta fcavicchi venerdì 19 febbraio 2010 12:55
    venerdì 19 febbraio 2010 11:18
    Moderatore
  • Dimenticavo...

    non abusare delle viste indicizzate, dietro le quinte vanno a spazzolarsi tutti i dati della tabella per memorizzare i totali nella vista, questa operazione appesantisce le operazioni di scrittura/cancellazione della tabella di origine.

    Quindi usale se proprio necessario, anche perchè il problema del dead-lock potrebbe sempre verificarsi.
    :-)
    Giorgio Rancati
    venerdì 19 febbraio 2010 13:24
    Moderatore