Deadlock

hi,
Need help in identifying deadlock issue. we are almost getting 10-15
deadlock issues in a day and with almost same type of log, lock type.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
--
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
--
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
--
RaviCould you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
hi,
Need help in identifying deadlock issue. we are almost getting 10-15
deadlock issues in a day and with almost same type of log, lock type.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
--
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
--
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
--
Ravi|||==========Part of code of update operation================SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> hi,
> Need help in identifying deadlock issue. we are almost getting 10-15
> deadlock issues in a day and with almost same type of log, lock type.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditions
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we are
> getting deadlocks.
> --
> Ravi
>
>|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID => es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode => ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
> > Could you post the complete text of the procedures involved?
> >
> > Generally, if you are selecting data from a table, and then updating that
> > data later in the transaction, you are likely to have deadlocking
> > situations. In this case, selecting with UPDLOCK hint helps.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > hi,
> > Need help in identifying deadlock issue. we are almost getting 10-15
> > deadlock issues in a day and with almost same type of log, lock type.
> >
> > what i am really interested in knowing is
> > 1) is it cycle deadlock or conversion deadlock.
> > 2) is it really good to use NOLOCk hint in production env.
> > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > 4) IS page lock really good (this is happening now) .
> > 5) Probable reasons why deadlock is coming ?
> > 6) All the columns are indexed in tables.
> >
> > below is the log..
> > Deadlock encountered ... Printing deadlock information
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:1
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> > Mode: SIU Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> > Ref:1 Life:00000000 SPID:77 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> > Line #: 36
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:2
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> > Mode: IX Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> > Ref:2 Life:02000000 SPID:66 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> > Line #: 320
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > Events.dbo.UpdateDb;1
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> >
> > --
> > this is line which caused deadlock..
> >
> > UPDATE Line #: 320
> > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.access
> > FROM #Temp1 ae
> > )
> >
> > --
> > second part of select st which is victim SP. This procedure has all select
> > statements only.
> >
> > SELECT a.*
> > FROM dbo.access ec
> > WHERE ec.accessID = 1 -->(1=Active)
> > AND ec.access = 23
> >
> >
> > Please note : we are getting almost 10-15 deadlocks with similiar conditions
> > and same page lock with same set of sp's. Actually both sp's are run by 2
> > SQL
> > jobs which run after every 10 mins and i think this is the reason why we are
> > getting deadlocks.
> >
> > --
> > Ravi
> >
> >
> >|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID => es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode => ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
> > Could you post the complete text of the procedures involved?
> >
> > Generally, if you are selecting data from a table, and then updating
that
> > data later in the transaction, you are likely to have deadlocking
> > situations. In this case, selecting with UPDLOCK hint helps.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > hi,
> > Need help in identifying deadlock issue. we are almost getting 10-15
> > deadlock issues in a day and with almost same type of log, lock type.
> >
> > what i am really interested in knowing is
> > 1) is it cycle deadlock or conversion deadlock.
> > 2) is it really good to use NOLOCk hint in production env.
> > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > 4) IS page lock really good (this is happening now) .
> > 5) Probable reasons why deadlock is coming ?
> > 6) All the columns are indexed in tables.
> >
> > below is the log..
> > Deadlock encountered ... Printing deadlock information
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:1
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
CleanCnt:1
> > Mode: SIU Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
Flg:0x0
> > Ref:1 Life:00000000 SPID:77 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
SELECT
> > Line #: 36
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
IX
> > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:2
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
CleanCnt:1
> > Mode: IX Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
Flg:0x0
> > Ref:2 Life:02000000 SPID:66 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
UPDATE
> > Line #: 320
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > Events.dbo.UpdateDb;1
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> >
> > --
> > this is line which caused deadlock..
> >
> > UPDATE Line #: 320
> > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.access
> > FROM #Temp1 ae
> > )
> >
> > --
> > second part of select st which is victim SP. This procedure has all
select
> > statements only.
> >
> > SELECT a.*
> > FROM dbo.access ec
> > WHERE ec.accessID = 1 -->(1=Active)
> > AND ec.access = 23
> >
> >
> > Please note : we are getting almost 10-15 deadlocks with similiar
conditions
> > and same page lock with same set of sp's. Actually both sp's are run by
2
> > SQL
> > jobs which run after every 10 mins and i think this is the reason why we
are
> > getting deadlocks.
> >
> > --
> > Ravi
> >
> >
> >|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====> second SP in the meantime trying to select and place shared lock but it gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> > ==========Part of code of update operation================> > SELECT *
> > INTO #Activeaccesss
> > FROM dbo.access e (NOLOCK)
> > INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> > INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => > ecat.accessCategoryID
> > INNER JOIN dbo.accessStatus es ON e.accessStatusID => > es.accessStatusID
> > INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> > -- AND ed.LanguageCode = ed.LanguageCode
> > INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> >
> > INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode => edl.LanguageCode
> > AND l.LanguageID = edl.LanguageID
> > INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> >
> > LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> > AND ec.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> > AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => > dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> > LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> > And St.StateID
> > IN (Select StateID From StateLoc STL Where STL.LanguageCode => > ed.LanguageCode and STL.Description = el.StateProvince)
> > LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => > est.accessSubTypeID
> > LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => > etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => > estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> > LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> > AND cl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID => il.InitiativeID
> > AND il.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> > LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> >
> > LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> > LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> > WHERE e.IsWebPublishable = @.True
> > AND e.IsReadyForWebPublish = @.True
> > AND e.accessStatusID = 1
> > AND
> > (
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID = 1
> > AND e.accessEndDate >= GetDate()
> > AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> > AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> > )
> > OR
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID in (2, 4)
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > OR
> > (
> > e.accessCategoryID = 3
> > AND EGT.ArchivedStartDate <= @.CurDate
> > AND EGT.ArchivedEndDate >= @.CurDate
> > )
> > OR
> > (
> > e.accessCategoryID = 5
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > )
> >
> > SELECT @.RowCount = @.@.ROWCOUNT
> > -- some other code here
> >
> > UPDATE access
> > SET access.IsPublished = 0
> > -- , access.IsWebPublishable = 0 --geohey 2/14/03
> > WHERE accessID IN
> > (
> > SELECT es.accessID
> > FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => > ae.accessID
> > WHERE ae.accessID IS NULL
> > )
> > -- some other code here
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.accessID
> > FROM #Activeaccesss ae
> > )
> > ================================ENDS here===========> >
> > Another SP is SELECT STATEMENT both of these are in jobs and runs after
> > every 10 mins. Infact i also noticed that deadlocks are not coming after
> > every 5-10 mins but there is certain interval of 1 or 2 hours.
> >
> > Thanks,
> >
> >
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Could you post the complete text of the procedures involved?
> > >
> > > Generally, if you are selecting data from a table, and then updating
> that
> > > data later in the transaction, you are likely to have deadlocking
> > > situations. In this case, selecting with UPDLOCK hint helps.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > > hi,
> > > Need help in identifying deadlock issue. we are almost getting 10-15
> > > deadlock issues in a day and with almost same type of log, lock type.
> > >
> > > what i am really interested in knowing is
> > > 1) is it cycle deadlock or conversion deadlock.
> > > 2) is it really good to use NOLOCk hint in production env.
> > > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > > 4) IS page lock really good (this is happening now) .
> > > 5) Probable reasons why deadlock is coming ?
> > > 6) All the columns are indexed in tables.
> > >
> > > below is the log..
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:1
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
> CleanCnt:1
> > > Mode: SIU Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
> Flg:0x0
> > > Ref:1 Life:00000000 SPID:77 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
> SELECT
> > > Line #: 36
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
> IX
> > > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:2
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
> CleanCnt:1
> > > Mode: IX Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
> Flg:0x0
> > > Ref:2 Life:02000000 SPID:66 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
> UPDATE
> > > Line #: 320
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > > Events.dbo.UpdateDb;1
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > >
> > > --
> > > this is line which caused deadlock..
> > >
> > > UPDATE Line #: 320
> > > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> > >
> > > UPDATE access
> > > SET IsPublished = 1
> > > WHERE accessID IN
> > > (
> > > SELECT ae.access
> > > FROM #Temp1 ae
> > > )
> > >
> > > --
> > > second part of select st which is victim SP. This procedure has all
> select
> > > statements only.
> > >
> > > SELECT a.*
> > > FROM dbo.access ec
> > > WHERE ec.accessID = 1 -->(1=Active)
> > > AND ec.access = 23
> > >
> > >
> > > Please note : we are getting almost 10-15 deadlocks with similiar
> conditions
> > > and same page lock with same set of sp's. Actually both sp's are run by
> 2
> > > SQL
> > > jobs which run after every 10 mins and i think this is the reason why we
> are
> > > getting deadlocks.
> > >
> > > --
> > > Ravi
> > >
> > >
> > >
>
>|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====> second SP in the meantime trying to select and place shared lock but it
gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> > ==========Part of code of update operation================> > SELECT *
> > INTO #Activeaccesss
> > FROM dbo.access e (NOLOCK)
> > INNER JOIN dbo.accessGMTTime EGT On e.accessID =EGT.accessID
> > INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => > ecat.accessCategoryID
> > INNER JOIN dbo.accessStatus es ON e.accessStatusID => > es.accessStatusID
> > INNER JOIN dbo.accessDetail ed ON e.accessID =ed.accessID
> > -- AND ed.LanguageCode = ed.LanguageCode
> > INNER JOIN dbo.Language l ON ed.LanguageCode =l.LanguageCode
> >
> > INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode => edl.LanguageCode
> > AND l.LanguageID = edl.LanguageID
> > INNER JOIN dbo.accessLocation el ON e.LocationID =el.LocationID
> >
> > LEFT JOIN dbo.CountryLoc ec ON el.CountryCode =ec.CountryCode
> > AND ec.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID =tzl.TimeZoneID
> > AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => > dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> > LEFT JOIN dbo.State st ON e.CountryCode =st.CountryCode
> > And St.StateID
> > IN (Select StateID From StateLoc STL Where STL.LanguageCode => > ed.LanguageCode and STL.Description = el.StateProvince)
> > LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => > est.accessSubTypeID
> > LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => > etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => > estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> > LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID =cl.CampaignID
> > AND cl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID => il.InitiativeID
> > AND il.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> > LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID =s.SubsidiaryID
> >
> > LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> > LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> > WHERE e.IsWebPublishable = @.True
> > AND e.IsReadyForWebPublish = @.True
> > AND e.accessStatusID = 1
> > AND
> > (
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID = 1
> > AND e.accessEndDate >= GetDate()
> > AND e.WebPublishStartDate <= convert(varchar(10), Getdate(),
120)
> > AND e.WebPublishEndDate >= convert(varchar(10), Getdate(),
120)
> > )
> > OR
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID in (2, 4)
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > OR
> > (
> > e.accessCategoryID = 3
> > AND EGT.ArchivedStartDate <= @.CurDate
> > AND EGT.ArchivedEndDate >= @.CurDate
> > )
> > OR
> > (
> > e.accessCategoryID = 5
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > )
> >
> > SELECT @.RowCount = @.@.ROWCOUNT
> > -- some other code here
> >
> > UPDATE access
> > SET access.IsPublished = 0
> > -- , access.IsWebPublishable = 0 --geohey 2/14/03
> > WHERE accessID IN
> > (
> > SELECT es.accessID
> > FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => > ae.accessID
> > WHERE ae.accessID IS NULL
> > )
> > -- some other code here
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.accessID
> > FROM #Activeaccesss ae
> > )
> > ================================ENDS here===========> >
> > Another SP is SELECT STATEMENT both of these are in jobs and runs after
> > every 10 mins. Infact i also noticed that deadlocks are not coming
after
> > every 5-10 mins but there is certain interval of 1 or 2 hours.
> >
> > Thanks,
> >
> >
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Could you post the complete text of the procedures involved?
> > >
> > > Generally, if you are selecting data from a table, and then updating
> that
> > > data later in the transaction, you are likely to have deadlocking
> > > situations. In this case, selecting with UPDLOCK hint helps.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > > hi,
> > > Need help in identifying deadlock issue. we are almost getting 10-15
> > > deadlock issues in a day and with almost same type of log, lock type.
> > >
> > > what i am really interested in knowing is
> > > 1) is it cycle deadlock or conversion deadlock.
> > > 2) is it really good to use NOLOCk hint in production env.
> > > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > > 4) IS page lock really good (this is happening now) .
> > > 5) Probable reasons why deadlock is coming ?
> > > 6) All the columns are indexed in tables.
> > >
> > > below is the log..
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:1
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
> CleanCnt:1
> > > Mode: SIU Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
> Flg:0x0
> > > Ref:1 Life:00000000 SPID:77 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
> SELECT
> > > Line #: 36
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
> IX
> > > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:2
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
> CleanCnt:1
> > > Mode: IX Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
> Flg:0x0
> > > Ref:2 Life:02000000 SPID:66 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
> UPDATE
> > > Line #: 320
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > > Events.dbo.UpdateDb;1
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > >
> > > --
> > > this is line which caused deadlock..
> > >
> > > UPDATE Line #: 320
> > > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> > >
> > > UPDATE access
> > > SET IsPublished = 1
> > > WHERE accessID IN
> > > (
> > > SELECT ae.access
> > > FROM #Temp1 ae
> > > )
> > >
> > > --
> > > second part of select st which is victim SP. This procedure has all
> select
> > > statements only.
> > >
> > > SELECT a.*
> > > FROM dbo.access ec
> > > WHERE ec.accessID = 1 -->(1=Active)
> > > AND ec.access = 23
> > >
> > >
> > > Please note : we are getting almost 10-15 deadlocks with similiar
> conditions
> > > and same page lock with same set of sp's. Actually both sp's are run
by
> 2
> > > SQL
> > > jobs which run after every 10 mins and i think this is the reason why
we
> are
> > > getting deadlocks.
> > >
> > > --
> > > Ravi
> > >
> > >
> > >
>
>