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_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 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|||Hello,
Process B should not set a shared locked, because it has not opened any
transaction.
--
Olivier GIL
LAFON SA
"M A Srinivas" wrote:
> On 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_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 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
>|||On May 25, 2:19 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> Process B should not set a shared locked, because it has not opened any
> transaction.
> --
> Olivier GIL
> LAFON SA
>
> "M A Srinivas" wrote:
> > On 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_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 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- Hide quoted text -
> - Show quoted text -
Whether Process B in a transaction OR Not it will acquire SHARE lock
on a ROW/PAGE/TABLE .|||Hi Olivier,
Per my analysis, in this case, a block may be caused but dead lock should
not be caused since when B get a shared lock, A cannot lock the table until
B finishes the query.
To track the root cause, I recommend that you enable the trace flags -T1204
and -T3605 to the startup parameters and then restart your SQL Server.
Once the issue reoccurs, please post the error logs here or mail it to me
(changliw_at_microsoft_dot_com) for further research.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
0x56EDF598) Value:0x52