Discussion :

[zoltix]

Bonjour,
Je suis en train d’étudier la problématique des deadlocks. Un ami m’a mis au défi de provoquer un deadlock avec un mix de deux types d’isolation de transaction un « read commited » et l’autre en « serializable » avec la base de données configuré « read commited snapshot ».
Et donc j’essaye de provoquer cette erreur, mais je n’y arrive pas.
Voici le deadlock qui devrait être généré.




La table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE [asf_avis].[noticeFunctionalACK](
	[noticeFunctionalACK_id] [int] IDENTITY(1,1) NOT NULL,
	[functionalACKStatusType_fk] [tinyint] NOT NULL,
	[ackReference] [nvarchar](20) NULL,
	[ackDate] [datetime] NULL,
	[noticeReceiptDate] [datetime] NULL,
	[nihilAttest] [bit] NULL,
	[noticeSending_fk] [int] NOT NULL,
	[party_fk] [int] NULL,
 CONSTRAINT [PK_noticeFunctionalACK] PRIMARY KEY CLUSTERED 
(
	[noticeFunctionalACK_id] ASC
)
) ON [PRIMARY]
 
 
 
CREATE NONCLUSTERED INDEX [IDX_FK_noticeSending_fk] ON [asf_avis].[noticeFunctionalACK]
(
	[noticeSending_fk] ASC
)
GO

Il y a un index pour sur noticeSending_fk qui n'est pas unique.



Le spid= 218 pose un lock "X"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN		
	INSERT INTO  [asf_avis].[noticeFunctionalACK] (noticeSending_fk,[ackReference],ackDate,noticeReceiptDate,[nihilAttest],functionalACKStatusType_fk)
 
	      VALUES (2796, '31280313000002221', 
	       GETDATE() ,
	       GETDATE() ,
	      1 ,
	      1    )

Le spid= 436 pose un lock "RangeS-S"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN	
DECLARE @count INT 
SELECT COUNT(*)
FROM [asf_avis].[noticeFunctionalACK] ACK
WHERE  noticeSending_fk =2796
print @count

suite spid 218 dans la même transaction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
      .......continue .........
      		  SELECT noticeSending_fk,* FROM [asf_avis].   [noticeFunctionalACK] WHERE noticeSending_fk =2796

et la j'ai un locking qui se fait mais pas un deadlock ce que je comprends. mais alors comment provoquer ce dead lock?
Merci de votre aide et partage de connaisance

[aieeeuuuuu]

Un deadlock se produit quand deux transactions s'attendant mutuellement.
Dans votre cas, la transaction B(celle en mode SERIALIZABLE) attend A car elle veut poser un verrou sur un objet sur lequel A a déjà un verrou de posé, mais A n'attends pas B...

vous pouvez cependant provoquer un deadlock (quelle drole d'idée, pendant que d'autres s’époumonent à les traquer ) en utilisant deux objets distinct X et Y :

1/ A verrouille X
2/ B verrouille Y
3/ A veut vérouiller Y, mais doit donc attendre que B ait relâché le verrou
4/ B veut vérouiller X, mais doit donc attendre que A ait relâché le verrou

par exemple, avec deux tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
CREATE TABLE DL1 (A int);
CREATE TABLE DL2 (A int);

lancer A... :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
 
INSERT INTO DL2 VALUES (2)
 
--pause... lancer B maintenant
WAITFOR DELAY '00:00:05'
 
DELETE FROM DL1
 
ROLLBACK TRAN

puis lancer B (entre le INSERT et le DELETE de A)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN	
 
INSERT INTO DL1 VALUES(1)
 
DELETE FROM DL2
 
ROLLBACK TRAN

Notez qu'en lançant B puis A, ça fonctionne aussi...

[SQLpro]

Pour info, nous allons faire une session mon collègue Arian Papillon et moi même consacrée au verrouillage dans SQL Server lors des SQL Days des 2 et 3 décembre chez MS (c'est gratuit).

Cette session aura lieu le 3 décembre en début d'après midi.

A +

[zoltix]

Je n'ai pas facile a faire mon labo car c'est avec une seule table avec un index sur une FK qui a 2 ressources qui font rowlocking sur cet index.

Comment puis-je savoir dans sp_lock avec la colonne "ressource", quel est le range/row qui est bloqué
la ressource pour une key (XXXXXXX)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 SELECT  *
FROM    mytable
WHERE   %%LOCKRES%% = '{0000ABCDEFAB}

'
pour une page (X:XXX), je ne sais pas?


Donc, j'approche de ma solution. Mais j'ai beaucoup de mal.

Merci de votre aide

[SQLpro]

Pour une page tu dois faire un DBCC PAGE.

A +

[aieeeuuuuu]

Vous pouvez aussi le mettre en évidence sur une seule table. Si on prend votre table :

lancez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
 
INSERT INTO [asf_avis].[noticeFunctionalACK] ([functionalACKStatusType_fk], [noticeSending_fk]) VALUES (3,4)
 
WAITFOR DELAY '00:00:05'
 
DELETE FROM [asf_avis].[noticeFunctionalACK] 
 
ROLLBACK TRAN

Puis cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN	
 
INSERT INTO [asf_avis].[noticeFunctionalACK] ([functionalACKStatusType_fk], [noticeSending_fk]) VALUES (1,2)
 
DELETE FROM [asf_avis].[noticeFunctionalACK]
 
ROLLBACK TRAN

[zoltix]

Super. J’approche de ma solution et grâce vous, a DBCC PAGE, DBCC ind et %%LOCKRES%%, j'arrive à voir la page(s) ou row(s) qui bloquent étape par étape. De plus j'arrive à jouer et à démontrer l'importance des clefs (cluster ou non) pour la granularité des locks. Et de démontrer, l’importance et l’impact du niveau d’isolation.
Lorsque j’aurai un peu de temps et que j’aurai assimilé tout ça, je posterai une explication complète de mon labo pour clôturer ce post.
Car j’ai pu remarquer dans ma recherche les nombreux d’impact table/index partitionné, compressé, l’impact de la normalisation. Et surtout, lorsqu’on joue avec une saloperie comme ORM (hibernate).

Merci a vous

[SQLpro]

...j'arrive à jouer et à démontrer l'importance des clefs (cluster ou non) pour la granularité des locks. Et de démontrer, l’importance et l’impact du niveau d’isolation.

Plus encore, l'importance :

• d'un bon modèle (normalisé à fond)
• de l'indexation !

Car j’ai pu remarquer dans ma recherche les nombreux d’impact table/index partitionné, compressé, l’impact de la normalisation. Et surtout, lorsqu’on joue avec une saloperie comme ORM (hibernate).

Oh que oui !!!!!

A +

[slefevre01]

Plus encore, l'importance :

• d'un bon modèle (normalisé à fond)
• de l'indexation !

Oh que oui !!!!!

A +

Bonjour Frédéric,
J'ai assisté à ta présentation sur les locks aux sqldays.
Tu as lâcher le mot hibernate en fin de prez comme quoi ca ne fonctionnait pas très bien avec sqlserver.
Peux-tu m'en dire un peu plus ?

Je suis en train d'intégrer un client utilisant l'application ecommerce "hybris" (java/hibernate) avec SQL2012 standard ed, et nous rencontrons beaucoup de problèmes (performances (gestion des locks), fonctionnels dans le sens ou c'est l'application qui gère ces PK, et on se retrouvre avec beaucoup de duplicate keys sur ces fameuses PK).
Au niveau isolation, l'éditeur préconise: READ_COMMITTED_SNAPSHOT ON, ALLOW_SNAPSHOT_ISOLATION ON. Ce que nous avons mis en place.

Je suis preneur de tout conseils, suggestions.

Par avance merci

Sebastien

[zoltix]

Lorsqu'un éditeur demande ce type de chose
"READ_COMMITTED_SNAPSHOT ON, ALLOW_SNAPSHOT_ISOLATION "
Il y a souvent un gros problème fonctionnel(analyse, normalisation...) qui n'est pas lié aux fournisseurs de DB(SQL SEVER, ORCALE, Teradata....).

Va voir c'est rigolo et tu auras un aperçu.

Si tu veux de l'aide faudrait avoir plus d'infos(le design, la charge ....)

http://www.codinghorror.com/blog/200...r-science.html

http://blogs.tedneward.com/2006/06/2...r+Science.aspx