Remove From My Forums

Aiuto su deadlock

Domanda

Registrati per votare

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

Registrati per votare

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

1

Registrati per votare
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

Risposta

|

Citazione

Moderatore

Tutte le risposte

Registrati per votare

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

0

Registrati per votare

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

Risposta

|

Citazione
1

Registrati per votare
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

Risposta

|

Citazione

Moderatore
0

Registrati per votare

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

Risposta

|

Citazione

Moderatore

Prodotti

Resources

Solutions

Aggiornamenti

Valutazioni

Siti correlati

Formazione

Certificazioni

Ulteriori risorse

Per prodotto

Altri collegamenti

Non professionisti IT

Principale utente con più risposte

Aiuto su deadlock

Domanda

Risposte

Tutte le risposte