deadlocked on lock resources. SQL Server 2000

Hi, i am getting this error when i am running a stored procedure.

Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

i think so it is getting this error becasue it blocking it self at one point in the SP

DECLARE cty_Cursor CURSOR FOR
SELECT Country FROM TB_Country

declare @.cty varchar(2)

OPEN cty_Cursor;
FETCH NEXT FROM cty_Cursor into @.cty;
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC SP_DO_SOMETHING @.cty
FETCH NEXT FROM cty_Cursor into @.cty;
END;
CLOSE cty_Cursor;
DEALLOCATE cty_Cursor;

i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.

how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.

Regards,

Haroon

what happens when you run the stored procedure outside of the cursor?

what's in the stored procedure?

Deadlock within stored procedure - need help

We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the system, we get the following error
periodically:

Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)

Here is the script that creates our stored procedure:

USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetGeneratorValue]
@.genTableName varchar(50),
@.Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @.Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
UPDATE GENERATOR SET GENVALUE = @.Gen_Value+1 WHERE
GENTABLENAME=@.genTableName
COMMIT;
SET @.Gen_Value = @.Gen_Value+1
SELECT @.Gen_Value
END

This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.On Jun 12, 9:37 am, byahne <bya...@.yahoo.comwrote:

Quote:

Originally Posted by

We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the system, we get the following error
periodically:
>
Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)
>
Here is the script that creates our stored procedure:
>
USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
>
CREATE PROCEDURE [dbo].[GetGeneratorValue]
@.genTableName varchar(50),
@.Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @.Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
UPDATE GENERATOR SET GENVALUE = @.Gen_Value+1 WHERE
GENTABLENAME=@.genTableName
COMMIT;
SET @.Gen_Value = @.Gen_Value+1
SELECT @.Gen_Value
END
>
This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.

1. Down your isolation level to REPEATABLE READ.
2. UPDATE first, then SELECT.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
UPDATE GENERATOR SET GENVALUE = GENVALUE + 1 WHERE
GENTABLENAME=@.genTableName
SELECT GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
COMMIT;

3. Consider allocating your numbers in batches rather than one at a
time.|||Fantastic! That appears to have fixed the problem! Thank you for
your timely response.
-b|||Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.

Any other words of wisdom on why this might be happening and how to
avoid it?|||byahne (byahne@.yahoo.com) writes:

Quote:

Originally Posted by

Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.
>
Any other words of wisdom on why this might be happening and how to
avoid it?

Did you also rewrite the procedure as Alex suggested? Or did you just
change the isolation level? In the latter case, you should add
"WITH (UPDLOCK)" to the SELECT query.

Else what happens is that two processes both get the read-lock on
the wrong, and then no one can procede with the UPDATE. Since only
one process at a time can hold an Update lock, one them will be held
up at this point - rather than both being held up later.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to Erland's suggestion, see
http://blogs.msdn.com/sqlcat/archiv...nce-number.aspx
for tweaks to the technique Alex suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"byahne" <byahne@.yahoo.comwrote in message
news:1181745584.424801.302750@.o11g2000prd.googlegr oups.com...

Quote:

Originally Posted by

Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.
>
Any other words of wisdom on why this might be happening and how to
avoid it?
>

deadlock victim even using temp table

Hi. I am struggling to understand why I get the following error in
using the stored procedure noted below. I recently starting using a
temp table as a way of providing custom paging in asp.net and this
problem has occured ever since (maybe 10 times per day with an average
of 30 users on all day).
Here is the error: "Transaction (Process ID ##) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction"
The client code is below. It uses a DataAdapter to fill a dataset that
is used to populate a datagrid. The long store procedure is below
that. It essentialy fills the temp table with records that are chosen
then retrieves all the necessary fields for the datagrid using whatever
page that is selected. There is code in there to support sorting and
hopefully it's not too confusing.
I apologize for the long post, I didn't want to remove parts of the SP
to make it shorter in case I removed an important element. I admit, I
am only an intermediate programmer so I may be missing some
fundamentals. Hopefully this is obvious to someone.
Thanks in advance.
Jeff
-- client code --
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter("tochange",
myConnection)
'Dim myCommand As New SqlDataAdapter
'myCommand.SelectCommand.Connection = myConnection
myCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
myCommand.SelectCommand.CommandText =
"dbo.csp_cGeneral_GetRequests2"
myCommand.SelectCommand.Parameters.Add("@.PortalID",
SqlDbType.Int).Value = iPortalID
myCommand.SelectCommand.Parameters.Add("@.Status",
SqlDbType.VarChar, 10).Value = Status
myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
SqlDbType.Int).Value = RequestSeqID
myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
SqlDbType.VarChar, 30).Value = BorrowerLastName
myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
SqlDbType.VarChar, 30).Value = LoanOfficerCompany
myCommand.SelectCommand.Parameters.Add("@.BegDate",
SqlDbType.VarChar, 25).Value = BegDate
myCommand.SelectCommand.Parameters.Add("@.EndDate",
SqlDbType.VarChar, 25).Value = EndDate
myCommand.SelectCommand.Parameters.Add("@.ContactName",
SqlDbType.VarChar, 30).Value = ContactName
myCommand.SelectCommand.Parameters.Add("@.AgentID",
SqlDbType.Int).Value = AgentID
myCommand.SelectCommand.Parameters.Add("@.HasDocs",
SqlDbType.Int).Value = HasDocs
myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
SqlDbType.Int).Value = iStaffSearchID
myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
SqlDbType.Int).Value = _currentPageNumber
myCommand.SelectCommand.Parameters.Add("@.PageSize",
SqlDbType.Int).Value = iPagesize
myCommand.SelectCommand.Parameters.Add("@.SortField",
SqlDbType.VarChar, 30).Value = strSortColumn
myCommand.SelectCommand.Parameters.Add("@.UserID",
SqlDbType.Int).Value = UserID
myCommand.SelectCommand.Parameters.Add("@.Role",
SqlDbType.VarChar, 20).Value = Role
myCommand.SelectCommand.Parameters.Add("@.Maps",
SqlDbType.VarChar, 20).Value = sMaps
' Create and Fill the DataSet
Dim myDataSet As New DataSet
myCommand.Fill(myDataSet, "Requests")
Dim myTable As DataTable = myDataSet.Tables("Requests")
If Not myTable Is Nothing Then
If myTable.Rows.Count > 0 Then
Dim dr As DataRow = myTable.Rows(0)
_TotalRecords = dr.Item("TotalRecords")
Else
_TotalRecords = 0
End If
End If
' Return the DataSet
-- stored procedure --
ALTER procedure dbo.csp_cGeneral_GetRequests2
@.PortalID int,
@.Status varchar(10) = "-1",
@.RequestSeqID int = -1,
@.BorrowerLastName varchar(30) = "-1",
@.LoanOfficerCompany varchar(30) = "-1",
@.BegDate varchar(25) = "-1",
@.EndDate varchar(25) = "-1",
@.ContactName varchar(30) = "-1",
@.AgentID Int = Null,
@.UserID int = 0,
@.Role varchar(20) = 'None',
@.HasDocs decimal = -1,
@.CurrentPage int,
@.PageSize int,
@.SortField varchar(30),
@.Maps varchar(20),
@.AssignedStaffID Int --(-1 all, -2 not in list)
as
--if @.AssignedStaffID = -2
--begin
--
--end
Declare @.TotalRecords int
Declare @.Status1 int
Declare @.Status2 int
Declare @.AssignedAgentID int
Declare @.CustomerID int
set @.AssignedAgentID = 0
set @.CustomerID = 0
if @.Role = 'NotaryAgent'
Begin
set @.AssignedAgentID = @.UserID
set @.CustomerID = Null
end
if @.Role = 'Customer'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = @.UserID
end
if @.Role = 'ServiceOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
if @.Role = 'AgentOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
set @.Status1 = -1
set @.Status2 = -1
if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
begin
set @.Status1 = cast(@.Status as int)
set @.Status2 = cast(@.Status as int)
end
else
begin
if @.Status = '123'
Begin
set @.Status1 = 1
set @.Status2 = 3
end
if @.Status = '45'
Begin
set @.Status1 = 4
set @.Status2 = 5
end
if @.Status = '123456'
Begin
set @.Status1 = 1
set @.Status2 = 6
end
if @.Status = '1234569'
Begin
set @.Status1 = 1
set @.Status2 = 10
end
end
Declare @.BegDate2 as smalldatetime
Declare @.EndDate2 as smalldatetime
if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
isdate(@.EndDate) = 0
begin
set @.BegDate2 = Null
Set @.EndDate2 = Null
end
else
begin
set @.BegDate2 = cast(@.BegDate as smalldatetime)
Set @.EndDate2 = cast(@.EndDate as smalldatetime)
end
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
RequestID int,
FileSize int,
DocCount int
)
INSERT INTO #TempTable
(
RequestID,
FileSize,
DocCount
)
SELECT
RequestID,
FileSize,
DocCount
>From (
Select SR.RequestID, isnull(FileSize,0) as FileSize,
isnull(temp1.DocCount,0) as DocCount,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN SRS.StatusOrder
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN 0
WHEN 'HasCust' THEN SR.UserID
WHEN 'StaffOrder' THEN Staff.StaffOrder
ELSE SRS.StatusOrder
END AS sortcol0,
CASE @.SortField
WHEN 'Request' THEN ''
WHEN 'Status' THEN ''
WHEN 'Borrower' THEN SR.BorrowerLastName
WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Location' THEN isnull(SR.SigningCity,'')
WHEN 'ContactInfo' THEN SR.ContactName
WHEN 'Agent' THEN Users.LastName
WHEN 'FileSize' THEN ''
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN Staff.StaffInitials
ELSE ''
END AS sortcol1,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Borrower' THEN SR.BorrowerFirstName
WHEN 'Date' THEN SR.SigningTime
WHEN 'Location' THEN isnull(SR.SigningState,'')
WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
WHEN 'Agent' THEN Users.FirstName
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE convert(varchar(20),SR.SigningDate,112)
END AS sortcol2,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN SR.SigningTime
WHEN 'Borrower' THEN '0'
WHEN 'Date' THEN '0'
WHEN 'Location' THEN '0'
WHEN 'ContactInfo' THEN '0'
WHEN 'Agent' THEN '0'
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE SR.SigningTime
END AS sortcol3,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN 0
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN FileSize
WHEN 'HasCust' THEN 0
WHEN 'StaffOrder' THEN 0
ELSE 0
END AS sortcol4,
SR.RequestSeqID AS sortcol5
>From dbo.ctbl_SigningRequests SR
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join (
Select ctbl_Docs.RequestID,
case when sum(Case when ctbl_Docs.LoanDocs = 0 and
ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as
DocCount,
cast(sum(ctbl_Docs.filesize) as decimal)/1000000 as filesize from
ctbl_Docs
Where ctbl_Docs.PortalID = @.PortalID
Group by ctbl_Docs.RequestID
) as temp1 ON SR.RequestID = temp1.RequestID
Where
SR.PortalID = @.PortalID
and SR.InActiveDate is null
and SR.BorrowerLastName like
IsNull(nullif('%'+@.BorrowerLastName+'%',
'%-1%'),'%'+SR.BorrowerLastName+'%')
and SR.LoanOfficerCompany like
IsNull(nullif('%'+@.LoanOfficerCompany+'%
','%-1%'),'%'+SR.LoanOfficerCompany+
'%')
and SR.ContactName like
IsNull(nullif('%'+@.ContactName+'%','%-1%'),'%'+SR.ContactName+'%')
and SR.RequestSeqID =
isnull(Nullif(@.RequestSeqID,-1),SR.RequestSeqID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AgentID,-1),IsNull(SR.AssignedAgent,-1))
and SR.SigningStatusID Between
IsNull(Nullif(@.Status1,-1),SR.SigningStatusID) and
IsNull(NullIf(@.Status2,-1),SR.SigningStatusID)
and SR.SigningDate Between IsNull(@.BegDate2,SR.SigningDate) and
IsNull(@.EndDate2,SR.SigningDate)
and SR.DeleteDate Is Null
and isnull(temp1.FileSize,0) > cast(@.HasDocs as decimal)
and SR.UserID = IsNull(@.CustomerID,SR.UserID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AssignedAgentID,-1),IsNull(SR.AssignedAgent,-1))
and IsNull(SR.AssignedStaffID,-1) =
isnull(Nullif(@.AssignedStaffID,-1),IsNull(SR.AssignedStaffID,-1))
) as t1
order by sortcol0, sortcol1, sortcol2, sortcol3, sortcol4 DESC,
sortcol5
--Create variable to identify the first and last record that should be
selected
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
if @.CurrentPage > ceiling(cast(@.TotalRecords as float)/cast (@.PageSize
as float))
set @.CurrentPage = isnull(ceiling(@.TotalRecords / @.PageSize),1)
--select ceiling(cast(@.TotalRecords as float)/cast (@.PageSize as
float))
--select ceiling(cast(31/10 as float))
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
--Return the total number of records available as an output parameter
--Select one page of data based on the record numbers above
Select SR.RequestID, SR.RequestSeqID, SRS.StatusNameShort, SR.UserID,
SRS.StatusOrder, SR.SigningDate, SR.SigningTime, SR.LoanNumber,
Case When SR.InvoiceCreated is Null then 0 else 1 end as
InvoiceCreated,
Case When SR.Invoiced is Null then 0 else 1 end as Invoiced,
Case When SR.InvoicePaid is Null then 0 else 1 end as CustomerPaid,
Case When SR.NotaryPaid is Null then 0 else 1 end as NotaryPaid,
Case When SR.Invoiced is Null then '0' else '1' end + Case When
SR.InvoicePaid is Null then '0' else '1' end
+ Case When SR.NotaryPaid is Null then '0' else '1' end as IconSort,
SR.ContactName, Isnull(SR.ContactEmail,'') as ContactEmail,
Isnull(SR.ContactPhone,'') as ContactPhone,
-- SR.ContactName + '' + left(SR.LoanOfficerCompany,10) as
ContactInfo, SR.BorrowerLastName, SR.BorrowerFirstName,
Left(SR.ContactName,15) as ContactInfo, SR.BorrowerLastName,
SR.BorrowerFirstName,
SR.LoanOfficerCompany, left(SR.LoanOfficerCompany,10) as
LoanOfficerCompanyShort,
Case When Users.UserID Is Null then '(Assign Notary)' else
Users.FirstName + ' ' + Users.LastName end as AssignedAgentName,
IsNull(Users.UserID,0) as AssignedAgentID, isnull(SR.SigningCity,'')
+ ', ' + isnull(SR.SigningState,'') as CityState,
isnull(SR.SigningZip,'') as SigningZip,
SR.LastChangedByMobile,
cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1)) as HudDocsFlag,
Case cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1))
When '001' then 'Loan Docs and Title are not in. Other docs exist.'
When '011' then 'Loan Docs are not in. Title is in. Other docs
exist.'
When '101' then 'Loan Docs are in. Title is not in. Other docs
exist.'
When '111' then 'Loan Docs and Title are in. Other docs exist.'
When '000' then 'Loan Docs and Title are not in.'
When '010' then 'Loan Docs are not in. Title is in.'
When '100' then 'Loan Docs are in. Title is not in.'
When '110' then 'Loan Docs and Title are in.'
else 'Desc error.'
end as HudDocsFlagDesc,
#TempTable.FileSize,
Case When SR.UserID = 0 then '-' else 'C' end as CustomerFlag,
Case When SR.AssignedAgent = 0 and @.Maps='MSN' then
''
When SR.AssignedAgent <> 0 and @.Maps='MSN' then
replace(replace('http://maps.msn.com/directionsFind.aspx?strt1=' +
isnull(Users.Street,'') + '&city1=' + isnull(Users.City,'') + '&zipc1='
+ isnull(Users.PostalCode,'')
+ '&cnty1=0&strt2=' + isnull(SR.SigningAddress1,'') + '&city2=' +
isnull(SR.SigningCity,'') + '&zipc2=' + isnull(SR.SigningZip,'') +
'&cnty2=0&rtyp=1&unit=0',' ','%20'),'#','')
When SR.AssignedAgent = 0 and @.Maps='QUEST' then
''
When SR.AssignedAgent <> 0 and @.Maps='QUEST' then
replace(replace('http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=
1&un=m&cl=EN&ct=NA&rsres=1&1a='
+ isnull(Users.Street,'') + '&1c=' + isnull(Users.City,'') + '&1s=' +
isnull(Users.Region,'') + '&1z=' + isnull(Users.PostalCode,'')
+ '&2a=' + isnull(SR.SigningAddress1,'') + '&2c=' +
isnull(SR.SigningCity,'') + '&2s=' + isnull(SR.SigningState,'') +
'&2z=' + isnull(SR.SigningZip,''),' ','%20'),'#','')
else ''
end as MapLink,
cast(isnull(ctbl_PortalData.FileSSLActivate,1) as varchar(1)) as
FileSSLActivate,
(isnull(SR.PriceQty_Agent1,0) * isnull(SR.PriceAmt_Agent1,0))
+(isnull(SR.PriceQty_Agent2,0) * isnull(SR.PriceAmt_Agent2,0))
+(isnull(SR.PriceQty_Agent3,0) * isnull(SR.PriceAmt_Agent3,0))
+(isnull(SR.PriceQty_Agent4,0) * isnull(SR.PriceAmt_Agent4,0))
+(isnull(SR.PriceQty_Agent5,0) * isnull(SR.PriceAmt_Agent5,0)) as
NotaryInvoiceTotal, @.TotalRecords as TotalRecords,
isnull(Staff.StaffInitials, Isnull(Staff.FirstName,'***')) as Staff,
isnull(Staff.StaffOrder,0) as StaffOrder
>From dbo.ctbl_SigningRequests SR
inner join #TempTable
on #TempTable.RequestID = SR.RequestID
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
WHERE
ID > @.FirstRec
AND
ID < @.LastRec
order by #TempTable.[ID]Check the transaction isolation level: you can probably live with READ
COMMITTED. Also make sure that the procedure isn't running in the context o
f
a transaction. If that isn't the problem, then make sure that indexes exist
on the columns joined and that the execution plan uses them. You may have t
o
coerce the optimizer with a hint or two.
"jhonz@.etsmail.com" wrote:

> Hi. I am struggling to understand why I get the following error in
> using the stored procedure noted below. I recently starting using a
> temp table as a way of providing custom paging in asp.net and this
> problem has occured ever since (maybe 10 times per day with an average
> of 30 users on all day).
> Here is the error: "Transaction (Process ID ##) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction"
> The client code is below. It uses a DataAdapter to fill a dataset that
> is used to populate a datagrid. The long store procedure is below
> that. It essentialy fills the temp table with records that are chosen
> then retrieves all the necessary fields for the datagrid using whatever
> page that is selected. There is code in there to support sorting and
> hopefully it's not too confusing.
> I apologize for the long post, I didn't want to remove parts of the SP
> to make it shorter in case I removed an important element. I admit, I
> am only an intermediate programmer so I may be missing some
> fundamentals. Hopefully this is obvious to someone.
> Thanks in advance.
> Jeff
>
> -- client code --
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
> Dim myCommand As New SqlDataAdapter("tochange",
> myConnection)
> 'Dim myCommand As New SqlDataAdapter
> 'myCommand.SelectCommand.Connection = myConnection
> myCommand.SelectCommand.CommandType =
> CommandType.StoredProcedure
> myCommand.SelectCommand.CommandText =
> "dbo.csp_cGeneral_GetRequests2"
> myCommand.SelectCommand.Parameters.Add("@.PortalID",
> SqlDbType.Int).Value = iPortalID
> myCommand.SelectCommand.Parameters.Add("@.Status",
> SqlDbType.VarChar, 10).Value = Status
> myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
> SqlDbType.Int).Value = RequestSeqID
> myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
> SqlDbType.VarChar, 30).Value = BorrowerLastName
> myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
> SqlDbType.VarChar, 30).Value = LoanOfficerCompany
> myCommand.SelectCommand.Parameters.Add("@.BegDate",
> SqlDbType.VarChar, 25).Value = BegDate
> myCommand.SelectCommand.Parameters.Add("@.EndDate",
> SqlDbType.VarChar, 25).Value = EndDate
> myCommand.SelectCommand.Parameters.Add("@.ContactName",
> SqlDbType.VarChar, 30).Value = ContactName
> myCommand.SelectCommand.Parameters.Add("@.AgentID",
> SqlDbType.Int).Value = AgentID
> myCommand.SelectCommand.Parameters.Add("@.HasDocs",
> SqlDbType.Int).Value = HasDocs
> myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
> SqlDbType.Int).Value = iStaffSearchID
> myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
> SqlDbType.Int).Value = _currentPageNumber
> myCommand.SelectCommand.Parameters.Add("@.PageSize",
> SqlDbType.Int).Value = iPagesize
> myCommand.SelectCommand.Parameters.Add("@.SortField",
> SqlDbType.VarChar, 30).Value = strSortColumn
> myCommand.SelectCommand.Parameters.Add("@.UserID",
> SqlDbType.Int).Value = UserID
> myCommand.SelectCommand.Parameters.Add("@.Role",
> SqlDbType.VarChar, 20).Value = Role
> myCommand.SelectCommand.Parameters.Add("@.Maps",
> SqlDbType.VarChar, 20).Value = sMaps
> ' Create and Fill the DataSet
> Dim myDataSet As New DataSet
> myCommand.Fill(myDataSet, "Requests")
> Dim myTable As DataTable = myDataSet.Tables("Requests")
> If Not myTable Is Nothing Then
> If myTable.Rows.Count > 0 Then
> Dim dr As DataRow = myTable.Rows(0)
> _TotalRecords = dr.Item("TotalRecords")
> Else
> _TotalRecords = 0
> End If
> End If
> ' Return the DataSet
>
> -- stored procedure --
> ALTER procedure dbo.csp_cGeneral_GetRequests2
> @.PortalID int,
> @.Status varchar(10) = "-1",
> @.RequestSeqID int = -1,
> @.BorrowerLastName varchar(30) = "-1",
> @.LoanOfficerCompany varchar(30) = "-1",
> @.BegDate varchar(25) = "-1",
> @.EndDate varchar(25) = "-1",
> @.ContactName varchar(30) = "-1",
> @.AgentID Int = Null,
> @.UserID int = 0,
> @.Role varchar(20) = 'None',
> @.HasDocs decimal = -1,
> @.CurrentPage int,
> @.PageSize int,
> @.SortField varchar(30),
> @.Maps varchar(20),
> @.AssignedStaffID Int --(-1 all, -2 not in list)
> as
> --if @.AssignedStaffID = -2
> --begin
> --
> --end
> Declare @.TotalRecords int
> Declare @.Status1 int
> Declare @.Status2 int
> Declare @.AssignedAgentID int
> Declare @.CustomerID int
> set @.AssignedAgentID = 0
> set @.CustomerID = 0
> if @.Role = 'NotaryAgent'
> Begin
> set @.AssignedAgentID = @.UserID
> set @.CustomerID = Null
> end
> if @.Role = 'Customer'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = @.UserID
> end
> if @.Role = 'ServiceOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> if @.Role = 'AgentOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> set @.Status1 = -1
> set @.Status2 = -1
> if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
> begin
> set @.Status1 = cast(@.Status as int)
> set @.Status2 = cast(@.Status as int)
> end
> else
> begin
> if @.Status = '123'
> Begin
> set @.Status1 = 1
> set @.Status2 = 3
> end
> if @.Status = '45'
> Begin
> set @.Status1 = 4
> set @.Status2 = 5
> end
> if @.Status = '123456'
> Begin
> set @.Status1 = 1
> set @.Status2 = 6
> end
> if @.Status = '1234569'
> Begin
> set @.Status1 = 1
> set @.Status2 = 10
> end
> end
> Declare @.BegDate2 as smalldatetime
> Declare @.EndDate2 as smalldatetime
> if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
> isdate(@.EndDate) = 0
> begin
> set @.BegDate2 = Null
> Set @.EndDate2 = Null
> end
> else
> begin
> set @.BegDate2 = cast(@.BegDate as smalldatetime)
> Set @.EndDate2 = cast(@.EndDate as smalldatetime)
> end
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> RequestID int,
> FileSize int,
> DocCount int
> )
> INSERT INTO #TempTable
> (
> RequestID,
> FileSize,
> DocCount
> )
> SELECT
> RequestID,
> FileSize,
> DocCount
> Select SR.RequestID, isnull(FileSize,0) as FileSize,
> isnull(temp1.DocCount,0) as DocCount,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN SRS.StatusOrder
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN 0
> WHEN 'HasCust' THEN SR.UserID
> WHEN 'StaffOrder' THEN Staff.StaffOrder
> ELSE SRS.StatusOrder
> END AS sortcol0,
> CASE @.SortField
> WHEN 'Request' THEN ''
> WHEN 'Status' THEN ''
> WHEN 'Borrower' THEN SR.BorrowerLastName
> WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Location' THEN isnull(SR.SigningCity,'')
> WHEN 'ContactInfo' THEN SR.ContactName
> WHEN 'Agent' THEN Users.LastName
> WHEN 'FileSize' THEN ''
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN Staff.StaffInitials
> ELSE ''
> END AS sortcol1,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Borrower' THEN SR.BorrowerFirstName
> WHEN 'Date' THEN SR.SigningTime
> WHEN 'Location' THEN isnull(SR.SigningState,'')
> WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
> WHEN 'Agent' THEN Users.FirstName
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE convert(varchar(20),SR.SigningDate,112)
> END AS sortcol2,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN SR.SigningTime
> WHEN 'Borrower' THEN '0'
> WHEN 'Date' THEN '0'
> WHEN 'Location' THEN '0'
> WHEN 'ContactInfo' THEN '0'
> WHEN 'Agent' THEN '0'
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE SR.SigningTime
> END AS sortcol3,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN 0
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN FileSize
> WHEN 'HasCust' THEN 0
> WHEN 'StaffOrder' THEN 0
> ELSE 0
> END AS sortcol4,
> SR.RequestSeqID AS sortcol5
> Left Outer Join dbo.ctbl_SigningRequestStatus SRS
> ON SR.SigningStatusID = SRS.SigningStatusID
> Left Outer Join dbo.ctbl_UserData UD
> ON SR.AssignedAgent = UD.UserID
> Left Outer Join dbo.Users Users
> ON SR.AssignedAgent = Users.UserID
> Left Outer Join ctbl_PortalData
> On ctbl_PortalData.PortalID = @.PortalID
> Left Outer Join dbo.Users Staff
> ON SR.AssignedStaffID = Staff.UserID
> Left Outer Join (
> Select ctbl_Docs.RequestID,
> case when sum(Case when ctbl_Docs.LoanDocs = 0 and
> ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as|||Thanks, Brian. Couldn't I use WITH (NOLOCK) on the select that fills
the temptable and then later selects from it? READ COMMITTED seems to
be SQL 2000 default and is probably arleady set. I image the locks or
on the select a temp table should not be shared amongts users. In
ASP.Net's connection pooling, do you think things could get crossed
there.
I am not running a transaction so I think I am safe there.
I ran the execution plan and I don't see any table scans. Is that
sufficient indication that things are OK there?
Jeff

Deadlock question

We are getting deadlocks when running this code from a stored procedure many
times simultaneously with 30 concurrent requests. From our understanding,
repeatable read in this case should lock the single row returned from the
SELECT TOP 1 statement for the length of this transaction and not allow othe
r
requesters to read it or update it. Can you tell us why this is deadlocking
and advise us of a better way to do this update?
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
where Pins.PinID = (
SELECT TOP 1 PinID FROM PINS
WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
and HeldDate is NULL
ORDER BY CreationDate, PinID
)
COMMIT TRANSACTIONHas PinID got a clustered index on it?
Is CreationDate indexed?
Your select might need to lock more rows than necessary due to the way it
accesses the data. Verify that your select is as optimal as possible WRT
index utilization. It may lock the single row, or the page the row is on,
or even extents of pages.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Larry Herbinaux" <Larry Herbinaux@.discussions.microsoft.com> wrote in
message news:044C513A-A65C-4F56-AA88-C91266B00A25@.microsoft.com...
> We are getting deadlocks when running this code from a stored procedure
> many
> times simultaneously with 30 concurrent requests. From our understanding,
> repeatable read in this case should lock the single row returned from the
> SELECT TOP 1 statement for the length of this transaction and not allow
> other
> requesters to read it or update it. Can you tell us why this is
> deadlocking
> and advise us of a better way to do this update?
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> BEGIN TRANSACTION
> UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
> where Pins.PinID = (
> SELECT TOP 1 PinID FROM PINS
> WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
> and HeldDate is NULL
> ORDER BY CreationDate, PinID
> )
> COMMIT TRANSACTION|||REPEATABLE READ places a shared lock on the resource, not an exclusive lock.
That's probably why you're getting deadlocks.
Change your logic:
DECLARE @.PinID int
BEGIN TRANSACTION
SELECT @.PinID = TOP 1 PinID FROM Pins WITH(UPDLOCK) WHERE...
UPDATE Pins ... WHERE PinID = @.PinID
COMMIT TRANSACTION
You don't need to set the transaction isolation level in this case.
"Larry Herbinaux" <Larry Herbinaux@.discussions.microsoft.com> wrote in
message news:044C513A-A65C-4F56-AA88-C91266B00A25@.microsoft.com...
> We are getting deadlocks when running this code from a stored procedure
many
> times simultaneously with 30 concurrent requests. From our understanding,
> repeatable read in this case should lock the single row returned from the
> SELECT TOP 1 statement for the length of this transaction and not allow
other
> requesters to read it or update it. Can you tell us why this is
deadlocking
> and advise us of a better way to do this update?
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> BEGIN TRANSACTION
> UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
> where Pins.PinID = (
> SELECT TOP 1 PinID FROM PINS
> WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
> and HeldDate is NULL
> ORDER BY CreationDate, PinID
> )
> COMMIT TRANSACTION

deadlock problem

I have inherited a problem from the guy I have taken over from. Below
is the log error message followed by the procedure in question. Anybody
have any ideas why this is deadlocking?
2006-04-03 15:00:40.14 spid4 Node:1
2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 3::
2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
SELECT Line #: 71
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
U SPID:58 ECID:0 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/B4)
2006-04-03 15:00:40.14 spid4
2006-04-03 15:00:40.14 spid4 Node:2
2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 1::
2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
UPDATE Line #: 38
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
varchar(128),@.JobDescription varchar(512),
@.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
@.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
varbinary(5500),
@.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
@.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
@.RowTS varbinary(8) Output )
-- WITH ENCRYPTION
AS
--Update table Job
--Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
--BEGIN & Commit Transaction is done in proc. Can also be done in VB.
--If Update fails it returns an error and does a RaisError (Will force
VB error)
BEGIN
Declare @.OldTs VarBinary(8)
DECLARE @.nRowCount INT
--Begin the transaction
BEGIN TRANSACTION
--Retrieve and check timestamp. Update lock held until Commit (or
Rollback)
SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.nRowCount = 0
BEGIN
RaisError 50302 'Update failed - Job record was deleted by another
user'
GOTO PROC_ROLLBACK
END
IF @.OldTs <> @.RowTS
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
UPDATE dbo.Job Set JobName = @.JobName,
JobDescription = @.JobDescription,
JobUserName = @.JobUserName,
JobEmailType = @.JobEmailType,
JobEmailTo = @.JobEmailTo,
JobEmailCC = @.JobEmailCC,
JobClassName = @.JobClassName,
JobParameters = @.JobParameters,
JobDLLName = @.JobDLLName,
JobDLLPathName = @.JobDLLPathName,
CategoryId = @.CategoryId,
FrequencyType = @.FrequencyType,
UpdateBy = @.UpdateBy,
UpdateDate = @.UpdateDate,
RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
WHERE JobID = @.JobID
AND RowTS = @.OldTs
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.@.error <> 0
BEGIN
RaisError 50301 'Job Update Failed'
GOTO PROC_ROLLBACK
END
IF @.nRowCount = 0
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
--Get the new timestamp
SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
--Commit the Transaction
COMMIT TRANSACTION
RETURN(0)
PROC_ROLLBACK:
--Rollback on Error
ROLLBACK TRANSACTION
RETURN (-301)
END
GO
chris,
Can you check if this table has a clustered index?
Can you check if this table has a nonclustered index by [JobID]?
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/kb/q169960/
AMB
"chris.nolan@.feltex.com" wrote:

> I have inherited a problem from the guy I have taken over from. Below
> is the log error message followed by the procedure in question. Anybody
> have any ideas why this is deadlocking?
>
> 2006-04-03 15:00:40.14 spid4 Node:1
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 3::
> 2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
> SELECT Line #: 71
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> U SPID:58 ECID:0 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/B4)
> 2006-04-03 15:00:40.14 spid4
> 2006-04-03 15:00:40.14 spid4 Node:2
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 1::
> 2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
> UPDATE Line #: 38
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
> 2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
>
> CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
> varchar(128),@.JobDescription varchar(512),
> @.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
> @.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
> varbinary(5500),
> @.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
> @.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
> @.RowTS varbinary(8) Output )
> -- WITH ENCRYPTION
> AS
> --
> --Update table Job
> --Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
> --BEGIN & Commit Transaction is done in proc. Can also be done in VB.
> --If Update fails it returns an error and does a RaisError (Will force
> VB error)
> --
> BEGIN
> Declare @.OldTs VarBinary(8)
> DECLARE @.nRowCount INT
> --Begin the transaction
> BEGIN TRANSACTION
> --Retrieve and check timestamp. Update lock held until Commit (or
> Rollback)
> SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.nRowCount = 0
> BEGIN
> RaisError 50302 'Update failed - Job record was deleted by another
> user'
> GOTO PROC_ROLLBACK
> END
> IF @.OldTs <> @.RowTS
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> UPDATE dbo.Job Set JobName = @.JobName,
> JobDescription = @.JobDescription,
> JobUserName = @.JobUserName,
> JobEmailType = @.JobEmailType,
> JobEmailTo = @.JobEmailTo,
> JobEmailCC = @.JobEmailCC,
> JobClassName = @.JobClassName,
> JobParameters = @.JobParameters,
> JobDLLName = @.JobDLLName,
> JobDLLPathName = @.JobDLLPathName,
> CategoryId = @.CategoryId,
> FrequencyType = @.FrequencyType,
> UpdateBy = @.UpdateBy,
> UpdateDate = @.UpdateDate,
> RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
> WHERE JobID = @.JobID
> AND RowTS = @.OldTs
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.@.error <> 0
> BEGIN
> RaisError 50301 'Job Update Failed'
> GOTO PROC_ROLLBACK
> END
> IF @.nRowCount = 0
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> --Get the new timestamp
> SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
> --Commit the Transaction
> COMMIT TRANSACTION
> RETURN(0)
> PROC_ROLLBACK:
> --Rollback on Error
> ROLLBACK TRANSACTION
> RETURN (-301)
> END
> GO
>
|||Good question. I should have mentioned that. It doesn't have any
indexes at all.

deadlock problem

I have inherited a problem from the guy I have taken over from. Below
is the log error message followed by the procedure in question. Anybody
have any ideas why this is deadlocking?
2006-04-03 15:00:40.14 spid4 Node:1
2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 3::
2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
SELECT Line #: 71
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
U SPID:58 ECID:0 Ec:(0x53BF3570) Value:0x2c2a82a0 Cost:(0/B4)
2006-04-03 15:00:40.14 spid4
2006-04-03 15:00:40.14 spid4 Node:2
2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 1::
2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
UPDATE Line #: 38
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
varchar(128),@.JobDescription varchar(512),
@.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
@.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
varbinary(5500),
@.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
@.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
@.RowTS varbinary(8) Output )
-- WITH ENCRYPTION
AS
--
-- Update table Job
-- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
-- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
-- If Update fails it returns an error and does a RaisError (Will force
VB error)
--
BEGIN
Declare @.OldTs VarBinary(8)
DECLARE @.nRowCount INT
-- Begin the transaction
BEGIN TRANSACTION
-- Retrieve and check timestamp. Update lock held until Commit (or
Rollback)
SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.nRowCount = 0
BEGIN
RaisError 50302 'Update failed - Job record was deleted by another
user'
GOTO PROC_ROLLBACK
END
IF @.OldTs <> @.RowTS
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
UPDATE dbo.Job Set JobName = @.JobName,
JobDescription = @.JobDescription,
JobUserName = @.JobUserName,
JobEmailType = @.JobEmailType,
JobEmailTo = @.JobEmailTo,
JobEmailCC = @.JobEmailCC,
JobClassName = @.JobClassName,
JobParameters = @.JobParameters,
JobDLLName = @.JobDLLName,
JobDLLPathName = @.JobDLLPathName,
CategoryId = @.CategoryId,
FrequencyType = @.FrequencyType,
UpdateBy = @.UpdateBy,
UpdateDate = @.UpdateDate,
RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
WHERE JobID = @.JobID
AND RowTS = @.OldTs
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.@.error <> 0
BEGIN
RaisError 50301 'Job Update Failed'
GOTO PROC_ROLLBACK
END
IF @.nRowCount = 0
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
-- Get the new timestamp
SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
-- Commit the Transaction
COMMIT TRANSACTION
RETURN(0)
PROC_ROLLBACK:
-- Rollback on Error
ROLLBACK TRANSACTION
RETURN (-301)
END
GOchris,
Can you check if this table has a clustered index?
Can you check if this table has a nonclustered index by [JobID]?
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/kb/q169960/
AMB
"chris.nolan@.feltex.com" wrote:
> I have inherited a problem from the guy I have taken over from. Below
> is the log error message followed by the procedure in question. Anybody
> have any ideas why this is deadlocking?
>
> 2006-04-03 15:00:40.14 spid4 Node:1
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 3::
> 2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
> SELECT Line #: 71
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> U SPID:58 ECID:0 Ec:(0x53BF3570) Value:0x2c2a82a0 Cost:(0/B4)
> 2006-04-03 15:00:40.14 spid4
> 2006-04-03 15:00:40.14 spid4 Node:2
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 1::
> 2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
> UPDATE Line #: 38
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
> 2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
>
> CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
> varchar(128),@.JobDescription varchar(512),
> @.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
> @.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
> varbinary(5500),
> @.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
> @.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
> @.RowTS varbinary(8) Output )
> -- WITH ENCRYPTION
> AS
> --
> -- Update table Job
> -- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
> -- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
> -- If Update fails it returns an error and does a RaisError (Will force
> VB error)
> --
> BEGIN
> Declare @.OldTs VarBinary(8)
> DECLARE @.nRowCount INT
> -- Begin the transaction
> BEGIN TRANSACTION
> -- Retrieve and check timestamp. Update lock held until Commit (or
> Rollback)
> SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.nRowCount = 0
> BEGIN
> RaisError 50302 'Update failed - Job record was deleted by another
> user'
> GOTO PROC_ROLLBACK
> END
> IF @.OldTs <> @.RowTS
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> UPDATE dbo.Job Set JobName = @.JobName,
> JobDescription = @.JobDescription,
> JobUserName = @.JobUserName,
> JobEmailType = @.JobEmailType,
> JobEmailTo = @.JobEmailTo,
> JobEmailCC = @.JobEmailCC,
> JobClassName = @.JobClassName,
> JobParameters = @.JobParameters,
> JobDLLName = @.JobDLLName,
> JobDLLPathName = @.JobDLLPathName,
> CategoryId = @.CategoryId,
> FrequencyType = @.FrequencyType,
> UpdateBy = @.UpdateBy,
> UpdateDate = @.UpdateDate,
> RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
> WHERE JobID = @.JobID
> AND RowTS = @.OldTs
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.@.error <> 0
> BEGIN
> RaisError 50301 'Job Update Failed'
> GOTO PROC_ROLLBACK
> END
> IF @.nRowCount = 0
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> -- Get the new timestamp
> SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
> -- Commit the Transaction
> COMMIT TRANSACTION
> RETURN(0)
> PROC_ROLLBACK:
> -- Rollback on Error
> ROLLBACK TRANSACTION
> RETURN (-301)
> END
> GO
>|||Good question. I should have mentioned that. It doesn't have any
indexes at all.|||Does anybody have any suggestions please?

deadlock problem

I have inherited a problem from the guy I have taken over from. Below
is the log error message followed by the procedure in question. Anybody
have any ideas why this is deadlocking?
2006-04-03 15:00:40.14 spid4 Node:1
2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 3::
2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
SELECT Line #: 71
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
U SPID:58 ECID:0 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/B4)
2006-04-03 15:00:40.14 spid4
2006-04-03 15:00:40.14 spid4 Node:2
2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 1::
2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
UPDATE Line #: 38
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
varchar(128),@.JobDescription varchar(512),
@.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
@.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
varbinary(5500),
@.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
@.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
@.RowTS varbinary(8) Output )
-- WITH ENCRYPTION
AS
--
-- Update table Job
-- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
-- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
-- If Update fails it returns an error and does a RaisError (Will force
VB error)
--
BEGIN
Declare @.OldTs VarBinary(8)
DECLARE @.nRowCount INT
-- Begin the transaction
BEGIN TRANSACTION
-- Retrieve and check timestamp. Update lock held until Commit (or
Rollback)
SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.nRowCount = 0
BEGIN
RaisError 50302 'Update failed - Job record was deleted by another
user'
GOTO PROC_ROLLBACK
END
IF @.OldTs <> @.RowTS
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
UPDATE dbo.Job Set JobName = @.JobName,
JobDescription = @.JobDescription,
JobUserName = @.JobUserName,
JobEmailType = @.JobEmailType,
JobEmailTo = @.JobEmailTo,
JobEmailCC = @.JobEmailCC,
JobClassName = @.JobClassName,
JobParameters = @.JobParameters,
JobDLLName = @.JobDLLName,
JobDLLPathName = @.JobDLLPathName,
CategoryId = @.CategoryId,
FrequencyType = @.FrequencyType,
UpdateBy = @.UpdateBy,
UpdateDate = @.UpdateDate,
RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,2
1)
WHERE JobID = @.JobID
AND RowTS = @.OldTs
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.@.error <> 0
BEGIN
RaisError 50301 'Job Update Failed'
GOTO PROC_ROLLBACK
END
IF @.nRowCount = 0
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
-- Get the new timestamp
SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
-- Commit the Transaction
COMMIT TRANSACTION
RETURN(0)
PROC_ROLLBACK:
-- Rollback on Error
ROLLBACK TRANSACTION
RETURN (-301)
END
GOchris,
Can you check if this table has a clustered index?
Can you check if this table has a nonclustered index by [JobID]?
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/kb/q169960/
AMB
"chris.nolan@.feltex.com" wrote:

> I have inherited a problem from the guy I have taken over from. Below
> is the log error message followed by the procedure in question. Anybody
> have any ideas why this is deadlocking?
>
> 2006-04-03 15:00:40.14 spid4 Node:1
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 3::
> 2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
> SELECT Line #: 71
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> U SPID:58 ECID:0 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/B4)
> 2006-04-03 15:00:40.14 spid4
> 2006-04-03 15:00:40.14 spid4 Node:2
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 1::
> 2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
> UPDATE Line #: 38
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
> 2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:63 ECID:0 Ec0x76F51570) Value:0x78fb0520 Cost0/B4)
>
> CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
> varchar(128),@.JobDescription varchar(512),
> @.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
> @.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
> varbinary(5500),
> @.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
> @.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
> @.RowTS varbinary(8) Output )
> -- WITH ENCRYPTION
> AS
> --
> -- Update table Job
> -- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
> -- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
> -- If Update fails it returns an error and does a RaisError (Will force
> VB error)
> --
> BEGIN
> Declare @.OldTs VarBinary(8)
> DECLARE @.nRowCount INT
> -- Begin the transaction
> BEGIN TRANSACTION
> -- Retrieve and check timestamp. Update lock held until Commit (or
> Rollback)
> SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.nRowCount = 0
> BEGIN
> RaisError 50302 'Update failed - Job record was deleted by another
> user'
> GOTO PROC_ROLLBACK
> END
> IF @.OldTs <> @.RowTS
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> UPDATE dbo.Job Set JobName = @.JobName,
> JobDescription = @.JobDescription,
> JobUserName = @.JobUserName,
> JobEmailType = @.JobEmailType,
> JobEmailTo = @.JobEmailTo,
> JobEmailCC = @.JobEmailCC,
> JobClassName = @.JobClassName,
> JobParameters = @.JobParameters,
> JobDLLName = @.JobDLLName,
> JobDLLPathName = @.JobDLLPathName,
> CategoryId = @.CategoryId,
> FrequencyType = @.FrequencyType,
> UpdateBy = @.UpdateBy,
> UpdateDate = @.UpdateDate,
> RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,2
1)
> WHERE JobID = @.JobID
> AND RowTS = @.OldTs
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.@.error <> 0
> BEGIN
> RaisError 50301 'Job Update Failed'
> GOTO PROC_ROLLBACK
> END
> IF @.nRowCount = 0
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> -- Get the new timestamp
> SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
> -- Commit the Transaction
> COMMIT TRANSACTION
> RETURN(0)
> PROC_ROLLBACK:
> -- Rollback on Error
> ROLLBACK TRANSACTION
> RETURN (-301)
> END
> GO
>|||Good question. I should have mentioned that. It doesn't have any
indexes at all.sql

Deadlock on Update Statement (NOLOCK)

I have the following updates statements in my stored procedure which caused
a deadlock. Should I take the (NOLOCK) statement out of the update
statements?
Is there some else I can to help resolve this deadlock?
Thanks,
Update SRA_FlowMaster
Set Status = 'I'
From SRA_FlowMaster New
Inner Join SRA_FlowMaster (NoLock)
On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
And New.TypeCode = SRA_FlowMaster. TypeCode
WhereNew. FlowMasterID = @.i_FlowMasterID
And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
And SRA_FlowMaster. Status In ('K', 'M')
Update SRA_FlowMaster
Set Status = 'I'
From SRA_FlowMaster New
Inner Join SRA_FlowMaster (NoLock)
On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
And New.TypeCode = SRA_FlowMaster. TypeCode
Where New. FlowMasterID = @.i_FlowMasterID
And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
And SRA_FlowMaster. Status = 'A'
And New. Status In ('A', 'D')Joe,
A deadlock involved two processes requesting a resource being locked by the
other. You have to identify the processes and the statements causing the
deadlock. The table hint you are using is not a hint to prevent deadlocks.
See "Minimizing Deadlocks" and "Troubleshooting Deadlocks" in BOL for more
information.
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Joe K." wrote:

> I have the following updates statements in my stored procedure which cause
d
> a deadlock. Should I take the (NOLOCK) statement out of the update
> statements?
> Is there some else I can to help resolve this deadlock?
> Thanks,
>
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
> And New.TypeCode = SRA_FlowMaster. TypeCode
> WhereNew. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status In ('K', 'M')
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
> And New.TypeCode = SRA_FlowMaster. TypeCode
> Where New. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status = 'A'
> And New. Status In ('A', 'D')
>|||Hmm. Let me guess.
'Status' has only a handful of values.
You have an index on 'Status'.
Almost all of the values of 'Status' are the same.
This is a pretty common issue. Status fields are really a bad way to
represent and control status of an object, even thought they seem intuitive
at first. The key range locking required on an update makes it almost
impossible to scale to any reasonable level.
You can drop the index on Status but you will probably time out on some
other queries. (NOLOCK) hints won't change the inherent locking required to
do an update. Your problem is architectural and will require adjusting the
schema to fix. I would represent Status as a work queue using another
table. The presence of a pointer to the Primary Key indicates the status.
If there is no entries, then the status is whatever the most common status
(I.E. 'Closed', 'C', 'Paid', depending on the context) actually is.
Geoff N. Hiten
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:BED3489D-5AC1-48A7-B785-C9EF6128573B@.microsoft.com...
> I have the following updates statements in my stored procedure which
> caused
> a deadlock. Should I take the (NOLOCK) statement out of the update
> statements?
> Is there some else I can to help resolve this deadlock?
> Thanks,
>
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
> And New.TypeCode = SRA_FlowMaster. TypeCode
> WhereNew. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status In ('K', 'M')
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
> And New.TypeCode = SRA_FlowMaster. TypeCode
> Where New. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status = 'A'
> And New. Status In ('A', 'D')
>

deadlock on tempdb..sysindexes

Greetings,
I have stored procedure dumping resultset into created temporary table.
Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
Problem is I that can not change stored procedure and not sure how to stop
locking sysindexes since stored proc will take between 5-15 seconds to run
depending on date range supplied.
I would appreciate any suggestions how to resolve this issue
PS: Code goes like this
create table #temp(...)
insert into #temp
execute sp_StoredProc
--
SaxonThe problem appears to be that the stored procedure is executing within a
transaction. This is unavoidable, since the INSERT...EXEC statement starts
a transaction prior to executing the procedure. To avoid the deadlocks, you
MUST alter the stored procedure. If you can't alter it, then make a copy
and alter that. Change the procedure so that it executes an INSERT
statement into the temp table. (If a temp table is created before executing
the stored procedure, it is available within the body of the stored
procedure.) This will eliminate the transaction
You should avoid creating, altering or deleting temporary objects within a
transaction. This includes both tables, indexes and constraints. You
should avoid executing procedures within a transaction. For this reason, I
generally avoid INSERT...EXEC.
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
> Greetings,
> I have stored procedure dumping resultset into created temporary table.
> Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
> Problem is I that can not change stored procedure and not sure how to stop
> locking sysindexes since stored proc will take between 5-15 seconds to run
> depending on date range supplied.
> I would appreciate any suggestions how to resolve this issue
> PS: Code goes like this
> create table #temp(...)
> insert into #temp
> execute sp_StoredProc
> --
> Saxon|||Thanks Brian,
so basically if I create temp table and call stored proc to insert into
table instead of using INSERT... EXEC it would not cause deadlock since no
transactions would be started.
PS: Why inserting into temp table would hold lock on sysindexes anyway? I
tried to find some info on that but no luck.
Regards
Saxon
"Brian Selzer" wrote:

> The problem appears to be that the stored procedure is executing within a
> transaction. This is unavoidable, since the INSERT...EXEC statement start
s
> a transaction prior to executing the procedure. To avoid the deadlocks, y
ou
> MUST alter the stored procedure. If you can't alter it, then make a copy
> and alter that. Change the procedure so that it executes an INSERT
> statement into the temp table. (If a temp table is created before executi
ng
> the stored procedure, it is available within the body of the stored
> procedure.) This will eliminate the transaction
> You should avoid creating, altering or deleting temporary objects within a
> transaction. This includes both tables, indexes and constraints. You
> should avoid executing procedures within a transaction. For this reason,
I
> generally avoid INSERT...EXEC.
>
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
>
>|||The lock isn't caused by inserting, it's caused by creating, altering, or
deleting a temporary object within the procedure! The problem is that
normally, when a procedure runs, any transactions must be explicitly started
within the body of the proc. INSERT...EXEC wraps the procedure call in a
transaction. There are several articles on MSDN about lock contention and
blocking--some cite concurrency issues with tempdb. (There are fixes for
that in SP4.)
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
> Thanks Brian,
> so basically if I create temp table and call stored proc to insert into
> table instead of using INSERT... EXEC it would not cause deadlock since no
> transactions would be started.
> PS: Why inserting into temp table would hold lock on sysindexes anyway? I
> tried to find some info on that but no luck.
> Regards
> --
> Saxon
>
> "Brian Selzer" wrote:
>|||Thank you kindly Brian.
Much appreciated.
Regards
Saxon
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||Thanks for this useful description of the problem.
If we creates at temporary table in the procedure and fill data into it with
a function, will that cause a transaction too?
create table #temp(...)
insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||On Wed, 23 Nov 2005 03:36:11 -0800, winther wrote:

>Thanks for this useful description of the problem.
>If we creates at temporary table in the procedure and fill data into it wit
h
>a function, will that cause a transaction too?
>create table #temp(...)
>insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
Hi winther,
Yes. Every modification is automatically part of a transaction. If you
didn't start one explicitly, it will be started implicitly.
If SET IMPLICIT_TRANSACTION is OFF, the implicitly started transaction
will also be implicitly committed after each statement. With this
setting to ON, the server waits for an explicit COMMIT or ROLLBACK to
end the transaction.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There will be a transaction, but it won't put a lock on sysindexes because
the create table occurs apart from the transaction started for the insert.
"winther" <winther@.discussions.microsoft.com> wrote in message
news:0A8FC1D4-1571-45B8-91A8-29656995C285@.microsoft.com...
> Thanks for this useful description of the problem.
> If we creates at temporary table in the procedure and fill data into it
> with
> a function, will that cause a transaction too?
> create table #temp(...)
> insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
>
> "Brian Selzer" wrote:
>

Deadlock issue SQLServer2000

Hi,
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec0x1B58F568) Value:0x193ff980 Cost0/614)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec0x1D915568) Value:0x1940e180 Cost0/518)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollbac
k;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec0x1B58F568) Value:0x193ff980 Cost0/614)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec0x1D915568) Value:0x1940e180 Cost0/518)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollbac
k;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk
2msftngp13.phx.gbl>...[vbcol=seagreen]
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces.
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh
@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:

> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces.
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n6
3buuhoh85qte4asjvh@.4ax.com>...
>