Principale utente con più risposte
Aiuto su deadlock

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
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
Avete qualche consiglio su come evitarlo?
Grazie
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
-
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
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
-
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
-
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
-
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