Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
Olivier GIL
LAFON SAOn May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
> A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAG
E
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint